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

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

From

Peter Mortier

Date:

19 January 2015, 20:36:50

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

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

From

Dave Cramer

Date:

19 January 2015, 21:28:40

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.mortier@gmail.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

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

From

rapidtransit440@aol.com

Date:

19 January 2015, 23:25:37

As a temporary work maybe try introducing a new operator. JSONB stuff is fairly new and the development team has to make absolutely sure any changes will not break customers applications I think they have paid support customers for versions going back to version 7

Sent from AOL Mobile Mail

On Monday, January 19, 2015 Peter Mortier <peter.mortier@gmail.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.