Batch Stored procedures insertion in database with Callable statement

Question

Hi All,

We have 4 stored procedures which we are using to insert the entries in database, These 4 are interdependent, If any of this fails Whole operation has to be rolled back, only if everything goes well I want to commit the transaction. How do I achieve that.

Thanks, Rohit.

Answer 1

You need to use JDBC's transaction support, which is described in the JDBC Tutorial here.

Pseudo-code:

Connection conn = ...
conn.setAutoCommit(false);
try {
   doAction1(connection);
   doAction2(connection);
   doAction3(connection);
   doAction4(connection);

   connection.commit();
} catch (Exception ex) {
   connection.rollback();
}

Plus all the usual closing of connections, statements, etc.

The link describes the specifics.