Hibernate version: hibernate-2.1.6.jar

Code between sessionFactory.openSession() and session.close():
Session s = HibernateUtil.getSessionFactory().openSession();
Transaction tx = s.beginTransaction();
//createQuery uses HQL to select from the Jobs table
List lstRules = s.createQuery("from Jobs j WHERE j.mailinglist_job_id = " + getMlJob().getMailinglist_job_id() + " " +
" LIMIT 0, " + getMlJob().getSend_per_minute())
.list();
Iterator iter = null;
if(lstRules != null) {
iter = lstRules.iterator();
}
//iter = lstRules.iterator();
tx.commit();
s.close();
HibernateUtil.commitTransaction();
HibernateUtil.closeSession();


Full stack trace of any exception that occurs:
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1547)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.mailbrand.mailreceiver.BulkSendMonitor.getJob(BulkSendMonitor.java:135)
at com.mailbrand.mailreceiver.BulkSendMonitor.manageJobs(BulkSendMonitor.java:108)
at com.mailbrand.mailreceiver.BulkSendMonitor.run(BulkSendMonitor.java:201)
at java.lang.Thread.run(Thread.java:595)
at com.mailbrand.threadpooling.ThreadPoolWorker.runIt(ThreadPoolWorker.java:79)
at com.mailbrand.threadpooling.ThreadPoolWorker.runWork(ThreadPoolWorker.java:70)
at com.mailbrand.threadpooling.ThreadPoolWorker.access$0(ThreadPoolWorker.java:56)
at com.mailbrand.threadpooling.ThreadPoolWorker$1.run(ThreadPoolWorker.java:33)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Syntax error or access violation message from server: "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 0 , 20 )' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at com.mchange.v2.sql.filter.FilterPreparedStatement.executeQuery(FilterPreparedStatement.java:68)
at com.mchange.v2.c3p0.impl.C3P0PooledConnection$2.executeQuery(C3P0PooledConnection.java:567)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
... 10 more

Name and version of the database you are using: Mysql Server 4.1

The generated SQL (show_sql=true):
select jobs0_.mailinglist_job_id as mailingl1_, jobs0_.contact_id as contact_id, jobs0_.status as status, jobs0_.time_stamp as time_stamp from jobs jobs0_ where (jobs0_.mailinglist_job_id=3 LIMIT 0 , 20 )


Hi,
Ok This query is nearly right except why is hibernate adding the brackets around this part of the code (jobs0_.mailinglist_job_id=3 LIMIT 0 , 20 ) @ the end of the query. As far as I can see in my HQL in my code I am not adding these brackets?

s.createQuery("from Jobs j WHERE j.mailinglist_job_id = " + getMlJob().getMailinglist_job_id() + " " +
" LIMIT 0, " + getMlJob().getSend_per_minute())
.list();

Anyone any ideas?? I've only got so much hair left on my head to pull out!! If the brackets are taken out the query runs fine when I test it from an SQL command prompt.

Let me know if you do,

Thanks,

divo

Hibernate is correctly doing its job. Keep in mind that your mixing HQL and SQL fragments by appending your limit clause. You need to remove the LIMIT clause and use hibernates API for setting the first record and max record. If you have set the dialect correctly then it will generate the correct syntax for the database in use.

David,
Thanks for the reply. I took the LIMIT part out of My HQL as suggested. Code now looks like:

Query q = s.createQuery("from Jobs j WHERE j.mailinglist_job_id = " + getMlJob().getMailinglist_job_id());
q.setFirstResult(0);
q.setMaxResults(1000);
List lstRules = q.list();

This now returns the desired result set.

Thanks for the help.

divo