SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Lance Andersen]

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>