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

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
Scorlóg
 Post subject: HQL Query problems
PostPosted: Tue Feb 08, 2005 8:03 am 
Beginner
Beginner

Joined: Tue Feb 01, 2005 8:38 am
Posts: 38
Hi,

I'm experiencing a very frustrating HQL query problem. I cannot execute even the simplest of queries in hibernate using the session.createQuery() method. I have been able to retrieve objects using the session.load() method but this is no good to me when I want to do more complicated lookups.

I'm following the documentataion distributed with Hibernate 3.0 and the query I'm trying to execute couldn't be more straight forward...

List result = session.createQuery("from DEVICE_CATEGORY").list();

...however I get this exception when it executes (including some debug)...

DEBUG - opened session
DEBUG - begin
DEBUG - opening JDBC connection
DEBUG - total checked-out connections: 0
DEBUG - using pooled JDBC connection, pool size: 0
DEBUG - current autocommit status: false
DEBUG - find: from DEVICE_CATEGORY
DEBUG - named parameters: {}
DEBUG - compiling query
DEBUG - HQL: from DEVICE_CATEGORY
DEBUG - SQL: select from
DEBUG - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG - select from
DEBUG - preparing statement
DEBUG - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG - closing statement
DEBUG - could not execute query [select from]
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:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1360)
at org.hibernate.loader.Loader.list(Loader.java:1343)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:876)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:974)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at HibernateTest.listDeviceCategories(HibernateTest.java:84)
at HibernateTest.main(HibernateTest.java:39)
WARN - SQL Error: 936, SQLState: 42000
ERROR - ORA-00936: missing expression

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1363)
at org.hibernate.loader.Loader.list(Loader.java:1343)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:876)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:974)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at HibernateTest.listDeviceCategories(HibernateTest.java:84)
at HibernateTest.main(HibernateTest.java:39)
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:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1360)
... 6 more


Note in the debug where it transformes HQL to SQL

DEBUG - HQL: from DEVICE_CATEGORY
DEBUG - SQL: select from

It drops the table name for some reason in the SQL.


...also, if I try to run a query with the 'as' keyword I get this...

List result = session.createQuery("from DEVICE_CATEGORY as dc").list();


DEBUG - find: from DEVICE_CATEGORY as dc
DEBUG - named parameters: {}
DEBUG - compiling query
org.hibernate.QueryException: unexpected token: as [from DEVICE_CATEGORY as dc]
at org.hibernate.hql.classic.FromParser.token(FromParser.java:98)
at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:86)
at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:29)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:172)
at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:148)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:410)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:988)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:965)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at HibernateTest.listDeviceCategories(HibernateTest.java:84)
at HibernateTest.main(HibernateTest.java:39)


...furthermore, I see in the 3.0 documentation a few refernces to session.find() and session.createSQLQuery() but my session object (in hibernate3.jar) has no find() or createSQLQuery methods!



Here's the full details....

Hibernate version: 3.0

Mapping documents:

Code:
<?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-generated mapping file from
        the hibernate.org cfg2hbm engine
-->
  <class name="DeviceCategory" table="DEVICE_CATEGORY" catalog="">
    <id name="DeviceCategoryId" type="java.lang.Integer">
      <column name="DEVICE_CATEGORY_ID" length="11" not-null="true" sql-type="int" />
      <generator class="assigned" />
    </id>
    <property name="NetworkFrequency" type="java.lang.String">
      <column name="NETWORK_FREQUENCY" length="100" not-null="true" sql-type="varchar" />
    </property>
    <property name="Description" type="java.lang.String">
      <column name="DESCRIPTION" not-null="true" sql-type="varchar" />
    </property>
    <set name="SetOfRoamingPartner">
      <key>
        <column name="MINIMUM_DEVICE_REQUIREMENT_ID" length="11" not-null="false" unique="true" />
      </key>
      <one-to-many class="RoamingPartner" />
    </set>
  </class>
</hibernate-mapping>



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

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
List result = session.createQuery("from DEVICE_CATEGORY").list();
tx.commit();
session.close();

Full stack trace of any exception that occurs:

DEBUG - opened session
DEBUG - begin
DEBUG - opening JDBC connection
DEBUG - total checked-out connections: 0
DEBUG - using pooled JDBC connection, pool size: 0
DEBUG - current autocommit status: false
DEBUG - find: from DEVICE_CATEGORY
DEBUG - named parameters: {}
DEBUG - compiling query
DEBUG - HQL: from DEVICE_CATEGORY
DEBUG - SQL: select from
DEBUG - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG - select from
DEBUG - preparing statement
DEBUG - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG - closing statement
DEBUG - could not execute query [select from]
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:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1360)
at org.hibernate.loader.Loader.list(Loader.java:1343)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:876)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:974)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at HibernateTest.listDeviceCategories(HibernateTest.java:84)
at HibernateTest.main(HibernateTest.java:39)
WARN - SQL Error: 936, SQLState: 42000
ERROR - ORA-00936: missing expression

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1363)
at org.hibernate.loader.Loader.list(Loader.java:1343)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:876)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:974)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at HibernateTest.listDeviceCategories(HibernateTest.java:84)
at HibernateTest.main(HibernateTest.java:39)
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:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1360)
... 6 more


Any help on this would be greatly appreciated.

John :)
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Feb 08, 2005 8:17 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
You don't have a class named "DEVICE_CATEGORY"
Top
 Profile  
 
Scorlóg
 Post subject:
PostPosted: Tue Feb 08, 2005 8:22 am 
Beginner
Beginner

Joined: Tue Feb 01, 2005 8:38 am
Posts: 38
gavin wrote:
You don't have a class named "DEVICE_CATEGORY"


That's true but I have one called DeviceCategory. All my hbm.xml files and corresponding POJO classes were generated using the eclipse plugin tool.
Top
 Profile  
 
Scorlóg
 Post subject:
PostPosted: Tue Feb 08, 2005 8:24 am 
Beginner
Beginner

Joined: Tue Feb 01, 2005 8:38 am
Posts: 38
Does this not create the mapping from the calls to the table?

<class name="DeviceCategory" table="DEVICE_CATEGORY">
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Feb 08, 2005 8:26 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Scorlóg wrote:
gavin wrote:
You don't have a class named "DEVICE_CATEGORY"


That's true


So .... why does your query name a class called DEVICE_CATEGORY?

Please read the HQL reference documentation again.
Top
 Profile  
 
Scorlóg
 Post subject:
PostPosted: Tue Feb 08, 2005 8:31 am 
Beginner
Beginner

Joined: Tue Feb 01, 2005 8:38 am
Posts: 38
gavin wrote:
So .... why does your query name a class called DEVICE_CATEGORY?

Please read the HQL reference documentation again.


Thanks Gavin - I see what I was doing wrong now. To be fair though, the documentation could point out a little more clearly (i.e. point out at all!)that you refer to the object name and not the table name in your query.

Cheers :)
Top
 Profile  
 
Kops
 Post subject:
PostPosted: Wed May 24, 2006 3:53 pm 
Newbie

Joined: Wed May 24, 2006 3:46 pm
Posts: 1
You will get exactly the same exception if you forget to add the mapping of relevant class in hibernate.cfg.xml like i did :-)

Also if anyone happens to make this rather avoidable mistake, then the exception on screen will not print the SQL and he will have to google all the way to this post like i did ;-)

cheers,
Kops
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 

Board index » Projects » Search, Validator, Shards

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.