SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Lance Andersen]

> On Oct 9, 2018, at 7:22 AM, Dave Cramer <davecramer at gmail.com> wrote:
> 
> PostgreSQL has a related problem in that ? is actually an operator for hstore, so in some cases we needed to allow ? to not be used ad a parameter marker. We solved it by using double question marks ?? to denote the hstore operator.
> 
> Obviously I'd love to see this solution get in the spec.
> 
> FWIW I find the {\..\} to be verbose..

Do you have an alternative suggestion?  That issue had been raised previously when it was discussed but the discussion did not come to a conclusion 
> as Douglas mentioned the \ will have to be doubled so it is really going to look like
> 
> PATTERN(A{\\?\\} X{\\*?\\} Y{\\+?\\} Z{\\??\\}) " +  // use of escape sequence

Yes, that was a cut and paste out of the Oracle docs which had a typo which I did missed updating before hitting send ;-(
> 
> 
> Dave Cramer
> 
> 
> On Mon, 8 Oct 2018 at 18:41, Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>> wrote:
> As the following example is written as Java String literals, the backslashes must be doubled.
> 
> > On Oct 8, 2018, at 1:20 PM, Lance Andersen <lance.andersen at oracle.com <mailto:lance.andersen at oracle.com>> wrote:
> > 
> > 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
>    "    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";
> 

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