Thread: JDBC 7.4 to 8.1 regression problem

I just tried to switch from Postgres 7.4 to 8.1. Works fine as far as the
server is concerned, as long as I use the JDBC 7.4 driver in my program. If
I use the JDBC driver postgresql-8.1-407.jdbc3.jar I have the following
problem:

I use the following prepared statement:

[...]
    final static String SQL= "SELECT " +
[...]
      " WHERE " +
[...]
    + "   AND service_date <= ?" +
      " and service_date > date ? - interval '365 days'; "
[...]

This statement works fine with the 7.4 JDBC, but using

statement.setDate(5, someDate); // where someDate is a valid java.sql.Date

I get the following error message when executing the statement:

ERROR: syntax error at or near "$5"

The last "?" (before "- interval") is $5.

Playing around a bit (leaving out the " date " bit) I noticed that JDBC
seems to substitute the "?" by "2005-05-02 +1000". However, I don't know if
this is a good or bad thing.

Your help in this would be much appreciated.

Thanks,
Carsten

"Carsten Friedrich" <carsten@dtecht.com.au> writes:
> I use the following prepared statement:

>     final static String SQL= "SELECT " +
> [...]
>       " WHERE " +
> [...]
>     + "   AND service_date <= ?" +
>       " and service_date > date ? - interval '365 days'; "

The above is broken code, and always has been: the fact that it failed
to malfunction back in 7.4 was purely accidental.  You can only use the
syntax "typename literal" with a literal constant.  Instead of "date ?"
use "?::date", or if you want to be fully SQL-spec-compliant write it
out as "CAST(? AS date)".

Actually, it'd probably be better to use "date_trunc('day', ?)" anyway,
since that's what you're trying to achieve with the cast.

            regards, tom lane