Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?

Question

From Sam Macbeth's question:

Is there anything in the JDBC spec which allows a ? to be escaped and be anything other than a parameter placeholder?

For example, Postgres allows you to use ? as an operator:

SELECT * FROM tbl WHERE tbl.data ? 'abc'

Would a JDBC driver that lets you use ? as an operator still be JDBC-compliant?

Answer 1

If you have the latest postgresql driver you can use:

??

Changing the original query:

SELECT * FROM tbl WHERE tbl.data ?? 'abc'

Details are in this pull request - https://github.com/pgjdbc/pgjdbc/pull/227 This change was made long after the original question, but it's worth noting that there's an easy answer now.

Answer 2

I think it would be perfectly acceptable if the JDBC driver would allow for the ? operator to be unescaped and used as it is, but it might 1) complicate your parser to actually identify parameters from this operator and 2) might confuse people (and maybe tools) who are used to ? only meaning parameter placeholder.

So my suggestion would be to provide some sort of escape (or alternative operator). However looking at the JDBC spec, drivers should only use the JDBC escape syntax to implement the escapes defined in the JDBC spec (13.4.2: "The escape syntax is not intended to be used to invoke user-defined or vendor specific scalar functions."; although this is specifically about the {fn ...} escape).

So either you need to use an alternative escape, or 'break' the rules (I don't think anyone would mind). If you want a more authorative answer, you could send your question to the jdbc-spec-discuss mailinglist. I am sure Lance Andersen (JDBC spec lead) will provide an answer.

Edit:

Also interesting to note that the JDBC spec section 6.2 (Guidelines and Requirements) says:

Drivers should provide access to every feature implemented by the underlying data source, including features that extend the JDBC API. The intent is for applications using the JDBC API to have access to the same feature set as native applications.

So based on that you should (not must) support the ?-operator, you just need to find a practical way to do it.

---

Update based on the discussion on jdbc-spec-discuss

According to Lance Andersen the JDBC specification follows the SQL specification with regard to question marks: they can only be used as parameter placeholders in query text (except of course in comments and quoted text), as such use of ? as in the PostgreSQL hstore operators wouldn't be allowed. (see this message)

The option available is to either provide an alias or an escape for the operator, provided this does not conflict with future changes (which is rather hard to do without clairvoyance ;). The best solution - to prevent problems with future JDBC changes - is probably a custom escape.

JDBC does not actually define a vendor escape, but Lance Andersen does suggest an escape that is similar to the JDBC escapes: {postgres <thing to be escaped>}; use of the vendorname or drivername in this escape will provide a form of namespacing that should prevent conflict with the specification. (see this message)

To be in line with 'normal' JDBC function escapes, I would suggest to define an escape that will allow the query in your question to be stated as:

SELECT * FROM tbl WHERE {postgres containskey(tbl.data, 'abc')}

I chose containskey based on the meaning of ? in the hstore documentation. Similar suggestions for ?& : containsallkeys) and for ?| : containsanykey. For consistency you could consider to do this for the other hstore operators as well.

You could also decide to only escape the question mark itself. For example escape with {postgres '?'} or {postgres qm} (qm for question mark). I do think readability is less than my function-escape suggestion:

SELECT * FROM tbl WHERE tbl.data {postgres '?'} 'abc'

---

Updated based on new proposal for JDBC v.Next (4.4?)

The next JDBC specification (likely numbered 4.4), will probably add an explicit JDBC escape to entirely escape fragments of a query, with the express intent to be able to escape question marks for database systems that don't natively use question marks for parameter markers and need to support other uses of question marks.

The proposed syntax would be {\ <thing-to escape> \} (which is already supported by the Oracle JDBC driver as a non-standard escape). With this syntax, a parameter marker could then be escaped with {\?\} (in a string literal: {\\?\\}), or escape a larger fragment for improved readability.

See also SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters and earlier discussion on the jdbc-spec-discuss mailing list.