SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Lance Andersen]

> On Oct 9, 2018, at 11:38 AM, Mark Rotteveel <mark at lawinegevaar.nl> wrote:
> 
> On 9-10-2018 09:29, Lukas Eder wrote:
>> I have two comments on this:
>> 1. Expecting the "unexpected", how would you go about escaping the escape
>> sequence? If {\...\} is ever something we would like to send to the server
>> "AS IS", without being processed, how could that be done? Another way to
>> escape it could be to double it: Ordinary marker: ?, Escaped marker: ??
> 
> Given the use of {...} is a common escape (it was originally defined in ODBC), I don't expect it likely that a SQL dialect will have a syntax where {} is in the literal, but even if that was the case, it can always be solved by another layer of escapes.
> 
> Say there is a SQL dialect that requires {\something\} to be sent literally to the server, then you could fix that by using
> {\{\something\\}{\}\} (that is surround {\something\ in the first escape and } in the second.
> 
> Ugly as hell, but it will work. Alternatively, you could disable escape processing (although, not all drivers have an option to disable it for prepared statements).

Escape processing cannot be disabled on a PreparedStatement via setEscapeProcessing() per the JDBC spec

From the Statement::setEscapeProcesing javadoc

———————————————
Note: Since prepared statements have usually been parsed prior to making this call, disabling escape processing for PreparedStatements objects will have no effect.
—————————————————————

Now that has been a soft assertion but the intent is that it cannot be disabled

> 
> -- 
> Mark Rotteveel

 <http://oracle.com/us/design/oracle-email-sig-198324.gif>
 <http://oracle.com/us/design/oracle-email-sig-198324.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
 <http://oracle.com/us/design/oracle-email-sig-198324.gif>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 <mailto:Lance.Andersen at oracle.com>