Thread: cannot dollar-quote $$?$$ in PreparedStatements

With 8.0 postgresql and driver, this works fine:
ps = con.prepareStatement("insert into product values(?, '?' , ?)");

But this fails:
ps = con.prepareStatement("insert into product values(?, $$?$$ , ?)");

It looks like "someone" (driver or engine?) is counting 3 parameters instead of 3:

org.postgresql.util.PSQLException: Pas de valeur specifiee pour le parametre 3.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:102)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:166)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282)
    at TestFetchSize.main(TestFetchSize.java:86)


How dollar-quoting and question marks are supposed to interact?
Sorry if this a well-known issue.

On Wed, 11 Jan 2006, Marc Herbert wrote:

>
> With 8.0 postgresql and driver, this works fine:
> ps = con.prepareStatement("insert into product values(?, '?' , ?)");
>
> But this fails:
> ps = con.prepareStatement("insert into product values(?, $$?$$ , ?)");
>
> It looks like "someone" (driver or engine?) is counting 3 parameters
> instead of 3:
>

This is a known issue.  The driver has no knowledge of dollar quotes in
its query parser.  This is not just a problem for parameters, but also an
issue when people want to issue a create function command with
Statement.execute("CREATE FUNCTION f() RETURNS int AS $$ [a number of
statements separated by semicolons] $$ language plpgsql");  The driver
will detect the semicolons and try to execute each fragment separately.
So this is a know issue with the only workaround being to not use dollar
quotes.

Kris Jurka