websphere 5.0 transaction rollback help

[LottoMonkey]

I am new to both websphere and ibatis and have been trying to use both to set up a new webapp at my job.

I have tried searching through the forums here and have tried follwing the suggestions to get websphere and ibatis connecting to my our oracle database.

But i have no idea how to commit transactions, everything i do gets rolled back by websphere. This may be because i simply don't understand websphere, but any help would be nice.

here is the connection string from my sql-map-config.xml. I had tried using the "JDBC" transaction manager but i couldn't get that to work.
<transactionManager type="JTA">
  <property name="UserTransaction" value="java:comp/UserTransaction" />
  <dataSource type="JNDI">
    <property name="DataSource" value="jdbc/ORACLE" />
  </dataSource>
</transactionManager>

I am using the dao maps as shown in the petstore example and the code:
daoManager.startTransaction();
admissionFormDao.insertAdmissionForm(admissionForm);
daoManager.commitTransaction();
daoManager.endTransaction();

I looks like this should commit the silly transaction but the log in websphere always shows a rollback and nothing can been seen in the db using toad.

[tbourdon]

You may want to make sure the jdbc driver you're using for Oracle supports global transactions. Also, with WAS your DataSource value needs to match exactly with the JNDI name you defined. If you have a Server project setup in WAS you can check there in the DataSource tab. If you don't have a Server project I believe you can set the JNDI name via the admin console.

- Troy

[LottoMonkey]

I am using a oracle 8i database and am using the driver located here:
http://download.oracle.com/otn/utilities_drivers/jdbc/8171/classes12.zip
And I am not sure how you tell if it supports global transactions or not.

I am just using a local server on wsad right now and I think that the data sources are set up properly, At least I am able to do selects on my tables and get results back but websphere also complains about having to rollback the transaction there too.

[tbourdon]

It sounds like your JNDI name is set up correctly if queries are working. The rollback message you get with the queries has been covered in other threads of this group. The upshot is, WAS will throw that rollback exception if you do not perform a commit after a query. This is just the way WAS does things.

Without trying to implement your architecture for you I can tell you a little bit about our implementation.

We only use the iBATIS DAO framework as a Factory to allow us to map interfaces to implementations. We do not use it at all for transaction management. So in our DaoConfig file we just have:

<transactionManager type="EXTERNAL">
</transactionManager>

Of course if you want to write your own factories you would not need the DAO framework at all.

Now in the SqlMapConfig file we have the following for TX management.

<transactionManager type="EXTERNAL">
<property name="SetAutoCommitAllowed" value="false"/>
<dataSource type="JNDI">
<property name="DataSource" value="${JNDIDataSource}"/>
</dataSource>       
</transactionManager>

Now you might ask, well how are transactions managed? Well we manage all transactions in our business layer which is completely separate from our data layer. In our business layer we just make the neccessary JNDI call to get a UserTransaction handle. At that point we just surround whatever data layer calls we make with the appropriate UT call.

We are using WAS 5.1 with Sybase 12.5 and the described architecture has worked well for us.

- Troy

[John Harby]

This is what we are doing below and it works. We had some advice from Clinton to use the commitRequired=true part for Websphere. Also, be sure you are using the XA driver.

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig
    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
   
<sqlMapConfig>
    <!-- These settings control SqlMap configuration details, primarily to do with transaction
        management. They are all optional (see the Developer Guide for more). -->
    <settings cacheModelsEnabled="false"
              enhancementEnabled="true"
              lazyLoadingEnabled="false"
              maxRequests="2"
              maxSessions="1"
              maxTransactions="5"
              useStatementNamespaces="false" />

    <!-- Configure a datasource to use with this SQL Map using SimpleDataSource.
        Notice the use of the properties from the above resource -->
    <transactionManager type="JTA" commitRequired="true">
        <property name="UserTransaction" value="java:comp/env/UserTransaction" />
        <dataSource type="JNDI">
            <property name="DataSource" value="jdbc/orads" />
        </dataSource>
    </transactionManager>   
    <sqlMap resource="Resource.xml"/>
               ...
</sqlMapConfig>

[John Harby]

One other thing - this commitRequired attribute is only available in Ibatis 2.0.8.