comma in where clause causes problem

mike.2 · **Joined:** Tue Apr 11, 2006 1:28 pm **Posts:** 6

Hello,

I have a problem with the generation of the WHERE clause in the SQL statement. Essentially, a comma is being generated where the word "and" should be. Is it possible to change the output of the WHERE clause by modifying the dialect?

Thanks for the help,
Mike

Hibernate version: 3.1

Mapping documents:

Code:

   <class name="Comment" table="tp_comment">
      <id name="id" type="integer">
         <generator class="native" />
      </id>      
      <any name="parent" meta-type="string" id-type="integer">
         <column name="parentType" sql-type="class" not-null="false"/>
         <column name="parent_id" sql-type="integer" not-null="false"/>
      </any>
      <many-to-one name="commentOriginator" class="com.cessna.techpubs.data.Person"/>
      <property name="commentDate" type="date"/>
      <property name="originatorsRole" type="string"/>
      <property name="commentString" type="string" />
   </class>

Code between sessionFactory.openSession() and session.close():

Code:

      Transaction t = session.beginTransaction();
      Query q = session.createQuery("FROM Comment WHERE parent is null");
      List list = q.list();
      t.commit();

Full stack trace of any exception that occurs:
could not execute query [select comment0_.id as id14_, comment0_.parentType as parentType14_, comment0_.parent_id as parent3_14_, comment0_.commentOriginator as commentO4_14_, comment0_.commentDate as commentD5_14_, comment0_.originatorsRole as originat6_14_, comment0_.commentString as commentS7_14_ from tp_comment comment0_ where (comment0_.parentType, comment0_.parent_id) is null]
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '(comment0_.parentType, comment0_.parent_id) is null'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4831)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at HibernateTest.deleteOrphanedComments(HibernateTest.java:242)
at HibernateTest.testComment(HibernateTest.java:1416)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
SQL Error: -3100, SQLState: 37000

Name and version of the database you are using:MSAccess

The generated SQL (show_sql=true):
select comment0_.id as id14_, comment0_.parentType as parentType14_, comment0_.parent_id as parent3_14_, comment0_.commentOriginator as commentO4_14_, comment0_.commentDate as commentD5_14_, comment0_.originatorsRole as originat6_14_, comment0_.commentString as commentS7_14_ from tp_comment comment0_ where (comment0_.parentType, comment0_.parent_id) is null

[/code]

mseritan · **Posted:** Wed Nov 01, 2006 11:23 pm

Mike,

What's a sql-type of class? Is that the right value for that attribute? Maybe hibernate gets confused...

Marius

Kaneda · **Posted:** Thu Nov 02, 2006 6:21 am

May be you can use a named query instead of the HQL Query you are using at the moment. Because of the any tag in the mapping the Querybuilder creates the wrong statement as you already mentioned in your posting.
If you need both fields else you should follow the next post.

JayeshJ · **Joined:** Tue May 16, 2006 3:32 am **Posts:** 117

Try

Code:

FROM Comment WHERE parent.parent_id is null

mike.2 · **Joined:** Tue Apr 11, 2006 1:28 pm **Posts:** 6

I awarded the credits prematurely. The fix was to change the HQL statement from parent to parent_id.

Code:

FROM Comment WHERE parent_id is null

mseritan -
I do not know what the sql-type="class" really means, In fact I had a very difficult time finding any real documentation on the <any> option.
I got that chunk of code from someone elses post. After I got it working, I removed the sql-type altogether and it worked fine.

Thank you all for the help.

Mike