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

On 19 May 2015 at 10:23, 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?
>
> > ​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!
>
> The JDBC spec provides for escapes using curly braces (including
> product-specific escapes); it seems like a big mistake for us to
> have chosen a completely different mechanism for escaping the
> question mark character in a SQL statement. Perhaps the least
> painful path would be to add support for {?} as the escape for a
> question mark, and a connection option to supplement that with
> support for the legacy \? escape. I would bet a lot of money that
> even with an "if" test for that option, the curly brace escape
> would be faster than what's there now (when the option was not
> set). Some operators would look a little funny in Java string
> literals, but that's not so bad.
>

Perhaps reviewing https://github.com/pgjdbc/pgjdbc/pull/187 might help
understand why we chose ??

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca