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

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Different HQL behaviour with Oracle9 and MySql
PostPosted: Wed Apr 19, 2006 11:43 am 
Newbie

Joined: Wed Apr 19, 2006 10:45 am
Posts: 2
Location: Zurich, Switzerland
Hello everybody,

I have been using Hibernate 3 for a short time so I am not very experienced. I am actually using it with Spring but I don't think it has anything to do with my problem. Here it is :

I have a very simple query that runs on MySql and not on Oracle 9. And I have to go live with Oracle....

Here is the case:

My SQL schema creation:
Code:
CREATE TABLE roles (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
);
INSERT INTO ROLES VALUES(1,'CC_SUPER_USER');
INSERT INTO ROLES VALUES(2,'CC_CONTENT_PROVIDER');
INSERT INTO ROLES VALUES(3,'CC_CONTENT_APPROVER');
INSERT INTO ROLES VALUES(4,'BL_SUPER_USER');
INSERT INTO ROLES VALUES(5,'BL_DATA_LOADER');
INSERT INTO ROLES VALUES(6,'BL_VIEWER');


Oracle schema creation:
Code:
create table roles (
      id int not null,
       name varchar2(20) not null,
      constraint pk_roles primary key (id)
);
insert into roles values(1,'CC_SUPER_USER');
insert into roles values(2,'CC_CONTENT_PROVIDER');
insert into roles values(3,'CC_CONTENT_APPROVER');
insert into roles values(4,'BL_SUPER_USER');
insert into roles values(5,'BL_DATA_LOADER');
insert into roles values(6,'BL_VIEWER');


Somwhere I have :
Code:
   public Collection<Role> getAllRoles() throws DataAccessException {
      return getHibernateTemplate().find("from Role role");
   }


The hbm.xml :

Code:
<hibernate-mapping auto-import="true" default-lazy="false">   
   <class name="com.cs.cts.billing.domain.Role" table="roles">
      <id name="id" column="id">
         <generator class="increment"/>         
      </id>
      <property name="name" column="name"/>
   </class>
</hibernate-mapping>


When I run this (by calling getAllRoles()) with MySql I get 6 rows (which is normal). With Oracle, I get nothing and the log does not complain, it just tells that there was nothing to retrieve:
Code:
2006-04-19 17:27:13,209 [main] DEBUG org.hibernate.impl.SessionImpl - opened session at timestamp: 4691805934104576
2006-04-19 17:27:13,241 [main] DEBUG org.hibernate.impl.SessionImpl - find: from Role role
2006-04-19 17:27:13,241 [main] DEBUG org.hibernate.engine.QueryParameters - named parameters: {}
2006-04-19 17:27:13,444 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - parse() - HQL: from com.cs.cts.billing.domain.Role role
2006-04-19 17:27:13,459 [main] DEBUG org.hibernate.hql.ast.AST - --- HQL AST ---
\-[QUERY] 'query'
    \-[SELECT_FROM] 'SELECT_FROM'
       \-[FROM] 'from'
          \-[RANGE] 'RANGE'
             +-[DOT] '.'
             |  +-[DOT] '.'
             |  |  +-[DOT] '.'
             |  |  |  +-[DOT] '.'
             |  |  |  |  +-[DOT] '.'
             |  |  |  |  |  +-[IDENT] 'com'
             |  |  |  |  |  \-[IDENT] 'cs'
             |  |  |  |  \-[IDENT] 'cts'
             |  |  |  \-[IDENT] 'billing'
             |  |  \-[IDENT] 'domain'
             |  \-[IDENT] 'Role'
             \-[ALIAS] 'role'

2006-04-19 17:27:13,459 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
2006-04-19 17:27:13,569 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() << begin, level = 1
2006-04-19 17:27:13,631 [main] DEBUG org.hibernate.hql.ast.FromElement - FromClause{level=1} :  com.cs.cts.billing.domain.Role (role) -> role0_
2006-04-19 17:27:13,631 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() : finishing up , level = 1
2006-04-19 17:27:13,631 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - processQuery() :  ( SELECT ( FromClause{level=1} roles role0_ ) )
2006-04-19 17:27:13,647 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - Derived SELECT clause created.
2006-04-19 17:27:13,678 [main] DEBUG org.hibernate.hql.ast.JoinProcessor - Using FROM fragment [roles role0_]
2006-04-19 17:27:13,678 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - query() >> end, level = 1
2006-04-19 17:27:13,678 [main] DEBUG org.hibernate.hql.ast.AST - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (roles)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'role0_.id as id' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=role,role=null,tableName=roles,tableAlias=role0_,colums={,className=com.cs.cts.billing.domain.Role}}}
    |  \-[SQL_TOKEN] SqlFragment: 'role0_.name as name2_'
    \-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[role], fromElementByTableAlias=[role0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
       \-[FROM_FRAGMENT] FromElement: 'roles role0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=role,role=null,tableName=roles,tableAlias=role0_,colums={,className=com.cs.cts.billing.domain.Role}}

2006-04-19 17:27:13,678 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
2006-04-19 17:27:13,709 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - HQL: from com.cs.cts.billing.domain.Role role
2006-04-19 17:27:13,709 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select role0_.id as id, role0_.name as name2_ from roles role0_
2006-04-19 17:27:13,709 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
2006-04-19 17:27:13,725 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-19 17:27:13,725 [main] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
2006-04-19 17:27:13,725 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC Connection to [jdbc:oracle:thin:@zus15d-3801.csfp.co.uk:1526:TZHBIL01]
2006-04-19 17:27:13,866 [main] DEBUG org.hibernate.SQL - select role0_.id as id, role0_.name as name2_ from roles role0_
Hibernate: select role0_.id as id, role0_.name as name2_ from roles role0_
2006-04-19 17:27:13,866 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
2006-04-19 17:27:13,912 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.loader.Loader - processing result set
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.loader.Loader - done processing result set (0 rows)
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.loader.Loader - total objects hydrated: 0
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.engine.PersistenceContext - initializing non-lazy collections
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.jdbc.JDBCContext - after autocommit
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.impl.SessionImpl - after transaction completion
2006-04-19 17:27:13,928 [main] DEBUG org.springframework.orm.hibernate3.HibernateTemplate - Eagerly flushing Hibernate session
2006-04-19 17:27:13,928 [main] DEBUG org.springframework.orm.hibernate3.SessionFactoryUtils - Closing Hibernate Session
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.impl.SessionImpl - closing session
2006-04-19 17:27:13,928 [main] DEBUG org.hibernate.jdbc.ConnectionManager - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2006-04-19 17:27:13,944 [main] DEBUG org.hibernate.jdbc.JDBCContext - after transaction completion


Would anybody have an idea or help? I don't really have a clue....

Please ask me if you need more details about my config

Hope somebody will answer!

Thanks in advance

Regards

Christophe


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 19, 2006 11:59 am 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
If you issue
Code:
select role0_.id as id, role0_.name as name2_ from roles role0_

from an Oracle SQL console or an Oracle query browser or something like ...

What do you get?


Top
 Profile  
 
 Post subject: Commit......
PostPosted: Wed Apr 19, 2006 12:06 pm 
Newbie

Joined: Wed Apr 19, 2006 10:45 am
Posts: 2
Location: Zurich, Switzerland
Sorry about the annoyance. I found the problem myself. I am a bit shameful to say that it's because there was not f...... commit after the oracle script......

Sorry for the inconvenience and thank you pepelnm for your time.


Best regards

Christophe


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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.