TIBCO JDBC Transaction Group Tutorial

Transaction is one of the group actions that you can use for activities grouped in a TIBCO BW process. Transaction group combines a set of activities as a single unit and they are commit or rollbacked based on completion of complete transaction. If any activity inside the transaction fails, all grouped activity actions are roll backed.

In this TIBCO JDBC Transaction Group step by step tutorial, I will explain how we can put a set of JDBC activities in a Transaction group so that they are committed together to maintain data consistency and correctness.

Example Scenario:

Consider a Banking Scenario in which balance is transferred from one bank account to another bank account. Account Balance of sender’s account is debited while receiver’s account is credited by the same amount. If transaction fails to update receiver’s account balance, sender’s account balance should also remain unchanged and any changes should be committed only as a transaction group and not individually.

For this scenario, a database table TBL_ACCOUNT is used which has below data:

 

Based on the above simple scenario, let’s now proceed with  TIBCO JDBC Transaction Step by Step Tutorial.

 

Step 1: Create Process and Define Input Schema

Create anew process in TIBCO Designer. In the start activity output editor, create a Complex Element with account balance, receiver account number and sender account number as its elements.

 

Step 2: Add JDBC Update Activities with JDBC Transaction Group Action

To explain the scenario clearly, we will use two JDBC Update activities here. One JDBC activity will update the sender’s account balance while other JDBC activity will update receiver’s balance by adding specified amount.

Below is the configuration of first JDBC Update activity where I have used update statement to deduct the amount from the balance:

update tbl_account
set
account_balance=account_balance-?
where
account_id=?

 

The two parameters shown in above screenshot are used in the Input tab where account balance and account id of the sender is mapped from the Start Activity’s output as shown below:

JDBC Update activity for Receiver’s account balance is also configured in same way. In this case, update query uses receiver’s account id.

update tbl_account
set
account_balance=account_balance-?
where
account_id=?

In the Input mapping for this JDBC Update activity, receiver account id (ToAccount) is mapped as shown below:

 

If Receiver’s account doesn’t exist; i.e. JDBC Update activity for the Receiver’s account doesn’t update a record, we will generate error manually and will catch the exception.

For this purpose, write below XPath Condition and create a transition from second JDBC Update activity to Generate Error activity.

$Update-Receiver-Balance/jdbcUpdateActivityOutput/noOfUpdates!=1

In the Input of Generate Error activity, you can specify any custom Message and Message Code for the exception.

Now, select the two JDBC Update activities and Generate Error activity and create a group. In the Group Action, choose Transaction while in Transaction Type choose JDBC as shown in below screenshot:

 

Add Catch activity and choose Catch All option in its configuration. Simply map from Catch activity to the End activity as we don’t want to add any Exception handling logic or any other actions here. In real time scenarios, you might be adding some Logging or other activities after catching the exception.

Now validate the process and make sure that there are no validation errors in the designed process.

 

Step 3: Test JDBC Transaction Process in designer tester

In this step, we are going to run and test our process to see how JDBC Transaction groups work. Load the process in designer tester and give it input like below (I am giving To and From Account Ids which do exist in database table TBL_ACCOUNT):

Click OK button so that process starts running. As you can see below process has run successfully to its End:

 

Now if you query database table, you can see that sender’s account balance has been debited while receiver’s balance has been credited successfully by amount 50:

 

Now in order to show you how the two activities work as a Transaction group and any error inside the group results in complete rollback, I am going to give receiver’s account id which doesn’t exist in the table (say 232) so that error is generated.

As you can see in below screenshot, first JDBC Update activity has run successfully but error is generated in the second JDBC Update activity which is caught and the process is ended.

Now if you query tbl_account table again, you will notice that no change has taken place for sender as well as receiver’s account balance as activities were in JDBC Transaction group and they are rolled back together.

 

That’s it for this topic. I hope you liked it. Feel free to contact me for any further help or clarification. Thanks