EntityManager createQuery().setLimit() does not work with My
dornus Sep 9, 2005 11:52 AMI have XP Pro, 4.0.3RC1, MySql 5.0.12 and ConnectorJ 3.1.10
I tried to run the following code and it gave me an error. When I comment out the setLimit() it works fine. I read through the forums and tried adding "?useServerPrepStmts=false" to my driver connection but that did not solve the problem.
Is this a bug?
List <MyObj> results = em.createQuery( "from User p where p.emailAddress = :email order by p.lastName, p.firstName" ) .setParameter( "email", emailAddress ) .setFirstResult( 1 ) .setMaxResults( 10 ) .getResultList();
The error is:
2005-09-09 11:46:16,523 DEBUG [org.hibernate.SQL] select limit ? ? user0_.ownerId as ownerId, user0_.emailAddress as emailAdd3_20_,user0_.firstName as firstName20_, user0_.lastName as lastName20_, from user user0_ where user0_.emailAddress=? order by user0_.lastName, user0_.firstName 2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement 2005-09-09 11:46:16,523 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() jdisten@hotmail.com -> email [3] 2005-09-09 11:46:16,523 DEBUG [org.hibernate.type.StringType] binding 'blah@hotmail.com' to parameter: 3 2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1) 2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement 2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection 2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.ConnectionManager] closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)] 2005-09-09 11:46:16,523 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select user0_.ownerId as ownerId, user0_.emailAddress as emailAdd3_20_, user0_.firstName as firstName20_, user0_.lastName as lastName20_ from user user0_ where user0_.emailAddress=? order by user0_.lastName, user0_.firstName] java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1 10 user0_.ownerId as ownerId, user0_.' at line 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120) at org.hibernate.loader.Loader.getResultSet(Loader.java:1537)