org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Hibernate JIRA | Peter Taudcher | 1 year ago
  1. 0

    We have a simple query that was working with Hibernate 4.1.9 and is now not working after updating to Hibernate 5 (CR4). It's basically a simple HQL query on a single table: {code:java} // Deliver last n events public List<ISportsTickerEvent> getLastSportsTickerEvent( int max ) { List<ISportsTickerEvent> result = new LinkedList<ISportsTickerEvent>(); StringBuffer sql = new StringBuffer(); sql.append( "from SportsTickerEvent where id.sportsTicker=:tickerId" ); sql.append( " and clearedBy is null" ); sql.append( " order by id.sportsTickerEventNumber desc" ); Query query = hda.getSession().createQuery( sql.toString() ); query.setLong( "tickerId", data.getIdsportsTicker() ); query.setMaxResults( max ); Collection<SportsTickerEvent> events = query.list(); for ( SportsTickerEvent entry : events ) { result.add( new SportsTickerEventImpl( entry, hda ) ); } return result; } {code} According to the sql logging this translates into something like: {code:sql} select TOP ? sportstick0_.SportsTickerID as SportsT15_264_, sportstick0_.SportsTickerEventNumber as SportsTi1_264_, sportstick0_.clearedBy as clearedB2_264_, sportstick0_.clockRunning as clockRun3_264_, sportstick0_.confirmed as confirme4_264_, sportstick0_.currentPlayTime as currentP5_264_, sportstick0_.dateTime as dateTime6_264_, sportstick0_.diffTime as diffTime7_264_, sportstick0_.EventCodeID as EventCo16_264_, sportstick0_.EventStateID as EventSt17_264_, sportstick0_.minute as minute8_264_, sportstick0_.ParentEventNumber as ParentEv9_264_, sportstick0_.PlayerID as PlayerI10_264_, sportstick0_.UserID as UserID18_264_, sportstick0_.scoutEventNumber as scoutEv11_264_, sportstick0_.Sequence as Sequenc12_264_, sportstick0_.SportsTickerStateID as SportsT19_264_, sportstick0_.tickerDangerState as tickerD13_264_, sportstick0_.triggeredBy as trigger14_264_ from SportsTickerEvent sportstick0_ where sportstick0_.SportsTickerID=? and ( sportstick0_.clearedBy is null ) order by sportstick0_.SportsTickerEventNumber desc {code} And following exception is caught: {code:java} org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2116) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2611) at org.hibernate.loader.Loader.doList(Loader.java:2594) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423) at org.hibernate.loader.Loader.list(Loader.java:2418) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at info.runningball.server.common.dataAbstraction.modules.impl.hibernate.sportsticker.SportsTickerImpl.getLastSportsTickerEvent(SportsTickerImpl.java:1444) [...] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 43 more {code} The only way I could get rid of this problem was to extend the SQLServerDialect and instead of returning *TopLimitHandler* in *getLimitHandler* to deliver *SQLServer2005LimitHandler*. When using TopLimitHandler this exception occurs, when using SQLServer2005LimitHandler the query works fine. So I guess this may be a problem in the LimitHandler implementation.

    Hibernate JIRA | 1 year ago | Peter Taudcher
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  2. 0

    We have a simple query that was working with Hibernate 4.1.9 and is now not working after updating to Hibernate 5 (CR4). It's basically a simple HQL query on a single table: {code:java} // Deliver last n events public List<ISportsTickerEvent> getLastSportsTickerEvent( int max ) { List<ISportsTickerEvent> result = new LinkedList<ISportsTickerEvent>(); StringBuffer sql = new StringBuffer(); sql.append( "from SportsTickerEvent where id.sportsTicker=:tickerId" ); sql.append( " and clearedBy is null" ); sql.append( " order by id.sportsTickerEventNumber desc" ); Query query = hda.getSession().createQuery( sql.toString() ); query.setLong( "tickerId", data.getIdsportsTicker() ); query.setMaxResults( max ); Collection<SportsTickerEvent> events = query.list(); for ( SportsTickerEvent entry : events ) { result.add( new SportsTickerEventImpl( entry, hda ) ); } return result; } {code} According to the sql logging this translates into something like: {code:sql} select TOP ? sportstick0_.SportsTickerID as SportsT15_264_, sportstick0_.SportsTickerEventNumber as SportsTi1_264_, sportstick0_.clearedBy as clearedB2_264_, sportstick0_.clockRunning as clockRun3_264_, sportstick0_.confirmed as confirme4_264_, sportstick0_.currentPlayTime as currentP5_264_, sportstick0_.dateTime as dateTime6_264_, sportstick0_.diffTime as diffTime7_264_, sportstick0_.EventCodeID as EventCo16_264_, sportstick0_.EventStateID as EventSt17_264_, sportstick0_.minute as minute8_264_, sportstick0_.ParentEventNumber as ParentEv9_264_, sportstick0_.PlayerID as PlayerI10_264_, sportstick0_.UserID as UserID18_264_, sportstick0_.scoutEventNumber as scoutEv11_264_, sportstick0_.Sequence as Sequenc12_264_, sportstick0_.SportsTickerStateID as SportsT19_264_, sportstick0_.tickerDangerState as tickerD13_264_, sportstick0_.triggeredBy as trigger14_264_ from SportsTickerEvent sportstick0_ where sportstick0_.SportsTickerID=? and ( sportstick0_.clearedBy is null ) order by sportstick0_.SportsTickerEventNumber desc {code} And following exception is caught: {code:java} org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2116) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2611) at org.hibernate.loader.Loader.doList(Loader.java:2594) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423) at org.hibernate.loader.Loader.list(Loader.java:2418) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at info.runningball.server.common.dataAbstraction.modules.impl.hibernate.sportsticker.SportsTickerImpl.getLastSportsTickerEvent(SportsTickerImpl.java:1444) [...] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 43 more {code} The only way I could get rid of this problem was to extend the SQLServerDialect and instead of returning *TopLimitHandler* in *getLimitHandler* to deliver *SQLServer2005LimitHandler*. When using TopLimitHandler this exception occurs, when using SQLServer2005LimitHandler the query works fine. So I guess this may be a problem in the LimitHandler implementation.

    Hibernate JIRA | 1 year ago | Peter Taudcher
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  3. 0

    "ORA-00932: inconsistent datatypes: expected - got BLOB" in EAP - Red Hat Customer Portal

    redhat.com | 8 months ago
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    NetBeans + Hibernate + PostGres

    Stack Overflow | 6 months ago | user1827988
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  6. 0

    How to use @Subselect in Hibernate

    Stack Overflow | 2 years ago | Chaitanya
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet

  1. regisso 2 times, last 6 months ago
  2. tjago 3 times, last 8 months ago
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. org.hibernate.exception.SQLGrammarException

    could not extract ResultSet

    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert()
  2. Hibernate
    QueryImpl.list
    1. org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    2. org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    3. org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    4. org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    5. org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
    6. org.hibernate.loader.Loader.getResultSet(Loader.java:2116)
    7. org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899)
    8. org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875)
    9. org.hibernate.loader.Loader.doQuery(Loader.java:919)
    10. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
    11. org.hibernate.loader.Loader.doList(Loader.java:2611)
    12. org.hibernate.loader.Loader.doList(Loader.java:2594)
    13. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423)
    14. org.hibernate.loader.Loader.list(Loader.java:2418)
    15. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
    16. org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    17. org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
    18. org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
    19. org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    19 frames
  3. info.runningball.server
    SportsTickerImpl.getLastSportsTickerEvent
    1. info.runningball.server.common.dataAbstraction.modules.impl.hibernate.sportsticker.SportsTickerImpl.getLastSportsTickerEvent(SportsTickerImpl.java:1444)
    1 frame