SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Mark Rotteveel]

For reference, an earlier discussion back in 2013: 
http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/thread.html#48

I don't really like {\ .. \}, for two reasons 1) the doubling required 
for escaping the '\' in normal Java strings, and 2) it deviates from the 
other JDBC escapes (or the ODBC escapes they were derived from). I think 
the first objection is the most important though.

I'd sooner suggest {q ?} (where the q is short for quote), or maybe as I 
suggested 5 years ago: {qm} (short for question mark). That would be 
more in line with the normal escape syntax. Which - with exception of 
the call escape - takes the form of {<name> <escape-specific-stuff>}. 
I'd suggest {q ?} over {qm}, as it might be more readable and maybe more 
versatile.

If an open/close sequence is preferred (it might make parsing easier), 
then maybe {`?`} could be used.

This doesn't require escaping in Java strings, and bare use of a 
back-tick (`) is - with exception of MySQL and MariaDB - not common in 
SQL syntax. And even for MySQL and MariaDB, this usage should not 
provide a problem.

This would even work fine with JEP-326 (raw string literals). It would 
also allow use of the '}' or '`' (but not '`}') within the escape. The 
only problem I foresee is in markdown or asciidoc inline code syntax ;)

Irrespective of the syntax chosen, I think it should be possible (and 
advisable) to widen the scope. That is, instead of

PATTERN(A{\?\} X{\*?\} Y{\+?\} Z{\??\})

You would use:

PATTERN({`A? X*? Y+? Z??`})

or maybe even

{`PATTERN(A? X*? Y+? Z??)`}

Mark

On 8-10-2018 22:20, Lance Andersen 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>
> 
> 
> 
> 


-- 
Mark Rotteveel