Thread: JDBC and the hstore ? operator - no longer working with build 1211

The support for using the hstore ? operator without a PreparedStatement worked with build 1208, but is broken (again)
withbuild 1211 

The following works with build 1208

   Connection conn = DriverManager.getConnection(...);
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select * from some_table where hstore_column ? 'foo');

with build 1211 it throws an exception with the message:

    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

Was this intended to work in build 1208 or was that just by accident?

Thomas

Dave Cramer schrieb am 01.11.2016 um 15:03:
>     I suggest we should cut 1212 as of now, and consider other PRs for 1213
>
>     Thomas, can you please confirm that the issue is not there in 9.4.1212-SNAPSHOT?
>
>
> as soon as you do we can release 1212

Works with 1212, thanks.

I tried it with


https://oss.sonatype.org/content/repositories/snapshots/org/postgresql/postgresql/9.4.1212-SNAPSHOT/postgresql-9.4.1212-20161029.195806-15.jar

But that JAR file is missing the META-INF/services directory.

I don't know if that is due to it being a snapshot or if that is a general problem with the build system.

It should be added before a final release.

Thomas

Dave Cramer schrieb am 01.11.2016 um 15:03:
>     I suggest we should cut 1212 as of now, and consider other PRs for 1213
>
>     Thomas, can you please confirm that the issue is not there in 9.4.1212-SNAPSHOT?
>
>
> as soon as you do we can release 1212

Works with 1212, thanks.

I tried it with


https://oss.sonatype.org/content/repositories/snapshots/org/postgresql/postgresql/9.4.1212-SNAPSHOT/postgresql-9.4.1212-20161029.195806-15.jar

But that JAR file is missing the META-INF/services directory.

I don't know if that is due to it being a snapshot or if that is a general problem with the build system.

It should be added before a final release.

Thomas

Jorge Solórzano schrieb am 01.11.2016 um 23:47:
>>     But that JAR file is missing the META-INF/services directory.
>>     I don't know if that is due to it being a snapshot or if that is a general problem with the build system.
>
> Thomas, can you please check the latest 9.4.1212-SNAPSHOT? (postgresql-9.4.1212-20161101.224154-17.jar)

Looks good.

I don't need Class.forName() any more with that version.

Jorge Solórzano schrieb am 01.11.2016 um 23:47:
>>     But that JAR file is missing the META-INF/services directory.
>>     I don't know if that is due to it being a snapshot or if that is a general problem with the build system.
>
> Thomas, can you please check the latest 9.4.1212-SNAPSHOT? (postgresql-9.4.1212-20161101.224154-17.jar)

Looks good.

I don't need Class.forName() any more with that version.

Dave Cramer schrieb am 02.11.2016 um 12:03:
>     Jorge Solórzano schrieb am 01.11.2016 um 23:47:
>     >>     But that JAR file is missing the META-INF/services directory.
>     >>     I don't know if that is due to it being a snapshot or if that is a general problem with the build system.
>     >
>     > Thomas, can you please check the latest 9.4.1212-SNAPSHOT? (postgresql-9.4.1212-20161101.224154-17.jar)
>
>     Looks good.
>
>     I don't need Class.forName() any more with that version.
>
>
> I don't believe that has been required for quite some time ?

The first snapshot I was testing (postgresql-9.4.1212-20161029.195806-15.jar) did not contain the necessary service
informationin the META-INF/services directory in the jar file.  

The new snapshot fixed this.

Thomas

Dave Cramer schrieb am 02.11.2016 um 12:03:
>     Jorge Solórzano schrieb am 01.11.2016 um 23:47:
>     >>     But that JAR file is missing the META-INF/services directory.
>     >>     I don't know if that is due to it being a snapshot or if that is a general problem with the build system.
>     >
>     > Thomas, can you please check the latest 9.4.1212-SNAPSHOT? (postgresql-9.4.1212-20161101.224154-17.jar)
>
>     Looks good.
>
>     I don't need Class.forName() any more with that version.
>
>
> I don't believe that has been required for quite some time ?

The first snapshot I was testing (postgresql-9.4.1212-20161029.195806-15.jar) did not contain the necessary service
informationin the META-INF/services directory in the jar file.  

The new snapshot fixed this.

Thomas

Vladimir Sitnikov schrieb am 01.11.2016 um 15:00:
>>This has been "broken" for a while now, you have to use ?? to get it to work.

This still doesn't work completely with a PreparedStatement.

Other hstore operators that start with an ? do not work: ?& and ?|

e.g.:

   connection.prepareStatement("select * from foo where hstore_column ?& array['key']");
   connection.prepareStatement("select * from foo where hstore_column ?| array['key']");

The prepareStatement() call succeeds, but when calling executeQuery() an exception is thrown:

   org.postgresql.util.PSQLException: No value specified for parameter 1.

The goal is to pass the array as a parameter, something like:

   Array keys = con.createArrayOf("text", new String[] {"key1","key2"});
   connection.prepareStatement("select * from foo where hstore_column ?& ?");
   pstmt.setArray(1, keys);
   ResultSet rs = pstmt.executeQuery();

Tested with 1212 and 42.0.0

Regards
Thomas

Vladimir Sitnikov schrieb am 01.11.2016 um 15:00:
>>This has been "broken" for a while now, you have to use ?? to get it to work.

