Thread: Problem with 8.1 driver

Hi,

I am running postgresql-8.1-404.jdbc3.jar against server version 7.4.7,
which I want to eventually upgrade to 8.1.

Right now I am testing our application with the 8.1 driver and found an
issue with a prepared query. (Btw. I am using a regular connection URL
without any special parameters set.)

The offending code looks like this:

String DELETE_QUERY = "DELETE FROM server_log WHERE level_value"
    + " <= ? AND event_time < NOW() - interval ?";

PreparedStatement stmt = con.prepareStatement(DELETE_QUERY);
stmt.setInt(1, level.intValue());
stmt.setString(2, "1 day");

int deleted = stmt.executeUpdate();

Using the 7.4 driver this worked just fine. Now with the new driver I get:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
...

Server statement log shows:
DELETE FROM server_log WHERE level_value <= $1 AND event_time < NOW() -
interval $2

Is this a bug, or is the driver just not compatible with the server? Should
not be so because both speak the V3 protocol. Thanks for your help.

Best Regards,
Michael Paesold

On Mon, 5 Dec 2005, Michael Paesold wrote:

> Hi,
>
> I am running postgresql-8.1-404.jdbc3.jar against server version 7.4.7, which
> I want to eventually upgrade to 8.1.
>
> String DELETE_QUERY = "DELETE FROM server_log WHERE level_value"
>   + " <= ? AND event_time < NOW() - interval ?";
>

The syntax "interval ?" may not be used in prepared queries.  This is a
limitation of the backend which you can see using PREPARE/EXECUTE at the
SQL level.  The 8.0 driver started using true prepared queries instead of
directly interpolating parameter values and issuing a regular sql
statement.  You must use "CAST(? AS INTERVAL)" or "?::interval".

Kris Jurka

Kris Jurka wrote:

> On Mon, 5 Dec 2005, Michael Paesold wrote:
>
>> Hi,
>>
>> I am running postgresql-8.1-404.jdbc3.jar against server version 7.4.7,
>> which I want to eventually upgrade to 8.1.
>>
>> String DELETE_QUERY = "DELETE FROM server_log WHERE level_value"
>>   + " <= ? AND event_time < NOW() - interval ?";
>>
>
> The syntax "interval ?" may not be used in prepared queries.  This is a
> limitation of the backend which you can see using PREPARE/EXECUTE at the
> SQL level.  The 8.0 driver started using true prepared queries instead of
> directly interpolating parameter values and issuing a regular sql
> statement.  You must use "CAST(? AS INTERVAL)" or "?::interval".

Ah, I understand.

PREPARE p1 (integer,interval) AS DELETE FROM server_log
WHERE level_value  <= $1 AND event_time < NOW() - interval $2;
ERROR:  syntax error at or near "$2" at character 118

After playing with ?protocolVersion I figured that the new driver is using
the real V3 protocol. Using V2 (with the driver parsing the arguments, the
query works just fine).

Another question that occurred to me is, how should I tell the driver to
specify a type as "undefined" or "unknown" to the server?

Some random tries that did not work:
stmt.setObject(2, "1 month", java.sql.Types.OTHER);
--> Cannot cast an instance of String to type Types.OTHER

stmt.setObject(2, new PGUnknown("1 month"));
--> don't know type for PGUnknown; use setObject(int,Object,int)

stmt.setObject(2, new PGUnknown("1 month"), Types.OTHER);
Cannot cast an instance of PGUnknown to type Types.OTHER

I guess there is currently no way to do this?

Best Regards,
Michael Paesold

On Tue, 6 Dec 2005, Michael Paesold wrote:

> Another question that occurred to me is, how should I tell the driver to
> specify a type as "undefined" or "unknown" to the server?
>
> Some random tries that did not work:
> stmt.setObject(2, "1 month", java.sql.Types.OTHER);
> --> Cannot cast an instance of String to type Types.OTHER

You can use

stmt.setObject(2, new org.postgresql.util.PGInterval("1 month"));

or you could try this which I have not tested:

PGobject o = new org.postgresql.util.PGobject("1 month");
o.setType("unknown");
stmt.setObject(2, o);

Kris Jurka

Kris Jurka wrote:
> On Tue, 6 Dec 2005, Michael Paesold wrote:
>
>> Another question that occurred to me is, how should I tell the driver
>> to specify a type as "undefined" or "unknown" to the server?
>>
>> Some random tries that did not work:
>> stmt.setObject(2, "1 month", java.sql.Types.OTHER);
>> --> Cannot cast an instance of String to type Types.OTHER
 >
> You can use
>
> stmt.setObject(2, new org.postgresql.util.PGInterval("1 month"));

Thanks, that works. Altough I would prefer a way to do this in a
standard way (without org.postgresql classes). The real problem here is
probably that there is no Types.INTERVAL. What a shame.

Do you think it would be reasonable for the driver to implementing
 > stmt.setObject(2, "1 month", java.sql.Types.OTHER);
as sending the parameter as "unknown"?
I.e. would a patch implementing this be accepted? Or is Types.OTHER
reserved for a different meaning?

Best Regards,
Michael Paesold

Dave Cramer wrote:
> The latest CVS has a parameter (stringtype = String)  in the URL to send
> strings as unknown

I saw that before. Nevertheless I do not want to send all strings as
unknown, so I prefer the answer that Kris gave :-). Thanks, anyway.

Best Regards,
Michael Paesold