| SQLServer dialect issue with native sql when setLimit() is present. After investigating it is found that Hibernate is expecting a space after select keyword. That means if query has a newline (enter) after the select keyword. we see the below exception. query that creates the below exception: select
- from employee e where e.first_name = :firstName
exception: May 11, 2016 11:08:45 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init> INFO: HHH000397: Using ASTQueryTranslatorFactory Hibernate: selec TOP(?)t
- from employee e where e.first_name = ?
May 11, 2016 11:08:46 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 156, SQLState: S0001 May 11, 2016 11:08:46 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Incorrect syntax near the keyword 'TOP'. could not extract ResultSet org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) at org.hibernate.loader.Loader.doQuery(Loader.java:910) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) at org.hibernate.loader.Loader.doList(Loader.java:2554) at org.hibernate.loader.Loader.doList(Loader.java:2540) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) at org.hibernate.loader.Loader.list(Loader.java:2365) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) at hibernate.test.QueryTest.main(QueryTest.java:101) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'TOP'. 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 org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ... 14 more
I have also check for the below query and it fails with the same above exception. select 1 Attaching the test case to reproduce the issue. |