-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
gpani
 Post subject: Hibernate creating SQL that fails in MySQL
PostPosted: Tue Jul 27, 2004 10:17 pm 
Senior
Senior

Joined: Wed Mar 24, 2004 11:40 am
Posts: 146
Location: Indianapolis, IN, USA
I am using Hibernate 2.1.4 with Resin 2.1.12 on MySQL 4.1. The SQL created from Hibernate HQL fails and I can't seem to see any reserved words that I might be using. The tables are FUNCTIONS, USERGROUPS and USERGROUPFUNCTIONS which is used as a join table.

TIA

I am using the following HQL

Code:
SELECT function FROM Function function WHERE function NOT IN
  (
    SELECT userGroupFunction.function
    FROM UserGroupFunction userGroupFunction
    WHERE userGroupFunction.userGroup.userGroupId = :userGroupId
  )
ORDER BY function.functionName


This creates the following SQL
Code:
select
  function0_.FUNCTIONID as FUNCTIONID, function0_.FUNCTIONTYPEID as FUNCTION2_, function0_.FUNCTIONNAME as FUNCTION3_, function0_.
  FUNCTIONCODE as FUNCTION4_, function0_.FUNCTIONCLASS as FUNCTION5_, function0_.FUNCTIONDESCRIPTION as FUNCTION6_, function0_.
  FUNCTIONURL as FUNCTION7_, function0_.TOPLEVEL as TOPLEVEL, function0_.CREATEBY as CREATEBY, function0_.CREATEON as CREATEON,
  function0_.UPDATEBY as UPDATEBY, function0_.UPDATEON as UPDATEON
from FUNCTIONS function0_
where ( function0_.FUNCTIONID NOT IN
   (
     select usergroupf1_.FUNCTIONID
     from USERGROUPFUNCTIONS usergroupf1_
     where (usergroupf1_.USERGROUPID=?)
  )
)
order by  function0_.FUNCTIONNAME


The following is the stack trace that I am seeing:

Code:
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 'select usergroupf1_.FUNCTIONID from USERGROUPFUNCTIONS usergrou"

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
Top
 Profile  
 
gpani
 Post subject:
PostPosted: Wed Jul 28, 2004 10:17 am 
Senior
Senior

Joined: Wed Mar 24, 2004 11:40 am
Posts: 146
Location: Indianapolis, IN, USA
From the looks of it, it appears that MySQL does not support SELECT subqueries and hence my problem.

http://www.hibernate.org/80.html

Anyone here have an alternate suggestion to how I should use HQL to fix my problem. Or should I resort to writing a list comparator and pop out the mismatches?
Top
 Profile  
 
gpani
 Post subject:
PostPosted: Wed Jul 28, 2004 5:52 pm 
Senior
Senior

Joined: Wed Mar 24, 2004 11:40 am
Posts: 146
Location: Indianapolis, IN, USA
It appears like the only solution I can find for this is to get two separate collections and use org.apache.commons.collections.CollectionUtils to get the disjoined collection instead of using SQL to do it.

http://jakarta.apache.org/commons/colle ... index.html

If there is a better solution out there to do this within hibernate, I would really appreciate it.
Top
 Profile  
 
robwms63
 Post subject: Another Option: Beta MySQL Release
PostPosted: Fri Jul 30, 2004 5:13 pm 
Newbie

Joined: Fri Jul 30, 2004 3:11 pm
Posts: 18
Support for subqueries is there....
Top
 Profile  
 
gpani
 Post subject:
PostPosted: Wed Aug 04, 2004 6:21 pm 
Senior
Senior

Joined: Wed Mar 24, 2004 11:40 am
Posts: 146
Location: Indianapolis, IN, USA
Do you know why I am getting that error stack trace then?
Top
 Profile  
 
michael
 Post subject:
PostPosted: Wed Aug 04, 2004 7:44 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
I suppose your mysql version is still to low. Why don't you just try if you can do subqueries?
Top
 Profile  
 
gpani
 Post subject:
PostPosted: Thu Aug 05, 2004 7:55 am 
Senior
Senior

Joined: Wed Mar 24, 2004 11:40 am
Posts: 146
Location: Indianapolis, IN, USA
michael,

i am using MySQL 4.1 and it indeed doesn't support subqueries. i was just wondering what others were doing to compensate for this with hibernate while using MySQL 4.1. I just went ahead and used commons-collection to do a subtract between two collections.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.