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

Fred (JIRA) noreply at atlassian.com
Fri Dec 1 08:31:04 EST 2006 

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2276?page=comments#action_25528 ] 

Fred commented on HHH-2276:
---------------------------

the java code is :
        Criteria criteria = session.createCriteria(boClass);
        [...]
        bos = criteria.setMaxResults(300).list();


sames errors with SQLQuery  :

          String sql = "SELECT * from A165D.TB3PARCV where TVOY='D'";
          SQLQuery query = session.getSession().createSQLQuery(sql);
          query.setMaxResults(10);
          // or query.setFetchSize(10);
          List results = query.list();


> 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: Hibernate3
>         Type: Bug

>   Components: query-criteria
>     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)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

More information about the hibernate-issues mailing list