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

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.AbstractJdbc2Statement.execute
> (AbstractJdbc2Statement.java:392)
>     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
> (AbstractJdbc2Statement.java:330)
>     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery
> (AbstractJdbc2Statement.java:240)
>
>
> _______________________________________________
> Pgjdbc-commit mailing list
> Pgjdbc-commit@gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pgjdbc-commit
>

Dave Cramer <pg@fastcrypt.com> writes:
> On 6-Nov-05, at 9:01 AM, William Harris wrote:
>> intervalStmt = con.prepareStatement( "select current_date +
>> (interval ?)" );

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            (Postgres-ism)
See
http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-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