Hibernate Community • View topic - Criteria#setMaxResults() Returns Schema as Lowercase

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Criteria#setMaxResults() Returns Schema as Lowercase

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

d-price

Post subject: Criteria#setMaxResults() Returns Schema as Lowercase

Posted: Tue Mar 12, 2013 3:32 pm

Newbie

Joined: Tue Mar 12, 2013 3:11 pm
Posts: 1

I discovered that when I call setMaxResults(), the SQL generated after returns the @SCHEMA@ scalar variable as lower case to @schema@. We have a routing schema interceptor class that replaces the @SCHEMA@ with the appropriate database schema for the current session.

Here's a sample code fragment demonstrating the issue:

Code:

Session currentSession = this.getCurrentSession();
Criteria criteria = currentSession.createCriteria(MyEntity.class);
criteria.setFirstResult(firstRow);
criteria.setMaxResults(maxResults); // causes @SCHEMA@ to be returned as @schema@

// throws a HibernateException:
// org.hibernate.exception.GenericJDBCException: Must declare the scalar variable "@schema@".
List<MyEntity> list = criteria.list(); 

Our routing schema interceptor was replacing text based on the uppercase schema variable, so we've modified it to do a case-insensitive search, so it's not a problem.

Here are my versions:
Java: 1.7
Hibernate: 4.1.3
Spring: 3.1.2
Database: SQL Server 2008 R2

Has anyone else seen this issue?

Thanks,
Derek

Top

JulianB

Post subject: Re: Criteria#setMaxResults() Returns Schema as Lowercase

Posted: Wed Mar 27, 2013 7:13 am

Newbie

Joined: Wed Nov 23, 2011 8:33 am
Posts: 4

Hey Derek,

I have got exact the same issue. When calling setMaxResult the schema, table- and column-names generated after are lower case. In my case this causes an error, because some of our databases working case sensitive and then this query causes a SQLServerException:

Code:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'schema.view'.

This is the working SQL-Statement:

Code:

WITH query AS (select ROW_NUMBER() OVER (order by this_.LOG_TIME desc) as __hibernate_row_nr__, this_.LOG_TIME as log1_12_0_, this_.CMDUSER as cmduser12_0_, this_.MESSAGE as message12_0_, this_.MSG_ID as msg4_12_0_ from SCHEMA.VIEW this_ ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ?

This is the generated SQL when using setMaxResult:

Code:

WITH query AS (select ROW_NUMBER() OVER (order by this_.log_time desc) as __hibernate_row_nr__, this_.log_time as log1_12_0_, this_.cmduser as cmduser12_0_, this_.message as message12_0_, this_.msg_id as msg4_12_0_ from schema.view this_ ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ?

Java: JDK 1.6.0_24
Hibernate: 3.6.8.Final
Database: SQL Server 2008 R2

Has anyone a working solution for this issue?

Best regards,
Julian

Top

JulianB

Post subject: Re: Criteria#setMaxResults() Returns Schema as Lowercase

Posted: Wed Mar 27, 2013 8:50 am

Newbie

Joined: Wed Nov 23, 2011 8:33 am
Posts: 4

Looks like the issue is fixed in Hibernate 4.1.9.Final!

For MSSQL the new Hibernate-Version uses "TOP" instead of the above-mentioned technique. The new query looks like:

Code:

select TOP(?) this_.LOG_TIME as LOG1_12_0_, this_.CMDUSER as CMDUSER12_0_, this_.MESSAGE as MESSAGE12_0_, this_.MSG_ID as MSG4_12_0_ from SCHEMA.VIEW this_

Regards,
Julian

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum