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

[Toby Moore (JIRA)]

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

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

Ah yes, after looking at the source a bit closer, it seems that the same problem is replicated in another part of the code. The calling code expects the method to return how many parameters were added, but instead it returns the index of the parameter passed in. I've added another patch to this thread which fixes both problems for us. 

This all seems to be related to the SQL dialects that have a limit clause at the end of the statement, which is why we're finding these problems for MySQL. 

Let me know if the patch works!


> 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