[eclipselink-users] JPQL produces SQL syntax error on Informix, but only

[Laird Nelson <ljnelson@xxxxxxxxx>]

Informix again (surprise).

We have a JPQL query like this (again, merely the messenger here on the query definition):

@NamedQuery(name = "RulesDocument.findVersionId",
            query = "SELECT CASE " +
                    "       WHEN COUNT(d) = 0 THEN 1 " +
                    "       ELSE (MAX(d.version) + 1) " +
                    "       END " +
                    "  FROM RulesDocument d " +
                    " WHERE d.status <> 'TEST' " +
                    "   AND d.fileName = :fileName")

Note that there is one "slot" named :fileName.

EclipseLink turns this into the following SQL for a prepared statement:

SELECT CASE  WHEN (COUNT(id) = ?) THEN ? ELSE (MAX(version) + ?) END FROM ngp.rules_document WHERE ((rule_status <> ?) AND (file_name = ?))

Note that there are five slots here.  EclipseLink's JPQL parser appears to be "smart" enough to infer parameters and so to be able (presumably?) to use the (safer) PreparedStatement constructs to bind parameters instead of just baking in literal values to the query itself.  Fine.

However, this statement produces a syntax error on Informix:

Caused by: java.sql.SQLException: A syntax error has occurred.

        at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413)

        at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3635)

        at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3963)

        at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2693)

        at com.informix.jdbcx.IfxXASqli.receiveMessage(IfxXASqli.java:116)

        at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1409)

        at com.informix.jdbc.IfxPreparedStatement.f(IfxPreparedStatement.java:326)

        at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:306)

        at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:176)

        at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:6787)

        at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2619)

        at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:562)

        at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:255)

        at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:52)

        at com.sun.gjc.spi.ManagedConnection.prepareCachedStatement(ManagedConnection.java:993)

        at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:173)

        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1474)

However, when you manually fill in the slots, so that this reads:

SELECT CASE  WHEN (COUNT(id) = 0) THEN 1 ELSE (MAX(version) + 1) END FROM ngp.rules_document WHERE ((rule_status <> 'TEST') AND (file_name = 'foobar'))

...and run it manually, this SQL query runs fine on Informix.

Am I overlooking something?  How does EclipseLink determine when to turn a literal (0 and 1 and 'TEST' in this example) into a parameter with a value?  Is there something in this prepared statement assembly that is somehow causing a SQL syntax error despite the fact that the equivalent SQL with the values filled in is syntactically correct?

Thanks,

Best,

Laird

--
http://about.me/lairdnelson