Hibernate version:2.1

Description of the Problem:
The question is regarding configuring a datasource for Oracle 9i in Websphere 5.1 for J2EE application.
I am using hibernate 2.1 to access the db. Reads are working fine. When I try to insert a record I get ORA-01002: fetch out of sequence. After research I am narrowing the problem to the fact that somehow I need to disable AutoCommit so I can use explicit transaction starts and commits in my code (this is what I am doing in hibernate). I believe in Oracle AutoCommit cannot be globally set to false. It is session or connection based?

The same code works fine in Tomcat (autoCommit is set to false in data source config - server.xml file)

Can someone please tell me - how do I set AutoCommit to false in Websphere for the data source? I tried to set it using hibernate before start and finish of each transaction by getting a handle to the connection and it did not work? I am using Oracle thin driver for JDK 1.4 ojdbc14.jar.

I would appreciate any help as this is a criticial hurdle that I need to cross!!

Full stack trace of any exception that occurs:
TRAS0014I: The following exception was logged java.sql.SQLException: ORA-01002: fetch out of sequence 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:573) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:955) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2520) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527) at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:426) at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:93) at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59) at n -- <truncated> --.

Name and version of the database you are using:Oracle 9i

My hibernate.cfg.xml file is as follows:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>

<!-- a SessionFactory instance listed as /jndi/name -->
<session-factory name="hibernate/sessionFactory">
<property name="show_sql">true</property>
<property name="use_outer_join">true</property>
<property name="jta.UserTransaction">
java:comp/UserTransaction/
</property>

<!-- properties for this SessionFactory only -->
<!-- <property name="connection.datasource">java:comp/env/jdbc/cdb</property> -->
<property name="connection.datasource">jdbc/cdb</property>
<property name="dialect">net.sf.hibernate.dialect.Oracle9Dialect</property>
<!-- <property name="connection.provider_class">net.sf.hibernate.websphere.WSDataSourceConnectionProvider</property> -->

<mapping resource="data/mapping/hibernate/user.hbm.xml" />

</session-factory>
</hibernate-configuration>

I presume you are using the "thin" jdbc driver from oracle. If so, when you specify the datasource you should be able to provide the following params. I am providing a sample subset
<username>scott</username>
<password>tiger</password>
<dburl>jdbc:oracle:thin:@localhost:1521:testDB</dburl>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<autocommit>true</autocommit>

If the Datasource factory (the one that is specified in the datasource config), ignores the param then you can write your own datasource factory. Now you control the connections creation. It is at this point of time you can explicitly make the autocommit false for every connection that gets created.

G

I have specified datasource using admin console of websphere for Oracle9i. How and where do I specify the parameter autommit to be set to false in websphere configuration of datasource? In websphere the xml files are generated behind the scene...

My application is Non EJB application without CMP. I want to manage all transactions using hibernate.

I changed my hibernator generator class from HiLo to sequence. I also created in Oracle 9i a sequence object called uid_sequence that I specify in my hibernate mapping file (see below):

<hibernate-mapping>
<class name="model.vo.User" table="user_tbl" >

<id name="oid" type="long" column="user_id" unsaved-value="0" >
<generator class="sequence">
<param name="sequence">uid_sequence</param>
</generator>
</id>
<!-- Class variables mapping with table columns -->
<property name="title" column="user_title" type="string"/>
<property name="firstName" column="user_first_name" type="string"/>
<property name="lastName" column="user_last_name" type="string"/>
<property name="email" column="user_email" type="string"/>
<property name="birthDate" column="user_birth_date" type="string"/>
</class>
</hibernate-mapping>

Now I am able to insert however, but I get an exception from WAS as follows:

javax.servlet.ServletException: LocalTransaction rolled-back due to setRollbackOnly
at
com.ibm.ws.webcontainer.webapp.WebAppTransactionCollaborator.postInvok (WebAppTransactionCollaborator.java:224)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:671)
...

It is accompanied by:

WLTC0033E: Resource {0} rolled back in cleanup of unresolved LocalTransactionContainment.

and:

WLTC0032E: One or more resources rolled back. An unresolved LocalTransactionContainment had an unresolved action of rollback

Questions:

1) Why is WAS trying to rollback transaction?
2) How can I disable this so I have full control of transaction?
3) Has anyone deployed a simple webapp (Non-EJB) app using hibernate, WAS, and Oracle? If so please send me you hibernate.cfg.xml and mapping files or atleast guide me...

Thanks,

Setting autocommit ON or OFF is possible in Database itself. Check your database settings.

In your SQL Plus run show all and check the autocommit settings. If the autocommit is ON

use the command set autocommit OFF to disable the same.

Also another observation, the following property should be:
<property name="jta.UserTransaction">
java:comp/UserTransaction/
</property>


CORRECT VERSION:
<property name="jta.UserTransaction">
java:comp/UserTransaction</property>

The final slash(/) is unnecessary.

Hope this solves your problems.