JDBC Transaction control in Sybase

Question

Need help in JDBC transaction control mechanism in JAVA.

Issue:

There are certain stored procedures in our Sybase DB that needs to be run on Unchained mode. Since we are updating our data on two different databases (unfortunately, both Sybase) we need to be able to rollback all our previous transactions, if there is any failure.

But running with Unchained Mode (Auto commit - on) is not helping us with the rollbacks as some of the SPs have already committed the transactions.

Connection connection = getConnection();
PreparedStatement ps = null;
try{
   String sql = getQuery(); // SQL Chained Mode 
   ps = connection.prepareStatement(sql); 
   ps.executeUpdate();        //Step 1
   .
   .
   sql = getTransctionQuery(); // SQL Unchained Mode
   connection.setAutoCommit(true);        //Step 2
   ps = connection.prepareStatement(sql); 
   ps.executeUpdate();
   connection.setAutoCommit(false);
   .
   .
   sql = getQuery(); // SQL Chained Mode 
   ps = connection.prepareStatement(sql);  
   ps.executeUpdate();     //Step 3 This step fails.
   connection.commit();
}catch(){
   connection.rollback(); //Doesn’t rollback step 1 and understandably step 2.
}
finally{
   connection.close();  //cleanup code
}

We would ideally like to rollback both step 1 and step 2 effectively if 3 fails.

Current Solution:

Our idea is to reinvent the wheel and write our own version of rollback (by deleting inserted records and reverting the updated values, from Java).

Need effective solution

Since this solution is effort intensive and not fool proof we would like to know if there are any other better solutions.

Thanks

Answer 1

You need to perform an explicit BEGIN TRANSACTION statement. Otherwise, every DML is a transaction by itself which you cannot control. Obviously autocommit must be off as well.