Re: Problems with question marks in operators (JDBC, ECPG, ...) - Mailing list pgsql-hackers
| From | Bruno Harbulot |
|---|---|
| Subject | Re: Problems with question marks in operators (JDBC, ECPG, ...) |
| Date | |
| Msg-id | CANPVNBZKahyghPm=Hd40HOECCuVcT1tDqAhFpvsm83ZS0H_mwg@mail.gmail.com Whole thread Raw |
| In response to | Re: Problems with question marks in operators (JDBC, ECPG, ...) ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Problems with question marks in operators (JDBC, ECPG, ...)
Re: Problems with question marks in operators (JDBC, ECPG, ...) |
| List | pgsql-hackers |
On Tue, May 19, 2015 at 9:50 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Gavin Flower <GavinFlower@archidevsys.co.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");
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.
pgsql-hackers by date: