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

[Mark Rotteveel]

On 2-11-2018 18:24, Douglas Surber wrote:
> The proposal is that text between ‘{\’ and ‘\}’ is not processed to recognize and expand JDBC specific syntax. In particular this means JDBC escape sequences and ‘?’ parameter markers. ‘{\’ and ‘\}’ are not recognized inside comments, literals or double quoted identifiers. Occurrences of ‘\’ between ‘{\’ and ‘\}’ other than inside comments, literals, or double quoted identifiers are doubled. It is vendor dependent whether use of ‘{\’ and ‘\}’ is required. If an implementation can distinguish  JDBC tokens from SQL tokens without ‘{\’ and ‘\}’ they may be omitted. An implementation must support their use even if not required.
> 
> Examples:
> 
> 	String literal							character sequence
> 	“{\\ foo \\}”							« foo »
> 	“{\\ {ts '1999-01-09 20:11:11.123455’} \\}		« {ts '1999-01-09 20:11:11.123455’} »
> 	“{\\ ? \\}”								« ? »
> 	“{\\ \\\\ \\}”								« \ »
> 	“{\\ /* \\} */ \\}”							« /* \} */ »
> 	“{\\ // \\} \n \\}”							« // \} ↵ »
> 	“{\\ ‘\\}’ \\}”								« ‘\}’ »
> 	“{\\ q’{\\ foo \\}’ \\}						« q’{\ foo \}’ »
> 	“{\\ \”\\}\” \\}”							« “\}” »
> 	“{\\ \\ \\}”								SQLException

In above examples, the fancy quotes (‘’) need to be replaced with '.

> I have discussed this with some folks from Oracle’s SQL standards team. They think it is reasonable to have the SQL standard somehow reserve this syntax in the SQL spec for use by preprocessors. I think there is a good chance that the JDBC EG and the SQL Standard Committee can agree on a syntax and reserve it in SQL. We should be able to solve this problem once and for all.
> 
> There are more additions coming to SQL that use ‘?’, ‘{‘, and ‘}’. This problem will just get worse.
> 
> There is at least one open issue. Should the JDBC spec say anything about how ‘?' would be handled by implementations where the underlying database uses ‘?’ as a parameter marker? It might be reasonable to say that any ‘?’ that is a parameter marker must not be escaped. Whether or not a ‘?’ that is not a parameter marker must be escaped is vendor dependent.

For database systems that use ? as a parameter marker, escaping ? or not 
would not really be relevant, as `where x = ?` and `where x = {\?\}` 
would result in the same query sent and prepared. On the other hand, for 
database systems that use an alternate parameter marker, the first would 
work and the second would likely be a syntax error.

It might be advisable to explicitly state that question marks intended 
as parameter markers must not be escaped, and question marks not 
intended as parameter markers should be escaped (difference between 
'must not' and 'should' intentional to allow leniency for 
implementations where there would be no ambiguity and escaping would not 
be necessary).

> Consider a database that uses ‘?’ as a parameter marker and supports MATCH RECOGNIZE. The SQL syntax is such that the database can distinguish between a parameter marker and a quantifier. This requires a full SQL parser. By including the above restrictions a driver can scan the SQL for parameter markers without implementing a full SQL parser or being tripped up by a ‘?’ used as a quantifier. The JDBC should say something, at least that it is vendor dependent. It would be better though to give users specific direction.

See above.

Other than that, I'm okay with this.

Mark

-- 
Mark Rotteveel