Обсуждение: PreparedStatement error for date parameter with overlaps

On Mon, May 30, 2011 at 8:39 AM, Simon Mitchell <simon@jseb.com> wrote:
>     jdbc driver issue for PreparedStatement error for date parameter with
> overlaps
> // some bad code
>     sqlText = "SELECT (date ?, date ?) OVERLAPS (date ?, date ?);";
>     System.out.println("Executing this command: "+sqlText+"\n");
>     PreparedStatement prs = db.prepareStatement(sqlText);
>     prs.setString(1,"2001-02-16");
>     prs.setString(2,"2001-12-21");
>     prs.setString(3,"2001-10-30");
>     prs.setString(4,"2002-10-30");
>     System.out.println("Executing this command: "+ prs.toString() +"\n");
>     ResultSet results = prs.executeQuery();
> // error
> ***Exception:
> org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
>   Position: 14
> Found a work around, changing the query to include brackets around the
> question marks works.
>     sqlText = "SELECT (date (?), date (?)) OVERLAPS (date (?), date (?))";
> Is this a bug?
> --
> simon
>

Doesn't look like a bug. I don't think you can write that SQL in psql.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Simon Mitchell <simon@jseb.com> writes:
> // some bad code
>     sqlText = "SELECT (date ?, date ?) OVERLAPS (date ?, date ?);";

That's not going to work, because the syntax
    date 'something'
only works for literal constants, and a ? marker is not a literal constant.
You could try
    ?::date
or
    CAST(? AS date)
if you prefer to avoid Postgres-specific syntax.  Even better, don't use
setString --- that's really only meant for parameters that are of string
datatypes.

            regards, tom lane

On 31 May 2011 15:12, Simon Mitchell <simon@jseb.com> wrote:

> The strange thing is the doing a toString() before executeQuery() prints out
> the sql as per the postgres example, I can cut and past it into psql and it
> runs fine.

PreparedStatement.toString() isn't guaranteed to give you the query as
executed (or even a syntactically correct query!), it just substitutes
parameter values into the query string to give you something that
might be more useful for debugging than the default Object.toString()
implementation.

If you wanted to get the equivalent query in psql, you should take
your query string and replace ? with $1, $2, etc, then use
PREPARE/EXECUTE. That's much closer to how the JDBC driver actually
executes queries; the driver does *not* just substitute values into
the query string.

Oliver

On 31/05/11 11:12, Simon Mitchell wrote:

> Both   ?::date and  CAST(? AS date) fail in java.
>
>    ?::date works fine in psql, but  CAST(? AS date) fails in psql.
>
> SELECT (date CAST('2001-02-16' AS date), date ('2001-12-21')) OVERLAPS
> (date ('2001-10-30'), date ('2002-10-30'));
> ERROR:  syntax error at or near "CAST"

You wrote:

SELECT (date CAST('2001-02-16' AS date) ...
        ^^^^
       whoops

you should write:

SELECT (CAST('2001-02-16' AS date) ...

See how that goes.

--
Craig Ringer

On 31/05/11 14:01, Simon Mitchell wrote:
> Bingo
>
> thanks guys, this works fine, obviously I had no idea what i was doing,
> no need to run the date function on a date.
> SELECT (CAST(? AS date), ?::date) OVERLAPS (date(?), date(?)) works fine.


Avoid using the type-as-function form like "date(varname)" or "date(?)".
It's a non-standard hack and a postgresql implementation quirk that
exists because the underlying cast implementation functions are named
after their data types. Don't rely on it.

Instead, use the SQL-standard "CAST(? AS date)" syntax.

> So from what I understand using the date function in jdbc stuff requires
> brackets like most functions do,

There *is* a DATE function, but you should avoid it in favour of
explicit CASTs.

> but the psql is happy for me to use the date function with or without
> brackets.

No. The difference is that via PgJDBC you were using prepared
statements. To compare PgJDBC prepared statement execution with psql,
you must use the psql 'PREPARE' and 'EXECUTE' commands, not just type
the SELECT in directly. See the example below.

> Postgresql docs examples seem to be all with out brackets.

The DATE literal prefix is only valid with *literals*, and the examples
you refer to show DATE being used with literals, where it is valid. If
you're using a variable or a placeholder, the DATE prefix to specify
data type is *not* *valid*.

Compare:

-- DATE used to specify type of a literal
--
regress=> SELECT DATE '2011-04-02';
    date
------------
 2011-04-02
(1 row)

-- DATE used to specify type of a literal in a prepared statement
--
regress=> PREPARE datetest AS SELECT DATE '2011-04-02';
PREPARE
regress=> EXECUTE datetest;
    date
------------
 2011-04-02
(1 row)

-- DATE used to (try to) specify the type of a placeholder
-- variable in a prepared statement.
--
regress=> PREPARE datetest2 AS SELECT DATE $1;
ERROR:  syntax error at or near "$1"
LINE 1: PREPARE datetest2 AS SELECT DATE $1;

-- It is not understood, because the DATE prefix is not
-- valid for a non-literal. The CAST(value AS type) syntax
-- should be used instead:

regress=> PREPARE datetest2 AS SELECT CAST($1 AS date);
PREPARE
regress=> EXECUTE datetest2('2011-04-02');
    date
------------
 2011-04-02
(1 row)

-- ... and it's also legal (albeit unnecessary) to explicitly specify
-- the type of the literal you pass to EXECUTE, because you're using
-- the typename on a literal:

regress=> EXECUTE datetest2(DATE '2011-04-02');
    date
------------
 2011-04-02
(1 row)

Because it only applies to literals and it offers no advantage over a
CAST expression, it's often best to avoid the literal-type prefix.
Instead, use a CAST expression. PostgreSQL will use casts to obtain the
same information as a literal-type prefix when they are used on a
literal of unknown type, but unlike a type name prefix they'll also work
on variables and placeholders. So, instead of:

regress=> PREPARE datetest2 AS SELECT DATE $1;

write

regress=> PREPARE datetest2 AS SELECT CAST($1 AS date);

It's the same in JDBC, except that placeholders are "?" instead of "$n".



These rules don't just apply to placeholders in prepared statements. You
can't use a type name to cast a variable either:

SELECT DATE a FROM (SELECT TEXT '2011-04-02' a) AS x;
ERROR:  column "date" does not exist
LINE 1: SELECT DATE a FROM (SELECT TEXT '2011-04-02' a) AS x;

even though this works because it's using DATE on a literal:

regress=> SELECT a FROM (SELECT DATE '2011-04-02' a) AS x;
     a
------------
 2011-04-02
(1 row)

--
Craig Ringer

Simon Mitchell <simon@jseb.com> writes:
> So from what I understand using the date function in jdbc stuff requires
> brackets like most functions do,
> but the psql is happy for me to use the date function with or without
> brackets.

Craig Ringer already posted a much more long-winded response, but the
short answer is that this syntax is *not* a function.  The SQL spec
defines
        type_name 'literal string'
as a way of writing a literal constant of a specific type.  (The spec
actually only seems to allow this for certain type names, but Postgres
extends that to allow any known type name to be used.)  This is not a
function and it doesn't work for any case except where the second part
is a quoted literal string.  Personally I think it's a wart best
avoided, but we're stuck with supporting it because of the standard.

            regards, tom lane