PostgreSQL JDBC - Re: [Pgjdbc-commit] ERROR: syntax error at or near "$1"

Author

Re: [Pgjdbc-commit] ERROR: syntax error at or near "$1"

Dave Cramer

2005-11-08, 4:12 pm

I'm forwarding to the pgsql list, this list is only for committers.

Dave
On 6-Nov-05, at 9:01 AM, William Harris wrote:

> Summary: PreparedStatement positional parameters not working as
> usefully as in version 7.
>
> Driver: http://jdbc.postgresql.org/download/
> postgresql-8.0-314.jdbc3.jar
> PostgreSQL: 8.0.4
>
> We have just migrated our application to version 8.04 of postgresql
> (from version 7), but are getting some issues with version 8
> postgresql jdbc driver when using prepared statements.
>
> Here are some examples of code that used to work pre version 8,
> which fails now;
>
> Ex 1;
> setSeqScanStmt = con.prepareStatement( "set enable_seqscan=?" );
> setSeqScanStmt.setString(1,"on");
> ResultSet rs = setSeqScanStmt.executeQuery();
>
> Ex 2;
> intervalStmt = con.prepareStatement( "select current_date +
> (interval ?)" );
> intervalStmt .setString(1,"5 days");
> ResultSet rs = intervalStmt.executeQuery();
>
> Both these exmaple throw the;
> ERROR: syntax error at or near "$1"
> exception
>
> If the later example can no longer be done it will be a big
> drawback in usabilty for this driver!
>
>
>
>
>
>
>
> java.sql.SQLException: ERROR: syntax error at or near "$1"
> at org.postgresql.core.v3.QueryExecutorImpl. receiveErrorResponse

> (QueryExecutorImpl.java:1471)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults
> (QueryExecutorImpl.java:1256)
> at org.postgresql.core.v3.QueryExecutorImpl.execute
> (QueryExecutorImpl.java:175)
> at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt.execute
> (AbstractJdbc2Statem
ent.java:392)
> at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt.executeWithFlags
> (AbstractJdbc2Statem
ent.java:330)
> at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt.executeQuery
> (AbstractJdbc2Statem
ent.java:240)
>
>
> ____________________
____________________
_______
> Pgjdbc-commit mailing list
> Pgjdbc-commit@gborg.postgresql.org
> http://gborg.postgresql.org/mailman...o/pgjdbc-commit
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-11-08, 4:12 pm

Dave Cramer <pg@fastcrypt.com> writes:[color=darkre
d]
> On 6-Nov-05, at 9:01 AM, William Harris wrote:

The above is not and never has been correct syntax; the fact that it
failed to fail in previous JDBC versions was accidental. The syntax
interval 'literal'
(or in general any type name followed by a string literal) is defined
to work only for literals. The correct way to do this is either
CAST(? AS interval) (SQL standard)
? ::interval (Postgr
es-ism)
See
http://www.postgresql.org/docs/8.0/...YNTAX-CONSTANTS
particularly the note at the end of section 4.1.2.5.

Fraid you're out of luck on the SET example though :-(

regards, tom lane

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

http://archives.postgresql.org

Sponsored Links