Re: problem with pgjdbc prepared statements and new jsonb exists operator (?)

Thanks David,

It is working against latest master when I use a double question mark to
'escape' the exists function:

connection.prepareStatement("select x2.\"json\" from \"JsonTest0\" x2 where
(x2.\"json\" ?? 'c') = true").executeQuery()

Any idea when there will be an official build supporting this?

BR,

Peter Mortier

On 19 January 2015 at 22:28, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> Peter,
>
> Can you pull from head, this should work
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 19 January 2015 at 15:36, Peter Mortier <peter(dot)mortier(at)gmail(dot)com>
> wrote:
>
>> Hello,
>>
>> I'm testing out the new jsonb functionality in Postgresql 9.4 and ran
>> across an issue with some of the newly introduced operators for the jsonb
>> datatype described here (
>> http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE)
>> when used with JDBC prepared statements.
>>
>> I have created the following table:
>>
>> create table "JsonTest0" ("id" BIGSERIAL NOT NULL PRIMARY KEY,"json"
>> jsonb DEFAULT ' {"a":"v1","b":2} ' NOT NULL)
>>
>> When I'm creating and executing a simple statement using the exist
>> operator then I get expected results back:
>>
>> connection.createStatement.executeQuery("select x2.\"json\" from
>> \"JsonTest0\" x2 where (x2.\"json\" ? 'c') = true")
>>
>> When I try to execute the same query using a prepared statement, I get
>> the following:
>>
>> connection.prepareStatement("select x2.\"json\" from \"JsonTest0\" x2
>> where (x2.\"json\" ? 'c') = true").executeQuery()
>>
>> org.postgresql.util.PSQLException: No value specified for parameter 1.
>> at
>> org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:216)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
>>
>> So it looks like the ? character, which is used as the jsonb exists
>> operator is confusing the prepared statement parser, which treats it as a
>> parameter substitution instead.
>>
>> Is this expected behaviour and am I stuck with non-prepared statements
>> when using the new ?, ?& and ?| jsonb operators ?
>> Any other workarounds, like escaping or function aliases that you may
>> know of ?
>>
>> I tested with both: 9.3-1100-jdbc41 and 9.4-1200-jdbc41-SNAPSHOT versions
>>
>> BR,
>>
>> Peter Mortier
>>
>>
>>
>>
>>
>>
>>
>>
>