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

The official build is imminent, it was supposed to be a few weeks ago, but
I got bogged down

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 20 January 2015 at 02:08, Peter Mortier <peter(dot)mortier(at)gmail(dot)com> wrote:

> 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>