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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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







Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8
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 <[hidden email]> 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








Reply | Threaded
Open this post in threaded view
|

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

rapidtransit440
In reply to this post by Peter Mortier

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 <[hidden email]> 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







Reply | Threaded
Open this post in threaded view
|

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

Peter Mortier
In reply to this post by Dave Cramer-8
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 <[hidden email]> 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 <[hidden email]> 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









Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-8
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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