We're updating the issue view to help you get more done. 

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

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 // 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 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.

Environment

MS SQL Server 2005 (Windows)

Status

Assignee

Unassigned

Reporter

Peter Taucher

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

5.0.0.CR4
5.0.0.Beta2

Priority

Major