Committing database updates

The information in this topic does not apply to the DatabaseInput node. For more information about the DatabaseInput node, see Event-based database integration.

You must use separate ODBC connections if you want to include nodes with Automatic transaction status and nodes with Commit transaction status in the same message flow, where the nodes operate on the same external database. Set up one connection for the nodes that are not to commit until the completion of the message flow, and a second connection for the nodes that are to commit immediately.

For each node, select the appropriate option for the Transaction property to specify when its database updates are to be committed:

• Choose Automatic (the default) if you want updates made in this node to be committed or rolled back as part of the whole message flow. The actions that you define in the ESQL module are performed on the message and it continues through the message flow. If the message flow completes successfully, the updates are committed. If the message flow fails, the message and the database updates are rolled back.
• Choose Commit if you want to commit the action of the node on the database, irrespective of the success or failure of the message flow as a whole. The database update is committed when the node processing is successfully completed, that is, after all ESQL has been processed, even if the message flow itself detects an error in a subsequent node that causes the message to be rolled back.

The value that you choose is implemented for the database tables that you have updated. You cannot select a different value for each table.

If you have set Transaction to Commit, the behavior of the message flow and the commitment of database updates can be affected by the use of the PROPAGATE statement in the node's ESQL.

If you choose to include a PROPAGATE statement that generates one or more output messages from the node, the processing of the PROPAGATE statement is not considered complete until the entire path that the output message takes has completed. This path might include several other nodes, including one or more output nodes. Only then does the node that issues the PROPAGATE statement receive control back and its ESQL terminate. At that point, a database commit is performed, if appropriate.

If one of the nodes on the propagated path detects an error and throws an exception, the processing of the node in which you have coded the PROPAGATE statement never completes. If the error processing results in a rollback, the message flow and the database update in this node are rolled back. This behavior is consistent with the stated operation of the Commit option, but might not be the behavior that you expect.