-->
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 » Hibernate & Java Persistence » Hibernate Users

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
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
sam74
 Post subject: Hibernate problem with Oracle
PostPosted: Fri Nov 04, 2005 11:05 am 
Newbie

Joined: Wed Nov 02, 2005 2:03 pm
Posts: 15
I am using Hibernate 3 and Oracle 9 i.
When I am trying to run my hibernate queries against Hypersonic database it works fine with all relationships.

But when running these with Oracle I am getting various exceptions.

Following are the properties I am using in hibernate.cfg.xml file

Code:
<property name="connection.url">jdbc:oracle:thin:@database url</property>
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.username">usrname</property>
        <property name="connection.password">password</property>

        <property name="dialect">org.hibernate.dialect.OracleDialect</property>

        <!-- Show all SQL DML executed by Hibernate -->
        <property name="show_sql">true</property>

        <!-- Switch to built-in cache provider -->
        <property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>



I also tried Oracle9Dialect but does'nt worked.

Does anybody has any idea what might be wrong.

I will appriciate your help.

Thanks.
Top
 Profile  
 
matt_mcgill
 Post subject:
PostPosted: Fri Nov 04, 2005 11:09 am 
Beginner
Beginner

Joined: Wed Jul 13, 2005 2:18 pm
Posts: 44
Can you post the relevant stack traces / mappings / HQL queries / code making use of a Session? I'm curently employed on a project involving Hibernate and a legacy ERP running on Oracle 9i and I haven't had any problems such as you describe.

_________________
- Matt

Top
 Profile  
 
sam74
 Post subject:
PostPosted: Fri Nov 04, 2005 11:19 am 
Newbie

Joined: Wed Nov 02, 2005 2:03 pm
Posts: 15
I am using hibernate 3 and Oracle 9i.
I am having two Objects Patient and Entry.They are having one to one bidirectional
relationship.

Mappings:

Code:
<class name="Patient" table="PATIENT">
   <id name="id"
      type="long"
      column="PATIENT_ID"
      access="field">
      <generator class="native"/>
   </id>

<one-to-one name="entry"
      class="Entry"
   property-ref="patient"/>   
</class>




<class name="Entry" table="ENTRY">
   <id name="id"
      type="long"
      column="WORKID"
      access="field">
      <generator class="native"/>
   </id>

<many-to-one name="patient"
         class="Patient"
         column="PATIENT_ID"
         unique="true"
         not-null="true"/>

</class>



Following are the properties I am using in hibernate.cfg.xml file.

Code:
<property name="connection.url">url</property>
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.username"></property>
        <property name="connection.password"></property>

        <property name="dialect">org.hibernate.dialect.OracleDialect</property>

        <!-- Show all SQL DML executed by Hibernate -->
        <property name="show_sql">true</property>
               

        <!-- Switch to built-in cache provider -->
        <property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>


I am having Patient object(Table) loaded and not having Entry table loaded.
When I am querying the patient object like

Code:
Query q3 = session.createQuery(
             "from Patient p where p.number =:nbr "
                    );   
                    q3.setParameter("nbr", "11453491"); 
                   
                   // List qlist3 = q3.list();
                 List qlist3 = (List) q3.uniqueResult();
                 System.out.println("NAME IS " +qlist3);
                    //Patient p1 = (Patient)qlist3.get(0); 
                    //String name1= p1.getName();
                    //System.out.println("NAME IS " +name1);
              
               //p = new Patient(nonzerocpi,name);
               // session.save(p);
                 tx.commit();
                 session.close();



I am getting two hibernate queries on console one is
Select from Patient...
other one is

Select from Entry....

and as result of second query I am getting exception that Entry could not be loaded.
I do'nt know why it is trying to load Entry when I am just querying for
one object i.e Patient.


Stack Trace:

Code:
Hibernate: select patient0_.PATIENT_ID as PATIENT1_0_, patient0_.VERSION as VERSION0_, patient0_.PATIENT_NAME as PATIENT3_0_, patient0_.CPI_NUMBER as CPI4_0_, patient0_.CREATED as CREATED0_, patient0_.LAST_APPOINTMENT_ID as LAST6_0_ from PATIENT patient0_ where patient0_.CPI_NUMBER=?
Hibernate: select entry0_.WORKITEM_ID as WORKITEM1_4_0_, entry0_.VERSION as VERSION4_0_, entry0_.PATIENT_NAME as PATIENT3_4_0_, entry0_.CPI_NUMBER as CPI4_4_0_, entry0_.BEGIN_DATE as BEGIN5_4_0_, entry0_.CREATED as CREATED4_0_, entry0_.STATUS as STATUS4_0_, entry0_.USER_ID as USER8_4_0_, entry0_.SUSPEND_BY as SUSPEND9_4_0_, entry0_.FORWARD_BY as FORWARD10_4_0_, entry0_.SUSPEND_DATE as SUSPEND11_4_0_, entry0_.QUEUE_ID as QUEUE12_4_0_, entry0_.PATIENT_ID as PATIENT13_4_0_ from TBW_ENTRY entry0_ where entry0_.PATIENT_ID=?
10:17:55,161  WARN JDBCExceptionReporter:71 - SQL Error: 904, SQLState: 42000
10:17:55,161 ERROR JDBCExceptionReporter:72 - ORA-00904: "ENTRY0_"."PATIENT_ID": invalid identifier

