0

I am using Oracle 19c and I am trying to use the following SQL string with a PreparedStatement

SELECT * 
FROM   TEST_JSON2
WHERE  JSON_EXISTS(json_data, '$?(@[*] == ?)');

But I get the following exception

java.sql.SQLException: Invalid column index

How can I escape the first question mark? I found some a similar discussion but it refers to MATCH_RECOGNIZE. Also found this but still the correct answer is not clear to me.

georgeliatsos
  • 1,168
  • 3
  • 15
  • 34
  • Did you click through from the `MATCH_RECOGNIZE` answer to the documentation they linked and try the escape sequence `{\?\}` used in the example in that documentation? – MT0 Mar 16 '22 at 09:32
  • Yes, I tried this with no luck - I guess it is specific to `MATCH_RECOGNIZE`... – georgeliatsos Mar 16 '22 at 09:33
  • What version of Oracle are you using? What JDBC driver are you using? – MT0 Mar 16 '22 at 09:34
  • Oracle 19c - ojdbc8 (19.3.0.0) – georgeliatsos Mar 16 '22 at 09:37
  • 2
    You forgot to close the string literal, which might be confusing JDBC. Does it work if you do `JSON_EXISTS(json_data, '$?(@[*] == ?')` and the `?` is inside the string literal? – MT0 Mar 16 '22 at 10:05
  • Typo error while writing the question - Sorry about that! In my code, it is correctly closed. But still throws the exception. – georgeliatsos Mar 16 '22 at 10:17

1 Answers1

1

It should be sufficient to just correct the typo where you forgot to terminate the string literal inside the JSON_EXISTS function call and then the ? will be inside a string and JDBC will not parse it as a bind parameter.

If for some reason correcting the typo is insufficient then:

From the OJDBC documentation (which is subtly different from the documentation linked from the question):

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

Following that, I think your code (correcting for the typo where you do not close the string literal and are missing a closing bracket in the JSON path expression) should be:

String sql =
      "SELECT * "
    + "FROM   TEST_JSON2 "
    + "WHERE  JSON_EXISTS(json_data, {\\'$?(@[*] == ?)'\\})";
PreparedStatement ps = conn.prepareStatatement(sql);
ResultSet rs = ps.executeQuery();

or, to not use a PreparedStatement and just use a Statement:

String sql =
      "SELECT * "
    + "FROM   TEST_JSON2 "
    + "WHERE  JSON_EXISTS(json_data, '$?(@[*] == ?)')";
Statement s = conn.createStatatement(sql);
ResultSet rs = s.executeQuery();
MT0
  • 143,790
  • 11
  • 59
  • 117
  • I tried your first suggestion but it still produces an exception. Although I do not understand why you put the escape syntax around the whole expression and not escaping only the first question mark. Your 2n proposal, it is an option but I would like first to be completely sure that I cannot use prepared statement. – georgeliatsos Mar 16 '22 at 10:20
  • @georgeliatsos If you want to surround every `?`, you can try `JSON_EXISTS(json_data, '${\\?\\}(@[*] == {\\?\\}')` but the JDBC escape sequence is not limited to processing a single character so you can do the entire string literal in a single escape sequence. – MT0 Mar 16 '22 at 10:23
  • OK, I guess the confusion originates from my misunderstanding, I was guessing that the second question mark should be as a placeholder for the parameter value while the first one is an explicit question mark used from the json_exists funtion. – georgeliatsos Mar 16 '22 at 10:35
  • @georgeliatsos That would be something like (untested): `JSON_EXISTS(json_data, '$?(@[*] == "' ||?||'")')` and then the first `?` would be inside the string literal and the second would be a bind variable. – MT0 Mar 16 '22 at 10:37
  • As far as I understand based on https://stackoverflow.com/questions/48913687/jdbc-prepared-statement-to-query-json-using-json-exists/48914253#48914253 , it is not possible to use json_exists in a PreparedStatement and passing parameters in the special string literal. – georgeliatsos Mar 16 '22 at 12:16
  • 1
    @georgeliatsos That is correct, `JSON_EXISTS` expects a single literal value at the time the SQL engine compiles the statement (so the code in my previous comment will not work); what you can do is use string concatenation (with appropriate sanitation and escaping of your data) to build the JSON path expression in your Java code and include it into the SQL statement. – MT0 Mar 16 '22 at 12:31