BUG #2036: 8.1 JDBC busted date with INTERVAL update

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #2036: 8.1 JDBC busted date with INTERVAL update

Reuben Pasquini

The following bug has been logged online:

Bug reference:      2036
Logged by:          Reuben Pasquini
Email address:      [hidden email]
PostgreSQL version: 8.1
Operating system:   linux
Description:        8.1 JDBC busted date with INTERVAL update
Details:

Hello!

Something like this would work with the postgres-7.4
jdbc3.jar driver, but fails with the shown error with
postgres-8.1 jdbc3.jar driver.
Running with a non-prepared statement works
with 8.1.
I'm running 8.1 server on linux, jdk1.5.
Not sure if this is a bug, or intentional.

Thanks for the great work!
Reuben

----------
s_query = "UPDATE cue.proc SET dt_started = now() - INTERVAL ? WHERE pk=5";
x_stmt = x_conn.prepareStatement ( s_query );
x_stmt.setString ( 1, "0 seconds" );

x_stmt.executeUpdate ();
------------
  org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1512)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1297)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)

        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:430)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:346)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:300)
        at spijava.cue.CuProcUpdateCommand.doItSimple(CuProcUpdateCommand.java:224)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: BUG #2036: 8.1 JDBC busted date with INTERVAL update

Kris Jurka


On Fri, 11 Nov 2005, Reuben Pasquini wrote:

> Bug reference:      2036
> Description:        8.1 JDBC busted date with INTERVAL update
> Details:
>
> Something like this would work with the postgres-7.4
> jdbc3.jar driver, but fails with the shown error with
> postgres-8.1 jdbc3.jar driver.
> Running with a non-prepared statement works
> with 8.1.
> I'm running 8.1 server on linux, jdk1.5.
> Not sure if this is a bug, or intentional.
>
> s_query = "UPDATE cue.proc SET dt_started = now() - INTERVAL ? WHERE pk=5";
> x_stmt = x_conn.prepareStatement ( s_query );
> x_stmt.setString ( 1, "0 seconds" );
>

This is a known limitation of server side prepared statements (which were
added in the 8.0 JDBC driver).  You may not use the syntax "INTERVAL ?",
but must instead use "CAST(? AS INTERVAL)" or "?::interval".

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org