Hello all JBoss gurus, JBoss lovers and you other guys,
I want to use raw JDBC calls to update the database and then see the result from CMP in the same session method. This works fine with a HSQLDB-datasource but not for an Oracle-DS (both local-tx).
Here is some example code I wrote to explain my case:
public void testConnectionPerTX() throws RemoteException {
try {
ServiceLocator sl = ServiceLocator.getInstance();
SequenceHome sh = (SequenceHome) sl.getLocalHome("java:comp/env/ejb/Sequence");
Sequence s = sh.findByPrimaryKey("Test");
log.debug("Value before update:"+s.getValue());
CommonQueryWrapper cqw = new CommonQueryWrapper("update sequences set value=value+1 where name='Test'");
cqw.update();
log.debug("Value after update:"+s.getValue());
cqw.closeAll();
}
catch(Exception e) {
log.error("Problem in AdminController::testConnectionPerTX()", e);
throw new RemoteException("Problem in AdminController::testConnectionPerTX()", e);
}
}
What is left out is the this is a session method configured to be CMT/Required. The CommonQueryWrapper looks up the same DS as the Sequence entity is configured to use and gets a connection from that. It then executes the query.
If I run this with the DS configured to HSQLDB and the original value of the DB column 'value' is 1 I get:
Value before update:1
...
Value after update:2
Which is what I want. If I run this with the DS configured to Oracle8 (with latest JDBC drivers) I get:
Value before update:1
...
Value after update:1 (!!)
And if I look in the database the value is 2 i.e. it gets updated but the CMP doesn't "see it" in the same transaction. Am I right to think that with proper configuration the above example should reuse the same connection for all the 3 calls (the 2 finders + the JDBC one) and that is what goes "wrong" in that new connections are taken from the pool and the result of the update can't be seen because it is runned in a different connection.
Anyway if anyone knows how to make this work for Oracle, please let me know!
