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

On Tue, May 19, 2015 at 9:50 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, May 19, 2015 at 1:36 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
> Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>>
>> > I prefer the $1 approach, others can't use that, and there are
>> > situations where I could not either.
>> >
>> > So, how about defaulting to the '?' approach, but have a method
>> > to explicitly set the mode - to switch to using '$'?
>>
>> Are you suggesting that we implement something other than what is
>> described in these documents for prepared statement parameters?:
>>
>> http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
>>
>>
>> http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf
>>
>> If so, I strongly oppose that. If we are not going to deprecate
>> use of the question mark character for operators, we need some
>> nonstandard hack to our JDBC implementation, but an alternative
>> syntax for specifying PreparedStatement and CallableStatement
>> parameters seems entirely the wrong way to go.
>>
>
> ​I'll repeat my earlier comment that having a mode that allows for libpq
> syntax while still conforming to the JDBC class API would have value for
> those users willing to admit their application and code is not portable
> (and if they are using these operators it is not) and would rather conform
> as closely to native PostgreSQL language mechanics as possible.​
>
>
I don't think that approach is workable at all. JDBC isn't limited to a
number of classes and their methods, the documentation that surrounds it
obviously has an impact on how it was implemented internally and what users
should and shouldn't be allowed to expect when using these classes. While
there are tools that convert various parameter styles to ? (e.g. Groovy SQL
or Hibernate's named parameter) and a layer of conversion from $1 to ?
could exist, the bottleneck here will still be the JDBC layer itself, since
it's what sends the query to the database.

Users of question mark operators are already admitting their application
and code isn't portable (since they are specific to PostgreSQL and its
extensions). The problem has more to do with how the other tools around
handle these customisations. For example, it can be useful to have a model
based on Hibernate in Java and be able to use ? operators for specific
features. (Other tools like SQLAlchemy in Python also allow you to have
customisations specific to the RDMBS platform, while being able to use the
core features in a more platform-neutral way.)

It turns out that you can indeed use ? in JSONB with a custom Hibernate
query, you just need to double-escape it as follows: ? becomes ?? and has
to be escaped as \?\?, but \ has to be escaped itself...

SQLQuery query = session
.createSQLQuery("SELECT
CAST((CAST('{\"key1\":123,\"key2\":\"Hello\"}' AS jsonb) \\?\\? CAST(? AS
text)) AS BOOLEAN)");
query.setString(0, "key1");

Again, this may have to do with the fact that these tools may have a
legitimate expectation that ? should be reserved for parameters, partly
because it seems to be very common in practice, but more importantly if the
SQL specification itself says it's what ? is for.

While I can imagine a Java PostgreSQL driver that would use the libpq
syntax, I can't see it being able to have any useful sort of
half-compatibility with JDBC, whether it mimics its interfaces or not. I'm
not sure it would be very useful at all, considering how much the existing
tooling the the Java world relies on JDBC.

This problem is also broader than JDBC: on top of the languages and
libraries already mentioned, it may affect ODBC, as Dave Cramer has just
said (I haven't tried).

Best wishes,

Bruno.