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 <br...@distributedmatter.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.4070...@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; > } >
