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

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;
}