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

[Mark Rotteveel]

On 2018-10-10 09:24, Lukas Eder wrote:
> I've just thought of another caveat related to the Oracle database,
> which knows quoted string literals:
> https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Literals.html#GUID-1824CBAA-6E16-4921-B2A6-112FB02248DA
> 
> This is a valid string literal in Oracle:
> q'{string content here}'
> 
> This is also a valid string literal in Oracle:
> q'{\string content with some backslashes here\}'
> 
> While there shouldn't be any syntactic ambiguity, given the leading q'
> token, it's still a caveat worth keeping in mind.

I already considered that (Firebird has Q-literals as well), see example 
4 below.

However, what about quoted identifiers? I have seen databases with 
columns like "a\b", eg "signal\noise", etc.

Would that need to be `{\"a\b"\}` or `{\"a\\b"\}`? I think the first is 
preferable.

Mark

> On Wed, Oct 10, 2018 at 6:52 AM Douglas Surber
> <douglas.surber at oracle.com> wrote:
> 
>> All correct.
>> 
>>> On Oct 9, 2018, at 1:18 PM, Mark Rotteveel <mark at lawinegevaar.nl>
>> wrote:
>>> 
>>> On 9-10-2018 21:50, Douglas Surber wrote:
>>>> Mark,
>>>> The Oracle Database Driver uses the first interpretation, any
>> occurrence of ‘/‘ must be doubled except for the single ‘/‘
>> that precedes the ‘}’ that ends the escape sequence. Your formal
>> definition is correct modulo character sequence literals and
>> comments.
>>> 
>>> Ok, rephrasing to check if we're on the same page:
>>> 
>>> Within {\..\}, occurrences of `\` must be doubled to `\\` except
>> if contained in string literals or comments.
>>> 
>>> Some examples:
>>> 
>>> 1. `{\'literal with {\..\}'\}` => `'literal with {\..\}'`
>>> 2. `{\something -- comment with {\..\} + linebreak
>>> something \\ else\}`
>>> =>
>>> `something -- comment with {\..\} + linebreak
>>> something \ else`
>>> 3. `{\/*comment {\..\}*/\} => /*comment {\..\}*/
>>> 4. `{\q'{\abc\}'\}` => q'{\abc\}'`
>>> 5. `{\{\\inception\\}\}` => `{\inception\}`
>>> 
>>> While `{\x \ y\}` would be a syntax error and should be `{\x \\
>> y\}`.
>>> --
>>> Mark Rotteveel