SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Lance Andersen]

> On Oct 9, 2018, at 3:29 AM, Lukas Eder <lukas.eder at gmail.com> 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?

Agree it is un-likely but should be considered.  Assuming there is not a better solution for disabling parameter markers/escape processing  found for a segment of a PreparedStatement,  the sequence could be doubled  up which is pretty ugly.

Definitely needs more thought

> Another way to escape it could be to double it: Ordinary marker: ?, Escaped marker: ??

The previous discussion in 2013 ruled out ?? 


> 2. Another database that would definitely benefit from this feature is PostgreSQL, which has quite a few ASCII operators like ?, ??, ?|, ?&, and many more. See: https://stackoverflow.com/q/38370972/521799 <https://stackoverflow.com/q/38370972/521799>.

Yep DaveC just chimed in :-)

Thank you Lukas
> 
> I hope this helps,
> Lukas
> 
> On Tue, Oct 9, 2018 at 4:21 AM Lance Andersen <lance.andersen at oracle.com <mailto:lance.andersen at oracle.com>> wrote:
> All,
> 
>  The SQL 2016  standard introduced the MATCH RECOGNIZE operator, http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip <http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip> <http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip <http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip>>.
> 
>  Because of that,  the Oracle JDBC team has asked us to revisit a discussion  that we talked about briefly in 2013 but did not reach closure whether JDBC should provide a means to  Escape the JDBC parameter marker.
> 
> 
> Consider the following example:
> ————————
> String sql =
>     "select T.firstW, T.lastZ, ? " +  // use of parameter marker
>     "from tkpattern_S11 " +
>     "MATCH_RECOGNIZE ( " +
>     "    MEASURES A.c1 as firstW, last(Z.c1) as lastZ " +
>     "    ALL MATCHES " +
>     "    PATTERN(A{\?\} X{\*?\} Y{\+?\} Z{\??\}) " +  // use of escape sequence
>     "    DEFINE " +
>     "        X as X.c2 > prev(X.c2), " +
>     "        Y as Y.c2 < prev(Y.c2), " +
>     "        Z as Z.c2 > prev(Z.c2)" +
>     ") as T";
>   PreparedStatement ps = conn.prepareStatatement(sql);
>   ps.setString(1, "test");
>   ResultSet rs = ps.executeQuery();
> ————————————
> 
> The above query allows for the use of a PreparedStatement with parameter markers and if you look at the PATTERN clause,  what would be a parameter marker is disabled by enclosing the parameter marker   {\…\}
> 
> As Escape processing is enabled for a PreparedStatement, the Oracle proposal would essentially treat anything within the {\…\} as is, i.e. not escaped and not attempt to transform the characters.
> 
> 
> Thoughts on the issue/syntax, alternative suggestions?
> 
> 
> 
>  <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>> <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> <mailto:Lance.Andersen at oracle.com <mailto:Lance.Andersen at oracle.com>>
> 
> 
> 

 <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>