10:15 a.m.
SetMaxResults with db2 database problem
---------------------------------------
Key: HHH-3177
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3177
Project: Hibernate3
Issue Type: Bug
Components: core
Environment: hibernate3.2.0.ga, DB2 8.2.1
Reporter: Irena
Trying to do simple thing:
List<Subject> subjects = (List<Subject>) currSession.createQuery("from
Subject").setMaxResults(10).list();
this prings fallowing:
WARN JDBCExceptionReporter:71 - SQL Error: -104, SQLState: 42601
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC:
10;select top ;.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could
not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at ru.nbch.quality.logic.ReportProcessor.main(ReportProcessor.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:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
SQLERRMC: 10;select top ;.
at com.ibm.db2.jcc.b.id.e(id.java:1640)
at com.ibm.db2.jcc.b.id.a(id.java:1229)
at com.ibm.db2.jcc.c.fb.h(fb.java:149)
at com.ibm.db2.jcc.c.fb.a(fb.java:43)
at com.ibm.db2.jcc.c.s.a(s.java:30)
at com.ibm.db2.jcc.c.wb.g(wb.java:152)
at com.ibm.db2.jcc.b.id.n(id.java:1209)
at com.ibm.db2.jcc.b.jd.eb(jd.java:1779)
at com.ibm.db2.jcc.b.jd.a(jd.java:2232)
at com.ibm.db2.jcc.b.jd.V(jd.java:505)
at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
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:2144)
... 13 more
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:53 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3177) SetMaxResults with db2 database problem
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3177?page=c...
]
Greg Atkinson commented on HHH-3177:
------------------------------------
I was receiving a very similar exception when trying to setMaxResults:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.hsbc.za.hnet2.dao.common.BaseDAO.getList(BaseDAO.java:141)
... 49 more
Caused by: java.sql.SQLException: [SQL0104] Token ( was not valid. Valid tokens: , FROM
INTO.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1576)
at
com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:193)
at
com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2023)
at
com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1965)
at
com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:916)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.pmiPrepareStatement(WSJdbcConnection.java:1778)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:2017)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1969)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
... 55 more
Turns out I was using the incorrect DB2 dialect, namely DB2Dialect, when I should have
been using DB2400Dialect. Which makes total sense now because my datasource is a DB2 UDB
for iSeries (AS400).
The difference is noticeable in the query generated by hibernate. While an ordirary sql
query would have "select top(1000) * from MyTable..." the DB2400Dialect query
has "... from MyTable fetch first 1000 rows only".
Perhaps one of Hibernate's other DB2 Dialects will solve your problem.
SetMaxResults with db2 database problem
---------------------------------------
Key: HHH-3177
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3177
Project: Hibernate3
Issue Type: Bug
Components: core
Environment: hibernate3.2.0.ga, DB2 8.2.1
Reporter: Irena
Trying to do simple thing:
List<Subject> subjects = (List<Subject>) currSession.createQuery("from
Subject").setMaxResults(10).list();
this prings fallowing:
WARN JDBCExceptionReporter:71 - SQL Error: -104, SQLState: 42601
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC:
10;select top ;.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could
not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at ru.nbch.quality.logic.ReportProcessor.main(ReportProcessor.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:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
SQLERRMC: 10;select top ;.
at com.ibm.db2.jcc.b.id.e(id.java:1640)
at com.ibm.db2.jcc.b.id.a(id.java:1229)
at com.ibm.db2.jcc.c.fb.h(fb.java:149)
at com.ibm.db2.jcc.c.fb.a(fb.java:43)
at com.ibm.db2.jcc.c.s.a(s.java:30)
at com.ibm.db2.jcc.c.wb.g(wb.java:152)
at com.ibm.db2.jcc.b.id.n(id.java:1209)
at com.ibm.db2.jcc.b.jd.eb(jd.java:1779)
at com.ibm.db2.jcc.b.jd.a(jd.java:2232)
at com.ibm.db2.jcc.b.jd.V(jd.java:505)
at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
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:2144)
... 13 more
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:26 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3177) SetMaxResults with db2 database problem
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3177?page=c...
]
Gail Badner commented on HHH-3177:
----------------------------------
Irena, did any of the DB2 dialects work for you?
SetMaxResults with db2 database problem
---------------------------------------
Key: HHH-3177
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3177
Project: Hibernate Core
Issue Type: Bug
Components: core
Environment: hibernate3.2.0.ga, DB2 8.2.1
Reporter: Irena
Trying to do simple thing:
List<Subject> subjects = (List<Subject>) currSession.createQuery("from
Subject").setMaxResults(10).list();
this prings fallowing:
WARN JDBCExceptionReporter:71 - SQL Error: -104, SQLState: 42601
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC:
10;select top ;.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
WARN JDBCExceptionReporter:71 - SQL Error: -727, SQLState: 56098
ERROR JDBCExceptionReporter:72 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC:
2;-104;42601;10|select top |.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could
not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at ru.nbch.quality.logic.ReportProcessor.main(ReportProcessor.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:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601,
SQLERRMC: 10;select top ;.
at com.ibm.db2.jcc.b.id.e(id.java:1640)
at com.ibm.db2.jcc.b.id.a(id.java:1229)
at com.ibm.db2.jcc.c.fb.h(fb.java:149)
at com.ibm.db2.jcc.c.fb.a(fb.java:43)
at com.ibm.db2.jcc.c.s.a(s.java:30)
at com.ibm.db2.jcc.c.wb.g(wb.java:152)
at com.ibm.db2.jcc.b.id.n(id.java:1209)
at com.ibm.db2.jcc.b.jd.eb(jd.java:1779)
at com.ibm.db2.jcc.b.jd.a(jd.java:2232)
at com.ibm.db2.jcc.b.jd.V(jd.java:505)
at com.ibm.db2.jcc.b.jd.executeQuery(jd.java:488)
at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
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:2144)
... 13 more
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6421
days inactive
6610
days old
hibernate-issues@lists.jboss.org
2 comments
3 participants
participants (3)
-
Gail Badner (JIRA) -
Greg Atkinson (JIRA)
-
Irena (JIRA)