Re: Problems with question marks in operators (JDBC, ECPG, ...)

Not sure what the point of this is: as you indicated the ship has sailed so
to speak

Dave Cramer

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

On 15 May 2015 at 15:14, Bruno Harbulot <bruno(at)distributedmatter(dot)net> wrote:

> Hello,
>
> I've been trying to use the new JSONB format using JDBC, and ran into
> trouble with the question mark operators (?, ?| and ?&).
> I realise there has already been a discussion about this (actually, it was
> about hstore, not jsonb, but that's more or less the same problem):
> - http://www.postgresql.org/message-id/51114165.4070106@abshere.net
> -
> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html
>
>
> From what I gather, the JDBC team seems to think that using ? in operators
> is not in line with the SQL standards, but the outcome on the PostgreSQL
> list team suggested that a fix could be implemented in the PostgreSQL JDBC
> driver anyway.
>
> I think this problem might actually affect a number of other places,
> unfortunately. I must admit I don't know the SQL specifications very well
> (a quick look at a draft seemed to suggest the question mark was indeed a
> reserved character, but this is probably out of context), and this isn't
> about finding out who is right or who is wrong, but from a practical point
> of view, this also seemed to affect other kinds of clients, for example:
> - Perl:
> http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html
> - JavaScript: https://github.com/tgriesser/knex/issues/519
> Of course, there can be workarounds in some cases, but even if they work,
> they can be quite awkward, especially if they differ from one language to
> another (in particular if you want to be able to re-use the same query from
> multiple languages).
>
> As far, as I can tell, question mark operators are also incompatible with
> PostgreSQL's ECPG when using dynamic SQL.
> http://www.postgresql.org/docs/current/static/ecpg-dynamic.html
> (I'm pasting an example at the end of this message, tried with a
> PostgreSQL 9.4 server.)
>
> I realise it's a bit late to raise this concern, considering that these
> operators have been around for a few versions now (at least as far as
> hstore), but wouldn't it be better to provide official alternative
> notations altogether, something that is less likely to conflict with most
> client implementations? Perhaps a function or a notation similar to what
> 'CAST(x AS y)' is to 'x::y' would be suitable if other symbols aren't
> better (although I think a short operator would still be preferable).
>
>
> Best wishes,
>
> Bruno.
>
>
>
>
> ____ ECPG test output:
>
> ** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->>
> ?::text)::text
>
> Result should be 123 for 'key1': 123
> Result should be empty for 'key3':
>
>
> ** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ?
> ?::text)::text
>
> SQL error: syntax error at or near "$1" on line 52
> SQL error: invalid statement name "mystmt3" on line 55
> Result should be true for 'key1':
> SQL error: invalid statement name "mystmt3" on line 59
> Result should be false for 'key3':
> SQL error: invalid statement name "mystmt3" on line 62
>
>
>
> ____ ECPG test code:
>
>
> #include <stdio.h>
> #include <stdlib.h>
>
> int main()
> {
> EXEC SQL BEGIN DECLARE SECTION;
> char* target = "unix:postgresql://localhost/mydatabase";
> char result1[2048];
> int result1_ind;
> char *key1_str = "key1";
> char *key3_str = "key3";
> char *stmt2 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb
> ->> ?::text)::text";
> char *stmt3 = "SELECT ('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb
> ? ?::text)::text";
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL WHENEVER SQLWARNING SQLPRINT;
> EXEC SQL WHENEVER SQLERROR SQLPRINT;
> EXEC SQL CONNECT TO :target AS testdb;
>
>
> printf("\n\n** Using query: %s\n\n", stmt2);
> EXEC SQL PREPARE mystmt2 FROM :stmt2;
>
> result1[0] = 0;
> EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key1_str;
> printf("Result should be 123 for 'key1': %s\n", result1);
>
> result1[0] = 0;
> EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;
> printf("Result should be empty for 'key3': %s\n", result1);
>
> EXEC SQL DEALLOCATE PREPARE mystmt2;
>
>
> printf("\n\n** Using query: %s\n\n", stmt3);
> EXEC SQL PREPARE mystmt3 FROM :stmt3;
>
> result1[0] = 0;
> EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;
> printf("Result should be true for 'key1': %s\n", result1);
>
> result1[0] = 0;
> EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;
> printf("Result should be false for 'key3': %s\n", result1);
>
> EXEC SQL DEALLOCATE PREPARE mystmt3;
>
> EXEC SQL DISCONNECT ALL;
>
> return 0;
> }
>