[jsr-221-eg] SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Mark Rotteveel]

On 9-10-2018 22:05, Lance Andersen wrote:

> Well with the SQL Standard evolving to include MATCH RECOGNIZE, this is 
> now an issue  to discuss as it will impact any vendor implementing this 

I think - in the case of MATCH_RECOGNIZE - this should only be necessary 
for database systems that do not use `?` as a native parameter marker 
(as the driver will need to do a translation to the dialect specific 
parameter markers), while dialects that natively use `?` should have no 
problem(*) as they can send the query to the server for prepare and get 
back the number and types of actual parameters. The `?` in the 
MATCH_RECOGNIZE pattern are in a position where it is - AFAIK - 
syntactically not possible to have a parameter. In that case there is no 
ambiguity that requires escaping.

*: That assumes they use the server for prepare, and don't try to 
'count' parameters themselves to avoid roundtrips.

Theoretically, the need to escape could be avoided by having a full 
statement parser within the driver, but that is a painful matter with 
regard to backward and forward compatibility (not to mention edge cases 
between different parser types or implementations).

[..]

> The decision can still be to do nothing, come up with a different syntax 
> and/or alternative.  This is not the first time a spec, not just JDBC, 
>   has revisited a discussion that was tabled.

Given a similar issue was raised earlier for PostgreSQL, I think we 
should standardize this.

Mark
-- 
Mark Rotteveel