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.
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);
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
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
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...
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:-)