Does the Jdbc Spec Prevent '' from Being Used as an Operator (Outside of Quotes)

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

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.

How to skip the ? in prepared statement

Escaping the first question mark by two question marks ??.

How do I escape a literal question mark ('?') in a JDBC prepared statement

The meaning of the ? is specified in the SQL specification, and the JDBC specification defers to the SQL specification for this.

A driver doesn't (and shouldn't) interpret a question mark in a literal as a parameter placeholder, as a question mark within a string literal is simply a character within the string literal. For more information look at chapter 5 of SQL:2011 Foundation (ISO-9075-2:2011).

So escaping is not necessary (nor possible).

Escaping hstore contains operators in a JDBC Prepared statement

Effectively, it looks like the java SQL parser is not hstore compliant.

But since the syntax c ? 'foo' is equivalent to exist(c, 'foo'), you can easily workaround this problem. Have a look at the following page to see what the verbose operators for hstore are.

Postgres hstore documentation

PostgreSQL jsonb, `?` and JDBC

As a workaround to avoid the ? operator, you could create a new operator doing exactly the same.

This is the code of the original operator:

CREATE OPERATOR ?(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true

Use a different name, without any conflicts, like #-# and create a new one:

CREATE OPERATOR #-#(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

SELECT '{"a":1, "b":2}'::jsonb #-# 'b'; -- true

Use this new operator in your code and it should work.

Check pgAdmin -> pg_catalog -> Operators for all the operators that use a ? in the name.

Slick plain SQL escape PostgreSQL json function

? is a placeholder for a parameter in JDBC (that's the level after Slick). You can escape ? specifically for PostgreSQL as ??|. There's useful discussion of this in SO 14779896 - Does the JDBC spec prevent '?' from being used as an operator.

An alternative to this convention would be to use non-symbolic alternatives: jsonb_exists_any. E.g.,

WHERE jsonb_exists_any(table.column, array['23', '12'])

---

Related Topics