org.hibernate.exception.SQLGrammarException: could not execute update query

Hibernate JIRA | Ludger Springmann | 9 years ago
  1. 0

    When using an update HQL with subselect containing at least two 'member of' expressions the SQL sent to DB2 is invalid. (An example as JUnit Test is attached to this entry.) The correspondig HQL with select instead of update works fine (see test). The problem is caused by a not qualifyed id in subselect query. Here is the result of the test (first select then update): HQL: from de.iskv.hibernate.test.Person person where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: select person0_.OID as OID0_, person0_.NAME as NAME0_, person0_.FIRSTNAME as FIRSTNAME0_ from SPRINGMA.TEST_PERSON person0_ where person0_.OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( address2_.OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( blog3_.OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) HQL: update de.iskv.hibernate.test.Person person set name = ' ' where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: update SPRINGMA.TEST_PERSON set NAME=' ' where OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) E Time: 2,857 There was 1 error: 1) testUpdate(de.iskv.hibernate.test.HibernateSelectUpdateTestCase)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:396) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.testUpdate(HibernateSelectUpdateTestCase.java:57) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.main(HibernateSelectUpdateTestCase.java:104) Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -203, SQLSTATE: 42702, SQLERRMC: OID at com.ibm.db2.jcc.a.rf.e(rf.java:1680) at com.ibm.db2.jcc.a.rf.a(rf.java:1239) at com.ibm.db2.jcc.b.jb.h(jb.java:139) at com.ibm.db2.jcc.b.jb.a(jb.java:43) at com.ibm.db2.jcc.b.w.a(w.java:30) at com.ibm.db2.jcc.b.cc.g(cc.java:160) at com.ibm.db2.jcc.a.rf.n(rf.java:1219) at com.ibm.db2.jcc.a.sf.gb(sf.java:1790) at com.ibm.db2.jcc.a.sf.d(sf.java:2266) at com.ibm.db2.jcc.a.sf.Y(sf.java:540) at com.ibm.db2.jcc.a.sf.executeUpdate(sf.java:523) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75) ... 22 more FAILURES!!! Tests run: 2, Failures: 0, Errors: 1 The DB2 error code means: SQL0203N A reference to column name is ambiguous. (here OID) Explanation: The column name is used in the statement and there is more than one possible column to which it could refer. This could be the result of: - two tables specified in a FROM clause that have columns with the same name - the ORDER BY clause refers to a name that applies to more than one column in the select list - a reference to a column from the subject table in a CREATE TRIGGER statement does not use the correlation name to indicate if it refers to the old or new transition variable. The column name needs further information to establish which of the possible table columns it is. The statement cannot be processed. User Response: Add a qualifier to the column name. The qualifier is the table name or correlation name. A column may need to be renamed in the select list. sqlcode: -203 sqlstate: 42702 Both member of expressions result in a reference two column OID. This is ambiguous.

    Hibernate JIRA | 9 years ago | Ludger Springmann
    org.hibernate.exception.SQLGrammarException: could not execute update query
  2. 0

    When using an update HQL with subselect containing at least two 'member of' expressions the SQL sent to DB2 is invalid. (An example as JUnit Test is attached to this entry.) The correspondig HQL with select instead of update works fine (see test). The problem is caused by a not qualifyed id in subselect query. Here is the result of the test (first select then update): HQL: from de.iskv.hibernate.test.Person person where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: select person0_.OID as OID0_, person0_.NAME as NAME0_, person0_.FIRSTNAME as FIRSTNAME0_ from SPRINGMA.TEST_PERSON person0_ where person0_.OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( address2_.OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( blog3_.OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) HQL: update de.iskv.hibernate.test.Person person set name = ' ' where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: update SPRINGMA.TEST_PERSON set NAME=' ' where OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) E Time: 2,857 There was 1 error: 1) testUpdate(de.iskv.hibernate.test.HibernateSelectUpdateTestCase)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:396) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.testUpdate(HibernateSelectUpdateTestCase.java:57) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.main(HibernateSelectUpdateTestCase.java:104) Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -203, SQLSTATE: 42702, SQLERRMC: OID at com.ibm.db2.jcc.a.rf.e(rf.java:1680) at com.ibm.db2.jcc.a.rf.a(rf.java:1239) at com.ibm.db2.jcc.b.jb.h(jb.java:139) at com.ibm.db2.jcc.b.jb.a(jb.java:43) at com.ibm.db2.jcc.b.w.a(w.java:30) at com.ibm.db2.jcc.b.cc.g(cc.java:160) at com.ibm.db2.jcc.a.rf.n(rf.java:1219) at com.ibm.db2.jcc.a.sf.gb(sf.java:1790) at com.ibm.db2.jcc.a.sf.d(sf.java:2266) at com.ibm.db2.jcc.a.sf.Y(sf.java:540) at com.ibm.db2.jcc.a.sf.executeUpdate(sf.java:523) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75) ... 22 more FAILURES!!! Tests run: 2, Failures: 0, Errors: 1 The DB2 error code means: SQL0203N A reference to column name is ambiguous. (here OID) Explanation: The column name is used in the statement and there is more than one possible column to which it could refer. This could be the result of: - two tables specified in a FROM clause that have columns with the same name - the ORDER BY clause refers to a name that applies to more than one column in the select list - a reference to a column from the subject table in a CREATE TRIGGER statement does not use the correlation name to indicate if it refers to the old or new transition variable. The column name needs further information to establish which of the possible table columns it is. The statement cannot be processed. User Response: Add a qualifier to the column name. The qualifier is the table name or correlation name. A column may need to be renamed in the select list. sqlcode: -203 sqlstate: 42702 Both member of expressions result in a reference two column OID. This is ambiguous.

    Hibernate JIRA | 9 years ago | Ludger Springmann
    org.hibernate.exception.SQLGrammarException: could not execute update query
  3. 0

    Sub-optimal performance of a UDF

    Google Groups | 1 decade ago | Mahesh S
    com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Executing sql UDF in java

    Google Groups | 1 decade ago | Mahesh S
    com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: HEALTHCAREDB.RISK_EQUATION_1;PROCEDURE
  6. 0

    I need to create a dynamic query based on the some condition. I don't want to hardcode this query in my java class. So that's the reason i want to get this using Named query in hibernate. Below is how iam trying to implement. DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String today = sdf.format(date); Query query = session.getNamedQuery("findDateDifference"); query.setString(1, today); List date_Diff_List = query.list(); <sql-query name="findDateDifference"><![CDATA[ select (? - temp.bv_date) as days from ( select CLO_DTE as bv_date from BRAND ) temp]]> </sql-query> I need to pass the value to the '?' which is given in above query. When I run the above code it gives me error as below. Can anyone please give me some suggestions how to fix this issue. Hibernate: select (? - temp.bv_date) as days from ( select CLO_DTE as bv_date from BRAND ) temp org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1333) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:146) at com.hibernate.ExceptionTest.getDateDifference(ExceptionTest.java:219) at com.hibernate.ExceptionTest.main(ExceptionTest.java:43) Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -171, SQLSTATE: 42815, SQLERRMC: 2;- at com.ibm.db2.jcc.a.hd.e(hd.java:1659) at com.ibm.db2.jcc.a.hd.a(hd.java:1235) at com.ibm.db2.jcc.a.hd.a(hd.java:1221) at com.ibm.db2.jcc.c.jb.h(jb.java:142) at com.ibm.db2.jcc.c.jb.a(jb.java:43) at com.ibm.db2.jcc.c.w.a(w.java:30) at com.ibm.db2.jcc.c.cc.g(cc.java:160) at com.ibm.db2.jcc.a.hd.n(hd.java:1215) at com.ibm.db2.jcc.a.id.gb(id.java:1780) at com.ibm.db2.jcc.a.id.d(id.java:2255) at com.ibm.db2.jcc.a.id.X(id.java:505) at com.ibm.db2.jcc.a.id.executeQuery(id.java:488) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107) at org.hibernate.loader.Loader.getResultSet(Loader.java:1183) at org.hibernate.loader.Loader.doQuery(Loader.java:363) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203) at org.hibernate.loader.Loader.doList(Loader.java:1499) ... 6 more

    Hibernate JIRA | 7 years ago | kalyan buddala
    org.hibernate.exception.SQLGrammarException: could not execute query

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.ibm.db2.jcc.a.SqlException

      DB2 SQL error: SQLCODE: -203, SQLSTATE: 42702, SQLERRMC: OID

      at com.ibm.db2.jcc.a.rf.e()
    2. IBM DB2
      rf.a
      1. com.ibm.db2.jcc.a.rf.e(rf.java:1680)
      2. com.ibm.db2.jcc.a.rf.a(rf.java:1239)
      2 frames
    3. com.ibm.db2
      cc.g
      1. com.ibm.db2.jcc.b.jb.h(jb.java:139)
      2. com.ibm.db2.jcc.b.jb.a(jb.java:43)
      3. com.ibm.db2.jcc.b.w.a(w.java:30)
      4. com.ibm.db2.jcc.b.cc.g(cc.java:160)
      4 frames
    4. IBM DB2
      sf.executeUpdate
      1. com.ibm.db2.jcc.a.rf.n(rf.java:1219)
      2. com.ibm.db2.jcc.a.sf.gb(sf.java:1790)
      3. com.ibm.db2.jcc.a.sf.d(sf.java:2266)
      4. com.ibm.db2.jcc.a.sf.Y(sf.java:540)
      5. com.ibm.db2.jcc.a.sf.executeUpdate(sf.java:523)
      5 frames
    5. Hibernate
      BasicExecutor.execute
      1. org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
      1 frame
    6. Hibernate
      JDBCExceptionHelper.convert
      1. org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      2. org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      2 frames
    7. Hibernate
      BasicExecutor.execute
      1. org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
      1 frame
    8. Hibernate HQL/JP-QL Parser
      QueryTranslatorImpl.executeUpdate
      1. org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
      1 frame
    9. Hibernate
      QueryImpl.executeUpdate
      1. org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
      2. org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
      3. org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
      3 frames
    10. de.iskv.hibernate
      HibernateSelectUpdateTestCase.testUpdate
      1. de.iskv.hibernate.test.HibernateSelectUpdateTestCase.testUpdate(HibernateSelectUpdateTestCase.java:57)
      1 frame
    11. Java RT
      DelegatingMethodAccessorImpl.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      3 frames
    12. de.iskv.hibernate
      HibernateSelectUpdateTestCase.main
      1. de.iskv.hibernate.test.HibernateSelectUpdateTestCase.main(HibernateSelectUpdateTestCase.java:104)
      1 frame