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

On Tue, May 19, 2015 at 3:23 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <
> bruno(at)distributedmatter(dot)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.