Problem with 8.1 driver
Michael Paesold
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Kris Jurka
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
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".
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
Dave Cramer
Kris Jurka
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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"));
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
Michael Paesold
> 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
---------------------------(end of broadcast)---------------------------