This still doesn't work completely with a PreparedStatement.

Other hstore operators that start with an ? do not work: ?& and ?|

e.g.:

   connection.prepareStatement("select * from foo where hstore_column ?& array['key']");
   connection.prepareStatement("select * from foo where hstore_column ?| array['key']");

The prepareStatement() call succeeds, but when calling executeQuery() an exception is thrown:

   org.postgresql.util.PSQLException: No value specified for parameter 1.

The goal is to pass the array as a parameter, something like:

   Array keys = con.createArrayOf("text", new String[] {"key1","key2"});
   connection.prepareStatement("select * from foo where hstore_column ?& ?");
   pstmt.setArray(1, keys);
   ResultSet rs = pstmt.executeQuery();

Tested with 1212 and 42.0.0

Regards
Thomas

On 2017-03-02 09:54, Thomas Kellerer wrote:
> Vladimir Sitnikov schrieb am 01.11.2016 um 15:00:
>>> This has been "broken" for a while now, you have to use ?? to get it
>>> to work.
>
> This still doesn't work completely with a PreparedStatement.
>
> Other hstore operators that start with an ? do not work: ?& and ?|
>
> e.g.:
>
>    connection.prepareStatement("select * from foo where hstore_column
> ?& array['key']");
>    connection.prepareStatement("select * from foo where hstore_column
> ?| array['key']");
>
> The prepareStatement() call succeeds, but when calling executeQuery()
> an exception is thrown:
>
>    org.postgresql.util.PSQLException: No value specified for parameter
> 1.
>
> The goal is to pass the array as a parameter, something like:
>
>    Array keys = con.createArrayOf("text", new String[]
> {"key1","key2"});
>    connection.prepareStatement("select * from foo where hstore_column
> ?& ?");
>    pstmt.setArray(1, keys);
>    ResultSet rs = pstmt.executeQuery();
>
> Tested with 1212 and 42.0.0

I haven't tried it, but would you logically need to use ??& and ??|, if
the ? operator is escaped using ??

Mark

On 2017-03-02 09:54, Thomas Kellerer wrote:
> Vladimir Sitnikov schrieb am 01.11.2016 um 15:00:
>>> This has been "broken" for a while now, you have to use ?? to get it
>>> to work.
>
> This still doesn't work completely with a PreparedStatement.
>
> Other hstore operators that start with an ? do not work: ?& and ?|
>
> e.g.:
>
>    connection.prepareStatement("select * from foo where hstore_column
> ?& array['key']");
>    connection.prepareStatement("select * from foo where hstore_column
> ?| array['key']");
>
> The prepareStatement() call succeeds, but when calling executeQuery()
> an exception is thrown:
>
>    org.postgresql.util.PSQLException: No value specified for parameter
> 1.
>
> The goal is to pass the array as a parameter, something like:
>
>    Array keys = con.createArrayOf("text", new String[]
> {"key1","key2"});
>    connection.prepareStatement("select * from foo where hstore_column
> ?& ?");
>    pstmt.setArray(1, keys);
>    ResultSet rs = pstmt.executeQuery();
>
> Tested with 1212 and 42.0.0

I haven't tried it, but would you logically need to use ??& and ??|, if
the ? operator is escaped using ??

Mark

Mark Rotteveel schrieb am 02.03.2017 um 19:10:
>>    connection.prepareStatement("select * from foo where hstore_column ?& array['key']");
>>    connection.prepareStatement("select * from foo where hstore_column ?| array['key']");
>>
>> The prepareStatement() call succeeds, but when calling executeQuery()
>> an exception is thrown:
>>
>>    org.postgresql.util.PSQLException: No value specified for parameter 1.
>>
>> The goal is to pass the array as a parameter, something like:
>>
>>    Array keys = con.createArrayOf("text", new String[] {"key1","key2"});
>>    connection.prepareStatement("select * from foo where hstore_column ?& ?");
>>    pstmt.setArray(1, keys);
>>    ResultSet rs = pstmt.executeQuery();
>>
>> Tested with 1212 and 42.0.0
>
> I haven't tried it, but would you logically need to use ??& and ??|, if the ? operator is escaped using ??

That indeed does work.

We first tried it with an obfuscation layer (aka "ORM") which seems to mess around with those operators and breaks that
escaping.

Thomas

Mark Rotteveel schrieb am 02.03.2017 um 19:10:
>>    connection.prepareStatement("select * from foo where hstore_column ?& array['key']");
>>    connection.prepareStatement("select * from foo where hstore_column ?| array['key']");
>>
>> The prepareStatement() call succeeds, but when calling executeQuery()
>> an exception is thrown:
>>
>>    org.postgresql.util.PSQLException: No value specified for parameter 1.
>>
>> The goal is to pass the array as a parameter, something like:
>>
>>    Array keys = con.createArrayOf("text", new String[] {"key1","key2"});
>>    connection.prepareStatement("select * from foo where hstore_column ?& ?");
>>    pstmt.setArray(1, keys);
>>    ResultSet rs = pstmt.executeQuery();
>>
>> Tested with 1212 and 42.0.0
>
> I haven't tried it, but would you logically need to use ??& and ??|, if the ? operator is escaped using ??

That indeed does work.

We first tried it with an obfuscation layer (aka "ORM") which seems to mess around with those operators and breaks that
escaping.

Thomas