5 Replies Latest reply on Jan 23, 2006 9:11 AM by dornus

    EntityManager createQuery().setLimit() does not work with My

    dornus Novice

      I 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 )

      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)