The following piece of code fails with a SQLServerException: Invalid column name 'x'.
Code:
Query nativeQuery = entitiyManager.createNativeQuery("SELECT 1 AS X FROM EDIS_HOLD ORDER BY X");
nativeQuery.setFirstResult(0);
nativeQuery.setMaxResults(1);
nativeQuery.getResultList();
The original statement is of course different, I have modified it to the simplest failing form.
If I remove the setMaxResults() it works fine.
The SQL statement issued by Hibernate is as follow:
Code:
WITH query AS (select ROW_NUMBER() OVER (order by x) as __hibernate_row_nr__, 1 as x from edis_hold ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ?
Our code works with JBoss AS 6.0 (Hibernate core 3.0.6) but is broken in JBoss 6.1 (Hibernate 3.6.6).
The database is SQL Server 2008 R2.
I have tried with and without specifying the dialect (SQLServer2008Dialect).
Here is the stack trace:
Code:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1215)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1148)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
... my code is here ...
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
... 134 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'x'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:91)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 141 more
It is hard to believe that I am the first one to encounter the problem but I haven't found anything similar on Google or in this forum.
Does anyone have a clue of what I am doing wrong or is it a bug without workaround?
Thanks,
-Cedric
Login
FAQ
Search