-->
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.  [ 8 posts ] 
Author Message
 Post subject: Hibernate fails to execute HQL Delete
PostPosted: Wed Sep 10, 2008 3:02 pm 
Newbie

Joined: Fri Sep 14, 2007 9:41 am
Posts: 8
I'm trying to execute an HQL Delete, but hibernate complains about some ',' in the HQL command.

Here's a test:
Code:
public static void main(String[] args) {
      Session s = HibernateUtil.getSessionFactory().getCurrentSession();
      Transaction t = s.beginTransaction();
      t.begin();
      UsuarioGrupoDAO ugDAO = new UsuarioGrupoDAO();
      ugDAO.deleteByHQL("delete UsuarioGrupo usuariorGrp"+
       " where usuariorGrp.usuario.idusuario = 77"+
       " and usuariorGrp.grupo.idgrupo in (3)"+
       " and usuariorGrp.grupo.cxpostal.idcxpostal = 2");
      t.commit();
   }



Hibernate version:

Hibernate 3.1.3

Full stack trace of any exception that occurs:

xception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:334)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:209)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1126)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.pix.hibernate.entities.grupo.BaseHibernateDAO.deleteByHQL(BaseHibernateDAO.java:45)
at com.pix.tests.HibernateTests.main(HibernateTests.java:24)
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near ','.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 6 more

Name and version of the database you are using:

MS SQL SERVER 2000

The generated SQL (show_sql=true):

DEBUG SQL:346 - delete from vanpix.dbo.usuario_grupo, vanpix.dbo.grupo grupo1_ where idusuario=77 and (idgrupo in (3)) and idcxpostal=2
Hibernate: delete from vanpix.dbo.usuario_grupo, vanpix.dbo.grupo grupo1_ where idusuario=77 and (idgrupo in (3)) and idcxpostal=2

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Code:


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 10, 2008 4:23 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Could it be that the 'usuariorGrp.grupo' part generates an implicit join? According to documentation at http://www.hibernate.org/hib_docs/v3/re ... tch-direct this is not supported:

Quote:
No Section 14.4, “Forms of join syntax” (either implicit or explicit) can be specified in a bulk HQL query


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 11, 2008 2:29 pm 
Newbie

Joined: Fri Sep 14, 2007 9:41 am
Posts: 8
Humm, this can probably be it, but how can I fix this then?

Using a subquery in the where clause? Will that stop the generated SQL show multiple tables in the delete clause?

How would this HQL Be?

Thanks in advance!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 11, 2008 3:39 pm 
Newbie

Joined: Thu Oct 26, 2006 11:50 am
Posts: 17
Location: Chesterfield, VA
Try an explicit join

Code:

delete UsuarioGrupo usuariorGrp
    left join usuario as usr
       with  usr.idusuario = 77
    left join grupo as grp
       with grp.idgrupo in (3)
          and grp.cxpostal.idcxpostal = 2




I'm not sure the name of the table that joins to UsuarioGrupo, I'm assumming usuario for the example above. Same for grupo. It looks like you might have one more nested join there.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 12, 2008 8:52 am 
Newbie

Joined: Fri Sep 14, 2007 9:41 am
Posts: 8
But where is it Joining with my UsuarioGrupo table?

I tried this but it gave an error : java.lang.IllegalArgumentException: node to traverse cannot be null!


UsuarioGrupo joins with -> Grupo table and Usuario table.
Grupo joins with Cxpostal and UsuarioGrupo.

I'm reading the hibernate documentation and see what I can do with this explicit joins, and where is the problem with this HQL. =/


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 12, 2008 10:13 am 
Newbie

Joined: Thu Oct 26, 2006 11:50 am
Posts: 17
Location: Chesterfield, VA
Your comments

Quote:

UsuarioGrupo joins with -> Grupo table and Usuario table.
Grupo joins with Cxpostal and UsuarioGrupo.



Imply a circular mapping which you cannot have and don't want in your sql.

UsuarioGrupo joins with Grupo and you also want Grupo to join to UsuarioGrupo .

The only join missing is Grupo joins with Cxpostal

This might work:

Code:
delete UsuarioGrupo usuariorGrp
    left join usuario as usr
       with  usr.idusuario = 77
    left join ( grupo as grp
       with grp.idgrupo in (3)
          and grp.cxpostal.idcxpostal = 2
       join cxpostal as cxp
          with cxp.idcxpostal = 2)



Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 12, 2008 10:31 am 
Newbie

Joined: Fri Sep 14, 2007 9:41 am
Posts: 8
Yes it is like:

UsuarioGrupo is the connection table to do the N:N association with Usuario and Grupo.

The database is mapped like this:

Usuario-1.........N-UsuarioGrupo-N.....1-Grupo-1..........N-Cxpostal

The HQL keeps giving the same 'node to tranverse' error.
I also tried a simpler delete statement with only one join and it didn't work either =/

Ex:
delete UsuarioGrupo usuariorGrp
left join usuario as usr
with usr.idusuario = 77

Same 'node to tranverse cannot be null' what is wrong with this one =


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 12, 2008 4:56 pm 
Newbie

Joined: Fri Sep 14, 2007 9:41 am
Posts: 8
Have anyone done this before and can post a code snippet please? I'm having a hard time sorting this out, although I was able to delete using alternative ways, I want to make this work. Thanks!!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 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.