SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Lukas Eder]

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: ??
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.

I hope this helps,
Lukas

On Tue, Oct 9, 2018 at 4:21 AM Lance Andersen <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
> >.
>
>  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>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>
>
>
>
>