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