Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the correct way to escape the ? character in a JDBC PreparedStatement when using Oracle 12c MATCH_RECOGNIZE?

The following query is correct in Oracle 12c:

SELECT *
FROM dual
MATCH_RECOGNIZE (
  MEASURES a.dummy AS dummy
  PATTERN (a?)
  DEFINE a AS (1 = 1)
)

But it doesn't work through JDBC because of the ? character that is used as a regular expression character, not as a bind variable.

What's the correct way to escape the ? through JDBC, assuming I want to run this as a PreparedStatement with bind variables?

Note:

  • I've found a discussion on the JDBC spec discuss mailing list, but there's no conclusion to this problem: http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-October/000066.html
  • PostgreSQL has similar problems with JSON operators: How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC
like image 306
Lukas Eder Avatar asked Feb 08 '17 08:02

Lukas Eder


People also ask

Which of the following statements are correct about PreparedStatement in JDBC?

Q 5 - Which of the following is correct about PreparedStatement? A - PreparedStatement allows mapping different requests with same prepared statement but different arguments to execute the same execution plan.

Which methods on the PreparedStatement can be used to bind the parameters?

You must supply values for every parameter before executing the SQL statement. The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter.

What is the use of PreparedStatement in JDBC?

The PreparedStatement interface extends the Statement interface it represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.

Why do we use PreparedStatement instead of statement?

1. PreparedStatement in Java allows you to write a parameterized query that gives better performance than the Statement class in Java. 2. In the case of PreparedStatement, the Database uses an already compiled and defined access plan, this allows the prepared statement query to run faster than a normal query.


1 Answers

This is covered explicitly in the documentation:

MATCH_RECOGNIZE Clause

The ? character is used as a token in MATCH_RECOGNIZE clause in Oracle Database 11g and later versions. As the JDBC standard defines the ? character as a parameter marker, the JDBC Driver and the Server SQL Engine cannot distinguish between different uses of the same token.

In earlier versions of JDBC Driver, if you want to interpret the ? character as a MATCH_RECOGNIZE token and not as a parameter marker, then you must use a Statement instead of a PreparedStatement and disable escape processing. However, starting from Oracle Database 12c Release 1 (12.1.0.2), you can use the '{\ ... \}' syntax while using the ? character, so that the JDBC driver does not process it as a parameter marker and allows the SQL engine to process it.

like image 179
Jim Garrison Avatar answered Oct 03 '22 04:10

Jim Garrison