I'm trying to execute an HQL Delete, but hibernate complains about some ',' in the HQL command.

Here's a test:



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

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:

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!

Try an explicit join



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.

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

Your comments



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:

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 =

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