org.hibernate.exception.SQLGrammarException: could not load an entity: [edu.umich.med.pbs.domain.model.Entry#194]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1796)
   at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
   at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:85)
   at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1512)
   at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:365)
   at org.hibernate.type.EntityType.resolve(EntityType.java:306)
   at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:113)
   at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
   at org.hibernate.loader.Loader.doQuery(Loader.java:717)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
   at org.hibernate.loader.Loader.doList(Loader.java:2147)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
   at org.hibernate.loader.Loader.list(Loader.java:2021)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:992)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
   at edu.umich.med.pbs.dataload.hibernate.TestLoad.loadOTISData(TestLoad.java:92)
   at edu.umich.med.pbs.dataload.hibernate.TestLoad.main(TestLoad.java:43)
Caused by: java.sql.SQLException: ORA-00904: "ENTRY0_"."PATIENT_ID": invalid identifier

   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:137)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1782)
   ... 19 more



I will appriciate if you have any idea about this.

Thanks.
Top
 Profile  
 
matt_mcgill
 Post subject:
PostPosted: Fri Nov 04, 2005 12:07 pm 
Beginner
Beginner

Joined: Wed Jul 13, 2005 2:18 pm
Posts: 44
Try not to doctor up your mappings / code *too* much before posting. I know you're trying to make things clearer, but it could cause confusion. Your HQL query references a 'number' property on Patient, which your mapping does not include. I presume that it's there, but you removed it in your post.

As to why Hibernate's loading the associated Entity right off the bat, I'm not sure. But the root cause of that exception is the Oracle driver telling you that table TBW_ENTRY does not have a column named PATIENT_ID on it. Take a look at your schema again, and post it here if you can't figure it out.

_________________
- Matt

Top
 Profile  
 
sam74
 Post subject:
PostPosted: Fri Nov 04, 2005 12:09 pm 
Newbie

Joined: Wed Nov 02, 2005 2:03 pm
Posts: 15
I am writing both the queries from console.These looks same to me.

From Hypersonic: This works fine

Code:
Hibernate: select patient0_.PATIENT_ID as PATIENT1_0_1_, patient0_.VERSION as VERSION0_1_, patient0_.PATIENT_NAME as PATIENT3_0_1_, patient0_.CPI_NUMBER as CPI4_0_1_, patient0_.CREATED as CREATED0_1_, patient0_.LAST_APPOINTMENT_ID as LAST6_0_1_, entry1_.WORKITEM_ID as WORKITEM1_4_0_, entry1_.VERSION as VERSION4_0_, entry1_.PATIENT_NAME as PATIENT3_4_0_, entry1_.CPI_NUMBER as CPI4_4_0_, entry1_.BEGIN_DATE as BEGIN5_4_0_, entry1_.CREATED as CREATED4_0_, entry1_.STATUS as STATUS4_0_, entry1_.USER_ID as USER8_4_0_, entry1_.SUSPEND_BY as SUSPEND9_4_0_, entry1_.FORWARD_BY as FORWARD10_4_0_, entry1_.SUSPEND_DATE as SUSPEND11_4_0_, entry1_.QUEUE_ID as QUEUE12_4_0_, entry1_.PATIENT_ID as PATIENT13_4_0_ from PATIENT patient0_ left outer join TBW_ENTRY entry1_ on patient0_.PATIENT_ID=entry1_.PATIENT_ID where patient0_.PATIENT_ID=?


From oracle: This is giving the exception

Hibernate: select patient0_.PATIENT_ID as PATIENT1_0_1_, patient0_.VERSION as VERSION0_1_, patient0_.PATIENT_NAME as PATIENT3_0_1_, patient0_.CPI_NUMBER as CPI4_0_1_, patient0_.CREATED as CREATED0_1_, patient0_.LAST_APPOINTMENT_ID as LAST6_0_1_, entry1_.WORKITEM_ID as WORKITEM1_4_0_, entry1_.VERSION as VERSION4_0_, entry1_.PATIENT_NAME as PATIENT3_4_0_, entry1_.CPI_NUMBER as CPI4_4_0_, entry1_.BEGIN_DATE as BEGIN5_4_0_, entry1_.CREATED as CREATED4_0_, entry1_.STATUS as STATUS4_0_, entry1_.USER_ID as USER8_4_0_, entry1_.SUSPEND_BY as SUSPEND9_4_0_, entry1_.FORWARD_BY as FORWARD10_4_0_, entry1_.SUSPEND_DATE as SUSPEND11_4_0_, entry1_.QUEUE_ID as QUEUE12_4_0_, entry1_.PATIENT_ID as PATIENT13_4_0_ from PATIENT patient0_, TBW_ENTRY entry1_ where patient0_.PATIENT_ID=entry1_.PATIENT_ID(+) and patient0_.PATIENT_ID=?

