| Author |
Message
|
| jboller001 |
 Posted: Wed Oct 25, 2006 2:12 pm Post subject: Database Rollback using Java Compute Node |
 |
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
I have a message flow that inserts data from each message into an Oracle table. I use the MbSQLStatement class to insert the data and have configured the class to throw an exception on a database error.
Currently when an exception occurs after the record is inserted into the table, the database insert is not rolled back. I would like to configure the flow so that if an exception occurs post database insert the record is rolled back.
The flow has been configured as follows:
1. The Transaction Mode of the MQInput node is set to Automatic.
2. All exceptions are caught by an exception handling sub flow that is connected to the Catch and Failure terminals of the MQInput node. The subflow processes the exceptions and sends them to an error queue.
3. The Transaction Mode and Persistence Mode values of the MQOutput nodes are set to Automatic.
Any thoughts on how to achieve this? Do the messages need to be persistent? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Oct 25, 2006 3:07 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Database rollbacks for broker (or XA) coordinated transactions do not occur if the message flow does not throw an error all the way back to the xxxInput node that started the flow.
MbSQLStatements use Broker coordinated transactions.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jboller001 |
| Posted: Thu Oct 26, 2006 5:40 am Post subject: |
|
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
Do you have any ideas on how I can do this but at the same time implement custom exception handling logic?
The rollback works when I disconnect the MQInput node's catch terminal. However if I connect the catch terminal and include a Throw Node at the end of the exception handling flow the exception is thrown back to the catch terminal which causes a loop.
What I'm trying to do is catch an exception, parse the exception list tree and create a custom exception message to be sent to another queue, and throw an exception that would cause the database insert to roll back. After running a few tests it seems like I have to choose between one or the other. |
|
| Back to top |
|
|
| jboller001 |
| Posted: Thu Oct 26, 2006 1:50 pm Post subject: SOLVED |
|
|
Apprentice
Joined: 31 May 2006
Posts: 40
|
| Was able to do this using a TryCatch node and placing a couple Throw nodes in the exception handling flow connected to the TryCatch catch terminal. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 26, 2006 1:54 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
There are several good references on error handling in Broker message flows, including a devworks article or two.
Generally, you need to change the transaction mode on any resources that you need to commit inside your error handling and then end with a THROW node to propagate back up to the xxxInput node.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
