SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Douglas Surber]

‘{‘ is also a valid SQL token.

Backslash is written with doubled backslashes. Since each backslash has to be doubled in a string literal that means four. Escape sequences are not recognized inside SQL character sequence literals or SQL comments so backslashes in literals or comments are not a problem.

> PATTERN(A{\\?\\} X{\\*?\\} Y{\\+?\\} Z{\\??\\}) " +  // use of escape sequence


  “  {\\PATTERN(A? X*? Y+? Z??\\} “ + // use of escape sequence

Note that MATCH RECOGNIZE is not the only problem. SQL 2016 add some JSON support which also uses ‘?’ as a token. And MATCH RECOGNIZE also uses ‘{‘ as a token so JDBC needs a way to include ‘{‘ as a SQL token. One of the uses of ‘{‘ is followed by an identifier so all of the currently defined JDBC escape sequences are at least the prefix of valid SQL.

Oracle Database JDBC has supported ‘{\ … \}’ as an escape sequence since 2013. We have not received any negative feedback from customers. All the uses that I have seen are to escape long sequences of tokens as in the rewritten example above or to escape exactly one ? in the entire SQL text. I have never seen multiple escapes as in the original example above in actual customer SQL text.

We chose ‘{\ .. \}’ because Java programmers are very used to understanding ‘\’ as an escape character. The only alternative we considered was  ‘{quote …}’. This we rejected as matching the close brace ‘}’ could be challenging for a developer since ‘}’ is a SQL token as well. 

With respect to using ‘??’ to represent a single ‘?’, MATCH RECOGNIZE makes this problematic as ‘??’ is a valid SQL token. As a general idea I have no objection but because ‘??’ is a valid SQL token I don’t think this is something Oracle can support.

Douglas

> On Oct 9, 2018, at 5:08 AM, Dave Cramer <davecramer at gmail.com> wrote:
> 
> 
> 
> On Tue, 9 Oct 2018 at 07:42, Lance Andersen <lance.andersen at oracle.com <mailto:lance.andersen at oracle.com>> wrote:
> 
>> On Oct 9, 2018, at 7:22 AM, Dave Cramer <davecramer at gmail.com <mailto:davecramer at gmail.com>> wrote:
>> 
>> PostgreSQL has a related problem in that ? is actually an operator for hstore, so in some cases we needed to allow ? to not be used ad a parameter marker. We solved it by using double question marks ?? to denote the hstore operator.
>> 
>> Obviously I'd love to see this solution get in the spec.
>> 
>> FWIW I find the {\..\} to be verbose..
> 
> Do you have an alternative suggestion?  That issue had been raised previously when it was discussed but the discussion did not come to a conclusion
> 
> Yes, as mentioned just use ?? to escape the ?
> Dave Cramer
> 
> 
> <oracle_sig_logo.gif>