Thread: 8.0.3 parse errors where 7.x was ok

I'm getting a new exception when upgrading to Postgres 8.0.3 with the JDBC
drivers it builds:

java.sql.SQLException: ERROR: parse error at or near "$1"

This is happening with the following bit of code:

     105         try {
     106             s = c.prepareStatement("select now() - interval ?");
     107             s.setString(1, "7 days");
     108             ResultSet r = s.executeQuery();

The driver was built from postgresql-jdbc-8.0-311.src using the FreeBSD
port on FreeBSD 5.4 with Java 1.4.2-p7.

Using the older jar built for postgresql-7.3.4 works fine.

Is this syntax no longer supported?

    Nick

--
"The aptly-named morons.org is an obscenity-laced screed..."
     -- Robert P. Lockwood, Catholic League director of research
Nick Johnson, version 2.1                             http://web.morons.org/

On Tue, 9 Aug 2005, Nick Johnson wrote:

> I'm getting a new exception when upgrading to Postgres 8.0.3 with the JDBC
> drivers it builds:
>
> java.sql.SQLException: ERROR: parse error at or near "$1"
>
>      105         try {
>      106             s = c.prepareStatement("select now() - interval ?");
>      107             s.setString(1, "7 days");
>      108             ResultSet r = s.executeQuery();
>
> Is this syntax no longer supported?
>

Yes.  The 8.0 driver always uses server side prepared statements when in
the past the statements were usually put together on the driver side.
The using a type prefix (like "interval") is not supported in server
prepared statements, so this is a server limitation that is now exposed
with the new driver.  I believe "?::interval" or "cast(? as interval)"
should work.

Kris Jurka

Nick Johnson wrote:
> I'm getting a new exception when upgrading to Postgres 8.0.3 with the
> JDBC drivers it builds:
>
> java.sql.SQLException: ERROR: parse error at or near "$1"
>
> This is happening with the following bit of code:
>
>     105         try {
>     106             s = c.prepareStatement("select now() - interval ?");
>     107             s.setString(1, "7 days");
>     108             ResultSet r = s.executeQuery();

Because the driver now pushes parameter handling to the backend, you can
only put ? placeholders where there is a PARAM terminal in the backend's
SQL grammar. Previously the driver just substituted parameter values
directly into the query, so you could get away with all sorts of things.

Perhaps this will work instead:

  s = c.prepareStatement("select now() - ?");
  s.setObject(new org.postgresql.util.PGInterval("7 days"));

-O