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

[Lance Andersen - Oracle]

On Feb 11, 2013, at 3:44 PM, Mark Rotteveel wrote:

> On 11-2-2013 21:11, Lance Andersen - Oracle wrote:
>> There is nothing in the SQL standard that would support the use of an '?' as anything but a parameter marker.  While you may be able to make this work with just a Statement object, when you get to a PreparedStatement, I am not sure how you will be able to support  this as an operator as there is no way in the SQL standard to escape the '?'  as a parameter marker.
> 
> I believe the question of Seamus is more: 'Would the JDBC spec allow us to treat a question mark in a query string as anything other than a parameter placeholder?' and alternatively: 'If it isn't allowed by the JDBC spec, in what way can we escape it?'

I understand what the question was, however I was explaining the reason for why and where this requirement comes from.  Many JDBC drivers rely on the backend to handle the bindings as we have had the discussion of the support of named parameters which the EG decided against for this vary reason
> 
> Lets be practical: the SQL standard defines a lot of things that don't match with reality, and PostgreSQL does actually have the ? as an operator which currently isn't supported by their JDBC driver. PostgreSQL itself - AFAIK - doesn't actually use the ? as the parameter placeholder, so their JDBC driver needs to do the parsing and replacing question marks with the actual placeholders.
> 
> I think supporting the ? operator directly might be possible (if JDBC allows that), but I guess that would really complicate their parser (because you will need to a lot more context and state to get it right).

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.
> 
> 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.
> 
> Mark
> -- 
> Mark Rotteveel

-------------- 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