Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] JPQL produces SQL syntax error on Informix, but only because of PreparedStatement binding? 
Hi Laird,
I'm not sure what could be wrong with Informix's handling of that parameter binding. 'some things you could experiment with to narrow it down/work around.
- Persistence unit property: eclipselink.jdbc.bind-parameters=false to turn off parameter binding - Persistence unit property: eclipselink.logging.parameters=true combined with a logging level of FINE or better will log the parameters being bound so you can see if there are any issues with what is being bound - Query Hint: eclipselink.jdbc.bind-parameters=false to turn off parameter binding on a query-by-query basis 
- DatabasePlatform.setUsesStringBinding(boolean) set whether string values are bound
- DatabasePlatform.setShouldBindLiterals(boolean) set whether literals are bound

-Tom

On 23/02/2013 1:45 AM, Laird Nelson wrote:

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) PreparedStatementconstructs 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



_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top