SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters

[Dave Cramer]

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


Dave Cramer


On Mon, 8 Oct 2018 at 18:41, Douglas Surber <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>
> 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";
>
>