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

On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner <kgrittn@ymail.com> wrote:

David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <bruno@distributedmatter.net>wrote:

>> In the discussion on the OpenJDK JDBC list two years ago
>> ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000050.html ),
>> Lance Andersen said "There is nothing in the SQL standard that
>> would support the use of an '?' as anything but a parameter
>> marker.".

> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> provisions for user-defined operators in the SQL standard."

Exactly.  The standard specifies the characters to use for the
predicates that it defines, and provides no mechanism for adding
additional predicates; but who in the world would want to exclude
all extensions to the standard?

I was certainly not suggesting custom operators should be excluded. I was suggesting using something that was actually not incompatible with the SQL standards (and, even with standards aside, the expectations implementors have regarding the question mark, since it affects other tools too).
 

> ​And by extension if indeed the standard does require the use of
> "?" for parameters we are in violation there because the backend
> protocol deals with $# placeholders and not "?"​

We're talking about a different specification that has question
marks as parameter placeholders.  That's in the Java Database
Connector (JDBC) specification.  (It is apparently also specified
in other documents, although I'm not familiar enough with those to
comment.)  Note that it would create all sorts of pain if both the
SQL statements and a connector issuing them used the same
convention for substituting parameters; it is a *good* thing that
plpgsql and SQL function definitions use a different convention
than JDBC!

Actually, we were not just talking about JDBC. I don't know the specifications in details, but the SQL:201x (preliminary) documents linked from https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F seem to have some information. The Foundation document (Section 4.25 Dynamic SQL concepts) says that dynamic parameters are represented by a question mark.

In addition, the BNF grammar available at http://www.savage.net.au/SQL/sql-2003-2.bnf.html#dynamic%20parameter%20specification

also says:
    <dynamic parameter specification>    ::=   <question mark>

I'm not familiar enough with these documents to know whether I'm missing some context, but it would seem that the question mark is a reserved character, beyond the scope of JDBC (at the very least, it seems incompatible with Dynamic SQL and its implementation in ECPG).

Best wishes,

Bruno.