[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2207) Suggested fix for HQL - MySQL setMaxResults issue

Toby Moore (JIRA) noreply at atlassian.com
Fri Nov 3 11:16:05 EST 2006 

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2207?page=comments#action_25125 ] 

Toby Moore commented on HHH-2207:
---------------------------------

Sorry guys I've been having eclipse related nightmares this afternoon, so haven't been able to put a test case together, however I can give you the very simple mapping and hql I use. I map using EJB3 annotations, so no mapping files, but there's nothing special about Itmm.

        Query query = entityManager.createQuery("select itmm from Itmm as itmm where itmm.itmmId < ? order by itmm.itmmId asc");
        
        query.setParameter(1, new Long(1000));
        query.setFirstResult(2);
        query.setMaxResults(5);
        
        Iterator<Itmm> itmmsIterator  = query.getResultList().iterator();

this gives me the exception SQL 

Hibernate: select itmm0_.itmm_id as itmm1_3_, itmm0_.itmm_value as itmm2_3_, itmm0_.itmm_creator as itmm8_3_, itmm0_.itmm_updator as itmm10_3_, itmm0_.itmm_itmv_id as itmm9_3_, itmm0_.itmm_itmi_id as itmm11_3_, itmm0_.itmm_name as itmm3_3_, itmm0_.itmm_order as itmm4_3_, itmm0_.itmm_created as itmm5_3_, itmm0_.itmm_updated as itmm6_3_, itmm0_.itmm_searchable as itmm7_3_ from mcd_data.itmm itmm0_ where itmm0_.itmm_id<? order by itmm0_.itmm_id asc limit ?, ?

and exception : 

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:647)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
	at com.mindcandydesign.persistence.daohomes.ItmmHome.findAllItmms(ItmmHome.java:209)
	at com.mindcandydesign.persistence.daohomes.ItmmHome.main(ItmmHome.java:225)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:2155)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2036)
	at org.hibernate.loader.Loader.list(Loader.java:2031)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:398)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
	... 2 more
Caused by: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2556)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2584)
	at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:2540)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setInt(NewProxyPreparedStatement.java:677)
	at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1615)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1559)
	at org.hibernate.loader.Loader.doQuery(Loader.java:661)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.doList(Loader.java:2152)
	... 10 more


> Suggested fix for HQL - MySQL setMaxResults issue
> -------------------------------------------------
>
>          Key: HHH-2207
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2207
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.2.0.ga
>  Environment: Hi 3.2.0.ga MySQL
>     Reporter: Toby Moore
>  Attachments: querloaderdiff.patch
>
> Original Estimate: 0 minutes
>         Remaining: 0 minutes
>
> Hi,
> After some experimentation, we'd like to suggest an easy fix to a problem we encountered.
> When using HQL with the Hibernate 3 entity manager to limit the number of records returned ( at the database level, via generated SQL, not in memory) we were getting an exception due to the query builder trying to attach a parameter to the prepared statement that was out of bounds :
> by: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
>    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
>    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2556)
>    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2584)
>    at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:2540)
>    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setInt(NewProxyPreparedStatement.java:677)
>    at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1616)
>    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560)
>    at org.hibernate.loader.Loader.doQuery(Loader.java:661)
>    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
>    at org.hibernate.loader.Loader.doList(Loader.java:2156)
>    ... 10 more
> On looking at the code in the method bindParameterValues in org.hibernate.loader.hql.QueryLoader ( line 491 ) the method returns the next parameter position that needs to be set, not the amount of parameters it has added, which is expected in Loader.java on the line
>          col += bindParameterValues( st, queryParameters, col, session );
> This causes the parameter count to get out of sync and thus throw the expcetion saying that it can't add a parameter out of range.
> A simple fix for this is to change the return line in QueryLoader to be
> return position - startPosition;
> which will return the amount of parameters this method added.
> We've tested this on our mysql implementation and it works, and run the unit tests to check that no extra errors are generated. If this seems like a good fix then i'll submit it as a patch via the JIRA, but would like to hear any thoughts first?
> Kind regards,
> TobyM - MindCandyDesign London

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

More information about the hibernate-issues mailing list