Hi,

I am having problems with setMaxResults(...) and Informix... and the InformixDialect.

I believe the problem is because of the SQL syntax Informix uses to restrict the number of rows returned.

To get first returned row only:


the SQL generated by setMaxResults(...) on a Query instance from Session.createQuery(...) is correct:


but I believe that the ? is then used as a positional parameter by find(...) and since it is not in the SQL WHERE clause it is not getting replaced... hence a syntax error exception is thrown.

The docs say ? parameters are supported in the WHERE clause, hence my reasoning.

Can anyone confirm this... or offer another explanation?
Thanks...

Hibernate version:
2.1.6

Name and version of the database you are using:
Informix 9.4
Informix JDBC driver 2.21.JC5
InformixDialect (with applied patches as per hibernate.org/221.html)

Code between sessionFactory.openSession() and session.close():

The generated SQL (show_sql=true):

Full stack trace of any exception that occurs:
Could not execute query
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1547)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at HibernateComplexQuerySQLTest.test(HibernateComplexQuerySQLTest.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at junit.textui.TestRunner.run(TestRunner.java:72)
at HibernateComplexQuerySuite.main(HibernateComplexQuerySuite.java:131)
Caused by: java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3086)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3396)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2259)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2179)
at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1082)
at com.informix.jdbc.IfxResultSet.executePrepare(IfxResultSet.java:182)
at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:197)
at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:175)
at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1907)
at AdaltConnection.prepareStatement(AdaltConnection.java:110)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
... 18 more

Debug level Hibernate log excerpt:
... can supply this but it is extremely long.

you are not alone.
we switched back to an older version of hibernate, i think 2.1.4 but not sure

shomburg,

Can you elaborate as to why you did this... did someone on the Hibernate team advise this... because of... ?

Did you perform some code inspection/debugging and isolate the problem and hence make the change... ?

I am assuming you were using Hibernate 2.1.6 aswell and Informix9 (JDBC driver 2.21.JC5)... can you confirm this?

Any further information would be helpful (specific details would be great!),

Thanks,
Miles

For those interested...

In order to make the "FIRST" construct work (known as LIMIT in the Hibernate docs and other databases) the Informix dialect should be adjusted.



I will produce a proper patch once fully tested with the Hibernate JUnit tests (maybe now they will get to 100%), but in the meantime here is the is what needs to be done.

Add the following method:



This prevents Hibernate from handling the LIMIT syntax as a PreparedStatement IN parameter... something not supported by JDBC since the clause is not part of the SQL WHERE clause.

Then adjust the following method:




This basically inserts the LIMIT syntax as a static string into the SQL string rather than inserting "first ?" as it did previously and then setting it as an IN parameter later. Also any SQL DISTINCT or UNIQUE keyword appears after the FIRST clause and so does neet to be accounted for. (the getAfterSelectInsertPoint(String sql) method can be ignored or deleted)

Hope this saves you Informix uses some time!

Aloha