Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Transactions with Stored Procedures

 
Alfonso Rodr�guez
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello my name is Alfonso
(forgive my poor English)


My problem is to update data in two different databases. One of the databases the update using a stored procedure but when there is a problem in implementing the rollback procedure does not make me, so I left half the transaction.

The implementation of this I use a session bean (spec 2.1) is the type of transaction using JTA bean type using the UserTransaction interface.

He was reportedly treated as separate transactions. To make sure the transaction use a control table where it stores the procedure if the operation is successful or not. If the transaction was not successful throw an exception and do a roolback.

The logic goes like this:

BeanSession {

public void methodA() {

UserTransaction utx = context.getUserTransaction();

try {

conn = getConnection();

utx.begin();

public void methodInsertHeader(conn, param1, param2,...) {}

public void methodInsertDetail(conn, param1, param2, ...) {}

public void executeStoredProcedure(conn, param1, param2, ...) {}

result = isProcedureSuccessful(){}

if (result != 0) throw new Exception

utx.commit();

} catch (Exception ex) {

utx.rollback();

}

}

}

Method description

public void methodInsertHeader(conn, param1, param2,..)

throws Exception {

try {

conn.prepareStatement(sql)

logic ...

} catch(Exception ex) {

throw new Exception("Error")

}

}


Is correct this?


Can you help in handling transactions

Greetings
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34179
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the the driver support two phase commit?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic