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
Login
FAQ
Search
