Search code examples
javajdbcprepared-statementderby

Derby PreparedStatement does not inject parameter inside XMLEXISTS function

An error occurs when trying to inject the string parameter of xmlexists function using a prepared statement.

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection con = DriverManager.getConnection("jdbc:derby:c:\\mydb");
PreparedStatement st = con.prepareStatement("select * from \"Data\" where xmlexists(? passing by ref \"data\")");
st.setString(1, "//*[text()[contains(., 'v1')]]");
ResultSet rs = st.executeQuery();

Error:

ERROR 42Z75: XML query expression must be a string literal.

The statement succeeds when it's built manually without a prepared statement.

select *
from "Data"
where xmlexists('//*[text()[contains(., ''v1'')]]' passing by ref "data")

What could be the reason?

Solution

  • Quote from: https://db.apache.org/derby/docs/10.13/ref/rreffuncxmlexists.html

    Syntax: XMLEXISTS ( xqueryStringLiteral PASSING BY REF xmlValueExpression [ BY REF ] )

    xqueryStringLiteral
    Must be specified as a string literal. If this argument is specified as a parameter, an expression that is not a literal, or a literal that is not a string (for example an integer), Derby throws an error. The xqueryStringLiteral argument must also be an XPath expression. Derby does not support full XQuery, only the XPath subset. If it cannot compile or execute the query argument, Derby throws an SQLException.

    Sounds like derby does not allow dynamic parameters inside xmlexists function https://db.apache.org/derby/docs/10.13/ref/rrefsqlj1083019.html