These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Problem with Hibernate inserting extra bracket in LIMIT SQL
PostPosted: Tue Feb 06, 2007 1:52 pm 

Joined: Tue Nov 22, 2005 10:01 am
Posts: 7
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())
Iterator iter = null;
if(lstRules != null) {
iter = lstRules.iterator();
//iter = lstRules.iterator();

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 )

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

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,



 Post subject:
PostPosted: Tue Feb 06, 2007 6:55 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
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.

 Post subject:
PostPosted: Wed Feb 07, 2007 8:03 am 

Joined: Tue Nov 22, 2005 10:01 am
Posts: 7
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());
List lstRules = q.list();

This now returns the desired result set.

Thanks for the help.


Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.