Exception with LIMIT

[Roy Udassin]

I am facing a problem with query.setMaxResults(..):

The setup I am using is:
mySQL 4.1 with mysql-connector-java-3.1.12-bin.jar as the conntector
Hibernate 3.0
Weblogic 8.1 SP 5

The code is:
Session session;
Map params = new HashMap(2);
params.put("operatorId", new Integer(operatorId));
params.put("priority", new Integer(priority));
String command = "FROM com.bamboo.entities.TransmissionSms as ts WHERE ts.operatorId=:operatorId and ts.priority=:priority and ts.sendFlag=0"

session = sessionFactory.openSession();
Query query = session.createQuery(command);
query.setMaxResults(100);
Iterator keyIterator = params.keySet().iterator();
String name;
while (keyIterator.hasNext()) {
name = (String) keyIterator.next();
query.setParameter(name, params.get(name));
}
return query.list();

When running this code, in the jdbc.log of the hibernate we see the following exception:

SQLException: SQLState(42000) vendor code(1064)
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 '?' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1291)
at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:307)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4288)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4226)
at weblogic.jdbc.common.internal.ConnectionEnv.makeStatement(ConnectionEnv.java:1206)
at weblogic.jdbc.common.internal.ConnectionEnv.getCachedStatement(ConnectionEnv.java:994)
at weblogic.jdbc.common.internal.ConnectionEnv.getCachedStatement(ConnectionEnv.java:933)
at weblogic.jdbc.wrapper.Connection.prepareStatement(Connection.java:359)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.bamboo.dao.HibSessionWrapper$3.daoOperation(HibSessionWrapper.java:171)
at com.bamboo.dao.HibSessionWrapper.execute(HibSessionWrapper.java:38)
at com.bamboo.dao.TransmissionDao.getSMSMessages(TransmissionDao.java:180)
at com.bamboo.messaging.ejb.TransmissionHandler.getOtaMessages(TransmissionHandler.java:243)
at com.bamboo.messaging.ejb.TransmissionHandler.onMessage(TransmissionHandler.java:94)
at weblogic.ejb20.internal.MDListener.execute(MDListener.java:400)
at weblogic.ejb20.internal.MDListener.transactionalOnMessage(MDListener.java:333)
at weblogic.ejb20.internal.MDListener.onMessage(MDListener.java:298)
at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:2686)
at weblogic.jms.client.JMSSession.execute(JMSSession.java:2598)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)


When I added to the URL of the DB connection the parameter: useServerPrepStmts=false the exception was gone.


The query returned the right number of objects as expected but still produce this exception.

Please assist.

Thanks

Roy Udassin