Programmatic Transactions

[Johan Ruttens]

Hi friends,
I'm studying the transactions principle in J2EE a little bit. I worked out a little example, here is the code:

try
{
UserTransaction ut = ejbContext.getUserTransaction();
ut.begin();
PreparedStatement prepStat = null;
for(int i=0;i<3;i++)
{
SQLStatement = "INSERT INTO TransTestTbl1 VALUES('"+data[i][0] + "','"+data[i][1]+"')";
prepStat = conDataSource.prepareStatement(SQLStatement);
prepStat.execute();
}
System.err.println("STATUS: "+ut.getStatus());
int i = 5/0; //Triggering runtime exception here
ut.commit();
System.err.println("STATUS AFTER COMMIT: "+ut.getStatus());
}
catch(Exception ex)
{
try
{
System.err.println("Rolling back");
System.err.println("STATUS: "+ut.getStatus());
ut.rollback();
System.err.println("STATUS: "+ut.getStatus());
}
catch(Exception exx)
{
System.err.println("ERROR while rolling back!");
exx.printStackTrace();
}
System.err.println("ERROR catched");
}

As you can see I trigger a runtime exception. In the catch clause the transaction has to be rolled back. Here is where my problem occurs: the table in the DB gets always filled with the three records, while I expect an empty table (because of the rollback).
Can anyone help me or give me some tips?
I'm using JBuilder 7 with Borland Enterprise Server 5.0.2 and SQLServer 8.0
Thanks in advance.
Johan

[Vishwa Kumba]

Did you check, if the AutoCommit feature for the datasource set to false.
Before inserting the records, you could try conn.getAutoCommit()...

[Johan Ruttens]

Hi,
Thanks for your reply.
I did not touch the autocommit at first, but then changed it to false and this made no difference.

[Chris Mathews]

Originally posted by Johan Ruttens:
As you can see I trigger a runtime exception. In the catch clause the transaction has to be rolled back. Here is where my problem occurs: the table in the DB gets always filled with the three records, while I expect an empty table (because of the rollback).
Can anyone help me or give me some tips?
I'm using JBuilder 7 with Borland Enterprise Server 5.0.2 and SQLServer 8.0

I have a couple comments:
1 - My recommendation is to stick with CMT instead of BMT for your EJBs. CMT is much easier to work with and gives you more flexibility. There are very few cases where BMT is necessary.
2 - I suggest you check the documentation for your Application Server. Most require the use of certain types of Container Managed Datasource. For example, in this particular instance you would need a TXDatasource for transactioning to work properly. Unfortunately, I have next to no knowledge of BES so I can't help much with the specifics of your problem.
I know Borland runs its own newsgroups and forums... maybe you would get a better response if you posted this question there.
[ February 09, 2004: Message edited by: Chris Mathews ]

[Johan Ruttens]

A little adjustment concerning the previous post: if I perform conn.setAutoCommit(false); the table gets never updated, even when I put the runtimeException in comment and explicitly commit the transaction.
I also printed out the transaction status after the commit or the rollback, this is always 6, which stands for STATUS_PREPARING. This seems a bit strange to me.
Johan

[Melody Siu]

I encountered the same problem. It seemed to me that the rollback code
make no differences. However, if you simply throw an EJBException when
rolling is intended it seems to work.
../Mel

[Johan Ruttens]

Ok, I switched to declarative transactions, and ... still no rollback . I tried to throw an EJBException, an SQLException, an Exception, but none of them caused a rollback.
Anyone an idea?
Thanks in advance
Johan