Postgres uses ? as an operator; may its JDBC driver allow escaping?

[Lance Andersen - Oracle]

On Feb 12, 2013, at 3:55 AM, Mark Rotteveel wrote:

> On Mon, 11 Feb 2013 16:10:27 -0500, Lance Andersen - Oracle
> <Lance.Andersen at oracle.com> wrote:
>> Currently the intent in the JDBC spec is that the ? only be used as a
>> parameter marker and at this time there is no plans to change that as I
> do
>> not see the demand (this is the 1st time this question has ever been
> asked
>> in 15 years of this spec).  I also just checked the latest ODBC spec and
> I
>> have not seen them make any changes to escape  the parameter marker.
> 
> Clear, so we can conclude that using '?' for anything other than a
> parameter placeholder is not allowed (outside of comments or quoted text);
> this might be something to state explicitly in the spec (in section 13.2).


From a portability point of view, yes that would be true.  Parameter markers are not new and have been around for ages in the SQL Standard.  Its unfortunate that Postgres chose not to follow the sql standard for parameter markers if I understand you correctly

Keep in mind that all vendors have various SQL extensions so if Postgres wants to find a way to parse this syntax in their driver that is an option, it is not expected via the standard but they are welcome to do so

>>> So escaping or an alternative operator would be better. To be a bit
> like
>>> the JDBC defined escapes you could think about {ve ?} or {ve "?"} (ve
> for
>>> vendor escape, just made it up). For an alternative operator I think an
>>> escape would be preferable to make it clear it is not in the actual
>>> PostgreSQL language (say: {qm} or {ve qm} (qm for questionmark)).
>> 
>> Nothing prevents the Postgres JDBC driver from creating a vendor
> specific
>> way to accomplish this just care has to be taken in the design so that
> it
>> will not conflict with potential new features in JDBC going forward.
> 
> Predicting the future is hard to do, so designing something now that might
> conflict with future JDBC or SQL changes is entirely possible. My
> suggestion is that JDBC should define a 'vendor escape' (see above for an
> example), where the argument(s) and result of the escape is entirely up to
> the vendor.

Postgres could always add their own special syntax such as {postgres blah} or only support it via a class/method available within their own implementation.  Many have done this type of thing for years.
> 
> This vendor escape would be optional, driver vendors should be
> discouraged(!) to implement it but they could use it to do non-standard
> things (although escaping the ?-operator is the only application that comes
> to mind right now). This prevents a general requirement for driver vendors
> to implement something that is not useful for them (eg by providing a
> standard escape for the question mark), but will provide 1) a way out if
> you really need it and 2) a clear guideline from the perspective of JDBC.
> 
> This would leave PostgreSQL with various options to implement the escape
> for this query:
> SELECT * FROM tbl WHERE tbl.data ? 'abc'
> 
> Simple question mark escape:
> SELECT * FROM tbl WHERE tbl.data {ve qm} 'abc'
> 
> or to be more in line with the function escapes:
> SELECT * FROM tbl WHERE {ve qm(tbl.data, 'abc')}
> 
> or even (although I'd guess that might be a headache to implement in their
> parser):
> SELECT * FROM tbl WHERE {ve tbl.data qm 'abc'}

Adding an special escape syntax for an operator or specially for vendors has never come up before and I would need to see more support from other JDBC driver vendors.  This would not happen for JDBC 4.2 but can be considered for a future JDBC release if there is enough support from the other vendors.

Best
Lance
> 
> Mark

-------------- next part --------------

Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
Oracle Java Engineering 
1 Network Drive 
Burlington, MA 01803
Lance.Andersen at oracle.com