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

Поиск
Список
Период
Сортировка
От Peter Mortier
Тема problem with pgjdbc prepared statements and new jsonb exists operator (?)
Дата
Msg-id CAFxe7BBHtgryUVZtrjKH3Bw9Ms7jkEL5KAz_Ui67Q=sFyBXSiw@mail.gmail.com
обсуждение исходный текст
Ответы Re: problem with pgjdbc prepared statements and new jsonb exists operator (?)  (Dave Cramer <pg@fastcrypt.com>)
Re: problem with pgjdbc prepared statements and new jsonb exists operator (?)  (rapidtransit440@aol.com)
Список pgsql-jdbc
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







В списке pgsql-jdbc по дате отправления:

Предыдущее
От: dmp
Дата:
Сообщение: Re: Problem with DATE
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: problem with pgjdbc prepared statements and new jsonb exists operator (?)