Transaction handling with oracle DB

[kumaravnish]

I have an application where the Stateless Session EJB (with Bean Managed Transaction) makes an insert in the database. The insert method called in the Session EJB, is implemented in the DAO object where it makes multiple inserts in relational tables. The EJB should rollback the transaction if any of these relational table inserts fail. The problem is that my code works fine through the rollback without exceptions, but the rollback is not reflected in the database. Hence it looks like each SQL is auto committed. Where am I going wrong? Any Ideas? Is there a property somewhere to be changed so that Autocommit is not done?

I am using JBoss 3.0.2 with Oracle 8i..

Here is a code snippet :

public RuleVO insert(RuleVO rule) {

UserTransaction ut = context.getUserTransaction();

DAOFactory daoFactory = DAOFactory.getDAOFactory();
Connection conn = null;

RuleVO savedRule = new RuleVO();
try {
ut.begin();
try {
conn = daoFactory.getConnection();
} catch (IllegalArgumentException ex) {
throw new EJBException(
"Could not establish database connection: " + ex.getMessage());
}
savedRule = daoFactory.getRuleDAO().insert(conn, rule);
ut.commit();
} catch (SQLException e) {
try {
ut.rollback();
} catch (SystemException syex) {
throw new EJBException
("Rule insert & rollback failed: " + syex.getMessage());
}
throw new EJBException
("Rule insert failed: " + e.getMessage());
} catch (Exception ex) {
throw new EJBException
("Rule insert begin/commit transaction failed: " + ex.getMessage());
} finally {
try {
DAOFactory.closeConnection(conn);
} catch (SQLException ex) {
throw new EJBException(
"Could not close database connection: " + ex.getMessage());
}
return savedRule;
}
}

Thanks in advance...

Avnish

[davidjencks]

I don't see anything that would cause this in your code snippet, although I recommend disposing of resources in the reverse order you obtain them, and with one finally block per resource (i.e, close the connection before you end the tx).

I assume you are getting the connection from a LocalTx datasource each time you call getConnection on the daoFactory. If you are using an xa setup please use jboss 3.2 and the oracle specific wrapper. If you are using NoTx.... that is where the problem is.

I would turn logging for org.jboss.resource and org.jboss.tm up to debug or trace and study the log carefully to see what is happening. You should see an XAResource for your connection getting enlisted and delisted from your transaction.

[kumaravnish]

Yes I think thats the problem, I am not using Tx... Here is the code snippet where I get the DB Connection:

final InitialContext ctx = new InitialContext();
final Object home = ctx.lookup(DB_DATA_SOURCE_LOOKUP_NAME);
final DataSource ds = (DataSource) PortableRemoteObject.narrow(home, DataSource.class);
return ds.getConnection();

Also I used the oracle-service.xml to start up the oracle connector in JBoss..

Could you point me to an example or documentation for the Tx type datasource connections??

Thanks for the help

Avnish

[kumaravnish]

I m sorry I think I misunderstood your reply. I am using LocalTx. I am not using XA because I dont have multiple databases.

It looks like all my SQL statements are committed automatically.... I couldnt find a place where I could set autocommit to false.

Thanks in advance

Avnish

[kumaravnish]

Finally got it to work!! All I had to do was wrap the getConnection as well as closeConnection inside the userTransaction.begin() and and userTransaction.commit().

David, looks like you were right, the clean up of properly starting and closing did the trick...

Thanks

[davidjencks]

I'm glad it works now, but I think it originally not working is a bug, so I wrote one up to remind myself to look at it:-)

Thanks
david