[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-2276) Can not get N first results of query with DB2 dialect (neither with setMaxResults nor with setFetchSize)

Can not get N first results of query with DB2 dialect (neither with setMaxResults nor with setFetchSize) -------------------------------------------------------------------------------------------------------- Key: HHH-2276 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2276 Project: Hibernate Core Issue Type: Bug Components: query-criteria Affects Versions: 3.1.2 Environment: DB2 V7 version = DSN07012 DB OS = zOS IBM DB2 JDBC Universal Driver Architecture Version du pilote JDBC : 2.1.34 Using dialect: org.hibernate.dialect.DB2Dialect Reporter: Fred I can't get the N first records from a select request using the setMaxResults method. HB generates a request that can not be understood by DB2 (select * from ( select rownumber() over() as rownumber etc.... see first stacktrace below) Moreover, using setFetchSize does not work (see second stack trace below the first one). Is there a way to tell HB to append " fetch first 10 rows only" to the query (as a workaround) ? Because my DB2 can understand that one : select * from MYTABLE where numseq = '2' fetch first 10 rows only thanks ! Fred STACK WITH SETMAXRESULTS : Hibernate: select * from ( select rownumber() over() as rownumber_, * from A165D.TB3PARCV where TVOY='D' ) as temp_ where rownumber_ <= ? [01/12/06 12:03:17:516 CET] 2d68c035 SystemOut O 12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -104, SQLState: 42601 12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -104, SQLState: 42601 12:03:17,516 67422 ERROR JDBCExceptionReporter (logExceptions, 72 ) - ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO 12:03:17,516 67422 ERROR JDBCExceptionReporter (logExceptions, 72 ) - ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO 12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -516, SQLState: 26501 12:03:17,516 67422 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -516, SQLState: 26501 12:03:17,547 67453 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT 12:03:17,547 67453 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT 12:03:17,547 67453 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -514, SQLState: 26501 12:03:17,547 67453 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: -514, SQLState: 26501 12:03:17,578 67484 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE CURSOR SQL_CURLN300C4 IS NOT IN A PREPARED STATE 12:03:17,578 67484 ERROR JDBCExceptionReporter (logExceptions, 72 ) - THE CURSOR SQL_CURLN300C4 IS NOT IN A PREPARED STATE 12:03:17,578 67484 ERROR WAction (execute, 56 ) - org.hibernate.exception.SQLGrammarException: could not execute query org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2153) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169) [...] Caused by: com.ibm.db2.jcc.a.SqlException: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO at com.ibm.db2.jcc.a.cy.e(cy.java:1507) at com.ibm.db2.jcc.a.cy.a(cy.java:1117) at com.ibm.db2.jcc.a.cy.a(cy.java:1103) at com.ibm.db2.jcc.b.bd.h(bd.java:131) at com.ibm.db2.jcc.b.bd.a(bd.java:42) at com.ibm.db2.jcc.b.r.a(r.java:31) at com.ibm.db2.jcc.b.bs.g(bs.java:149) at com.ibm.db2.jcc.a.cy.l(cy.java:1097) at com.ibm.db2.jcc.a.cz.bb(cz.java:1554) at com.ibm.db2.jcc.a.cz.d(cz.java:1986) at com.ibm.db2.jcc.a.cz.S(cz.java:424) at com.ibm.db2.jcc.a.cz.executeQuery(cz.java:407) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:426) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2150) ... 52 more _____________________________________________________________________________________________________ STACK WITH SETFETCHSIZE() : [01/12/06 12:20:57:250 CET] 28ea8033 SystemOut O 11:20:57,250 403719 ERROR WAction (execute, 56 ) - org.hibernate.MappingException: No Dialect mapping for JDBC type: 3 org.hibernate.MappingException: No Dialect mapping for JDBC type: 3 at org.hibernate.dialect.TypeNames.get(TypeNames.java:56) at org.hibernate.dialect.TypeNames.get(TypeNames.java:81) at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:192) at org.hibernate.loader.custom.CustomLoader.getHibernateType(CustomLoader.java:170) at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:138) at org.hibernate.loader.Loader.getResultSet(Loader.java:1678) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2150) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:121) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)