Exception Trcae:

Hibernate: select patient0_.PATIENT_ID as PATIENT1_0_, patient0_.VERSION as VERSION0_, patient0_.PATIENT_NAME as PATIENT3_0_, patient0_.CPI_NUMBER as CPI4_0_, patient0_.CREATED as CREATED0_, patient0_.LAST_APPOINTMENT_ID as LAST6_0_ from PATIENT patient0_ where patient0_.CPI_NUMBER=?
Hibernate: select entry0_.WORKITEM_ID as WORKITEM1_4_0_, entry0_.VERSION as VERSION4_0_, entry0_.PATIENT_NAME as PATIENT3_4_0_, entry0_.CPI_NUMBER as CPI4_4_0_, entry0_.BEGIN_DATE as BEGIN5_4_0_, entry0_.CREATED as CREATED4_0_, entry0_.STATUS as STATUS4_0_, entry0_.USER_ID as USER8_4_0_, entry0_.SUSPEND_BY as SUSPEND9_4_0_, entry0_.FORWARD_BY as FORWARD10_4_0_, entry0_.SUSPEND_DATE as SUSPEND11_4_0_, entry0_.QUEUE_ID as QUEUE12_4_0_, entry0_.PATIENT_ID as PATIENT13_4_0_ from TBW_ENTRY entry0_ where entry0_.PATIENT_ID=?

Exception Trace:

Code:
11:07:49,288  WARN JDBCExceptionReporter:71 - SQL Error: 904, SQLState: 42000
11:07:49,288 ERROR JDBCExceptionReporter:72 - ORA-00904: "ENTRY0_"."PATIENT_ID": invalid identifier

org.hibernate.exception.SQLGrammarException: could not load an entity: [edu.umich.med.pbs.domain.model.Entry#218]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1796)
   at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
   at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:85)
   at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1512)
   at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:365)
   at org.hibernate.type.EntityType.resolve(EntityType.java:306)
   at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:113)
   at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
   at org.hibernate.loader.Loader.doQuery(Loader.java:717)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
   at org.hibernate.loader.Loader.doList(Loader.java:2147)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
   at org.hibernate.loader.Loader.list(Loader.java:2021)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:992)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
   at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
   at edu.umich.med.pbs.dataload.hibernate.TestLoad.loadOTISData(TestLoad.java:92)
   at edu.umich.med.pbs.dataload.hibernate.TestLoad.main(TestLoad.java:43)
Caused by: java.sql.SQLException: ORA-00904: "ENTRY0_"."PATIENT_ID": invalid identifier

   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:137)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
   at org.hibernate.loader.Loader.loadEntity(Loader.java:1782)
   ... 19 more
Top
 Profile  
 
matt_mcgill
 Post subject:
PostPosted: Fri Nov 04, 2005 12:29 pm 
Beginner
Beginner

Joined: Wed Jul 13, 2005 2:18 pm
Posts: 44
sam74 wrote:
I am writing both the queries from console.These looks same to me.

From Hypersonic: This works fine

(snip)

From oracle: This is giving the exception

(snip)

Exception Trace:

Code:
11:07:49,288  WARN JDBCExceptionReporter:71 - SQL Error: 904, SQLState: 42000
11:07:49,288 ERROR JDBCExceptionReporter:72 - ORA-00904: "ENTRY0_"."PATIENT_ID": invalid identifier
(snip)




Understood. What I am suggesting is that your schema in Oracle is different than your schema in Hypersonic - your Oracle table appears to be missing the PATIENT_ID column.

_________________
- Matt

Top
 Profile  
 
sam74
 Post subject:
PostPosted: Fri Nov 04, 2005 12:38 pm 
Newbie

Joined: Wed Nov 02, 2005 2:03 pm
Posts: 15
Thanks a lot.It is working fine now.
I really appriciate your help...
Top
 Profile  
 
matt_mcgill
 Post subject:
PostPosted: Fri Nov 04, 2005 1:04 pm 
Beginner
Beginner

Joined: Wed Jul 13, 2005 2:18 pm
Posts: 44
My pleasure =) Please don't forget to rate!

_________________
- Matt

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
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.