java.sql.SQLException: ORA-00936: missing expression

Hibernate JIRA | Madhusudan M Krishnamurthy | 1 decade ago
  1. 0

    After roughly a week and half of painful debugging, I figured out the problem with hibernate generating only a "PARTIAL QUERY" for the below mentioned code. (Right at the end of this post) The query generated was: "SELECT FROM" The query I was expecting: select catpersist0_.CAT_ID as CAT_ID, catpersist0_.NAME as NAME0_, catpersist0_.sex as sex0_, catpersist0_.weight as weight0_ from Cat catpersist0_ The problem is hibernate is not able to pick up the Persister class for the dynamic class mentioned in the mapping xml. Though the HashMap of Persisters class Mapping are well populated in the SessionFactoryImpl class constructor, there seems to be a problem while looking up or while retrieving persister class object from the same Map object. The look up process in my scenario starts from the getEntityPersisterUsingImports (String className)' method of the QueryTranslatorImpl class. While building the Persisters class mapping inside the SessionFactoryImpl class , entity name, used for the dynamic class element in the mapping XML is used as the key and an instance of the persister class is used as the value for the Map entry. While looking up the Persister class object for a given entity name from the FromParser.java 's token() method, FromParser uses getEntityPersisterUsingImports(token) method of the QueryTranslatorImpl.java class. The getEntityPersisterUsingImports method in the QueryTranslatorImpl.java class, in turn retrieves the the actual java Map implementation class name for the given entity-name from the mapping XML defined in the import element and uses the java Map class name as a parameter to the method, getEntityPersister(String) method. Which causes the SessionFactoryImpl class to return 'null' instead of a valid Persister class object. Since the 'classPersistersByName' Map object contain entity-name as the key, while the incoming parameter is a Map implementation class name to which entity-name maps to. This leads to incomplete generation of SQL in hibernate. Following is the patch that i applied in the org.hibernate.hql.QueryTranslatorImpl.java ****************************************************** Queryable getEntityPersisterUsingImports(String className) { final String importedClassName = factory.getImportedClassName(className); if (importedClassName==null) return null; try { //return ( Queryable ) factory.getEntityPersister(importedClassName); //### This is the buggy line ### return ( Queryable ) factory.getEntityPersister(className); } catch (MappingException me) { return null; } } ************************************************************* After the fix i ran few examples and some of my own programs which were previoulsy running/not-running both seem to work fine. I am surely interested to know about it. Please respond to the post or email to me at madhusudanmk@yahoo.com. Cheers, Madhusudan M Krishnamurthy Hibernate version:3.0 Mapping documents: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping auto-import="false"> <import class="java.util.Map" rename="CatPersistentMap"/> <dynamic-class entity-name="CatPersistentMap" table="Cat" discriminator-value="C" persister="org.hibernate.persister.SingleTableEntityPersister"> <id name="id" type="string" unsaved-value="null" > <column name="CAT_ID" sql-type="char(32)" not-null="true"/> <generator class="uuid.hex"/> </id> <property name="name" type="java.lang.String"> <column name="NAME" length="16" not-null="true"/> </property> <property name="sex" type="char"/> <property name="weight" type="float" /> </dynamic-class> </hibernate-mapping> Code between sessionFactory.openSession() and session.close(): transaction = session.beginTransaction(); String hqlQuery = "from CatPersistentMap catPersistentMap"; Query query = session.createQuery(hqlQuery); System.out.println("SQL :: " + query.getQueryString()); List catList = query.list(); if (catList != null && !catList.isEmpty()) { Iterator catIterator = catList.iterator(); while (catIterator.hasNext()) { Map catMap = (Map) catIterator.next(); System.out.println("Cat Map :: " + catMap); } } else System.out.println("Empty records"); transaction.commit(); Full stack trace of any exception that occurs: java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) SEVERE: Could not execute query java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) Name and version of the database you are using: Oracle 9i The generated SQL (show_sql=true): select from

    Hibernate JIRA | 1 decade ago | Madhusudan M Krishnamurthy
    java.sql.SQLException: ORA-00936: missing expression
  2. 0

    After roughly a week and half of painful debugging, I figured out the problem with hibernate generating only a "PARTIAL QUERY" for the below mentioned code. (Right at the end of this post) The query generated was: "SELECT FROM" The query I was expecting: select catpersist0_.CAT_ID as CAT_ID, catpersist0_.NAME as NAME0_, catpersist0_.sex as sex0_, catpersist0_.weight as weight0_ from Cat catpersist0_ The problem is hibernate is not able to pick up the Persister class for the dynamic class mentioned in the mapping xml. Though the HashMap of Persisters class Mapping are well populated in the SessionFactoryImpl class constructor, there seems to be a problem while looking up or while retrieving persister class object from the same Map object. The look up process in my scenario starts from the getEntityPersisterUsingImports (String className)' method of the QueryTranslatorImpl class. While building the Persisters class mapping inside the SessionFactoryImpl class , entity name, used for the dynamic class element in the mapping XML is used as the key and an instance of the persister class is used as the value for the Map entry. While looking up the Persister class object for a given entity name from the FromParser.java 's token() method, FromParser uses getEntityPersisterUsingImports(token) method of the QueryTranslatorImpl.java class. The getEntityPersisterUsingImports method in the QueryTranslatorImpl.java class, in turn retrieves the the actual java Map implementation class name for the given entity-name from the mapping XML defined in the import element and uses the java Map class name as a parameter to the method, getEntityPersister(String) method. Which causes the SessionFactoryImpl class to return 'null' instead of a valid Persister class object. Since the 'classPersistersByName' Map object contain entity-name as the key, while the incoming parameter is a Map implementation class name to which entity-name maps to. This leads to incomplete generation of SQL in hibernate. Following is the patch that i applied in the org.hibernate.hql.QueryTranslatorImpl.java ****************************************************** Queryable getEntityPersisterUsingImports(String className) { final String importedClassName = factory.getImportedClassName(className); if (importedClassName==null) return null; try { //return ( Queryable ) factory.getEntityPersister(importedClassName); //### This is the buggy line ### return ( Queryable ) factory.getEntityPersister(className); } catch (MappingException me) { return null; } } ************************************************************* After the fix i ran few examples and some of my own programs which were previoulsy running/not-running both seem to work fine. I am surely interested to know about it. Please respond to the post or email to me at madhusudanmk@yahoo.com. Cheers, Madhusudan M Krishnamurthy Hibernate version:3.0 Mapping documents: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping auto-import="false"> <import class="java.util.Map" rename="CatPersistentMap"/> <dynamic-class entity-name="CatPersistentMap" table="Cat" discriminator-value="C" persister="org.hibernate.persister.SingleTableEntityPersister"> <id name="id" type="string" unsaved-value="null" > <column name="CAT_ID" sql-type="char(32)" not-null="true"/> <generator class="uuid.hex"/> </id> <property name="name" type="java.lang.String"> <column name="NAME" length="16" not-null="true"/> </property> <property name="sex" type="char"/> <property name="weight" type="float" /> </dynamic-class> </hibernate-mapping> Code between sessionFactory.openSession() and session.close(): transaction = session.beginTransaction(); String hqlQuery = "from CatPersistentMap catPersistentMap"; Query query = session.createQuery(hqlQuery); System.out.println("SQL :: " + query.getQueryString()); List catList = query.list(); if (catList != null && !catList.isEmpty()) { Iterator catIterator = catList.iterator(); while (catIterator.hasNext()) { Map catMap = (Map) catIterator.next(); System.out.println("Cat Map :: " + catMap); } } else System.out.println("Empty records"); transaction.commit(); Full stack trace of any exception that occurs: java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) SEVERE: Could not execute query java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) Name and version of the database you are using: Oracle 9i The generated SQL (show_sql=true): select from

    Hibernate JIRA | 1 decade ago | Madhusudan M Krishnamurthy
    java.sql.SQLException: ORA-00936: missing expression
  3. 0

    With this HQL: from Company comp left outer join comp.employees empl with empl.birthday > :date Produce following SQL: select company0_.id as id0_0_, employees1_.id as id1_1_, company0_.companyName as companyN2_0_0_, employees1_.name as name1_1_, employees1_.birthday as birthday1_1_, employees1_.companyId as companyId1_1_ from TB_COMPANY_TEMP company0_, TB_EMPLOYEE_TEMP employees1_ where company0_.id=employees1_.companyId(+) and (employees1_.birthday(+)>(+)=?) When it is run, the stacktrace is: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2148) 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 mytest.OuterJoinTest.main(OuterJoinTest.java:28) Caused by: java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2145) ... 8 more The correct where clause should be : where company0_.id=employees1_.companyId(+) and (employees1_.birthday(+)>=?) This error don't occur with MySQLDialect or SQLServerDialect. Probably it considers >= as two operators instead of a single operator.

    Hibernate JIRA | 1 decade ago | Marcelo Oliveira
    org.hibernate.exception.SQLGrammarException: could not execute query
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Hibernate / Mailing Lists

    sourceforge.net | 8 months ago
    org.hibernate.exception.SQLGrammarException: could not execute query

    26 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. java.sql.SQLException

      ORA-00936: missing expression

      at oracle.jdbc.dbaccess.DBError.throwSqlException()
    2. oracle.jdbc.dbaccess
      DBError.throwSqlException
      1. oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      1 frame
    3. oracle.jdbc.ttc7
      TTC7Protocol.parseExecuteDescribe
      1. oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      2. oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
      3. oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
      4. oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
      4 frames
    4. Oracle jdbc
      OraclePreparedStatement.executeQuery
      1. oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
      2. oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
      3. oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
      4. oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
      4 frames
    5. Hibernate
      Loader.list
      1. org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96)
      2. org.hibernate.loader.Loader.getResultSet(Loader.java:1044)
      3. org.hibernate.loader.Loader.doQuery(Loader.java:321)
      4. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167)
      5. org.hibernate.loader.Loader.doList(Loader.java:1201)
      6. org.hibernate.loader.Loader.list(Loader.java:1186)
      6 frames
    6. Hibernate HQL/JP-QL Parser
      QueryTranslatorImpl.list
      1. org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872)
      1 frame
    7. Hibernate
      QueryImpl.list
      1. org.hibernate.impl.SessionImpl.find(SessionImpl.java:812)
      2. org.hibernate.impl.QueryImpl.list(QueryImpl.java:84)
      2 frames
    8. com.accelrys.platform
      TestCatQuery.main
      1. com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164)
      2. com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53)
      2 frames