Uploaded image for project: 'Hibernate ORM'
  1. HHH-10032

TopLimitHandler - SQLServerException: Incorrect syntax near '@P0'.

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Rejected
    • Affects versions: 5.0.0.Beta2, 5.0.0.CR4
    • Fix versions: None
    • Components: None
    • Labels:
    • Environment:
      MS SQL Server 2005 (Windows)
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      Description

      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:

      // 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;
      }
      

      According to the sql logging this translates into something like:

          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
      

      And following exception is caught:

      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
      

      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.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: