TIBCO JDBC Transaction Group Tutorial

By | November 27, 2014

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 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:

table 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.

start output editor complex element

 

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=?

sender jdbc update configuration

 

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:

sender jdbc update input mapping

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:

receiver jdbc update input mapping

 

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:

tibco jdbc transaction group example

 

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):

designer tester process input

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

designer tester run jdbc transaction process

 

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:

updated balance database table

 

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.

jdbc update generate error

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

Ajmal Abbasi

Ajmal Hussain Abbasi is a TIBCO Consultant By Profession with more than 6 years experience in TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Spotfire, TIBCO BE, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in Telecom sector by using his skills in Tibco Designer, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi is also experienced in developing solutions using Oracle PL/Sql, Linux and Java. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn

10 thoughts on “TIBCO JDBC Transaction Group Tutorial

  1. V U Mahesh

    Thank You Abbasi Sir,Please Post Like This Tutorials Regularly,They Are Very Useful To Us.

    Reply
  2. sanjeev

    Sir – not clear as where to write this Xpath Condition “$Update-Receiver-Balance/jdbcUpdateActivityOutput/noOfUpdates!=1”.

    Reply
    1. alec

      I, too, am unclear as to where the XPath Condition is supposed to go.

      Does anyone have any answers to this?

      Thanks.

      Reply
  3. simon

    I have query here, if the JDBC activities connects to different databases, in case of 2nd JDBC activity failure, whether the first JDBC updates will be rolledback or not ?

    Reply
    1. Mohamed Shafi

      All the JDBC activities inside the JDBC Tx group must share the same JDBC connection for it to rollback successfully.

      Reply
  4. Varun Thakur

    Hi Ajmal,

    Can you please explain how JMS sender activity will be rolled back in case a JTA or XA group fails at some point later than the JMS queue sender activity. Will the message be published to that queue or not?

    Thanks a lot for these clear explanations.

    Regards
    Varun Thakur

    Reply
  5. sagar

    hi Ajmal

    there is a minor error , in the update receiver balance instead of subtraction ,there should be addition of account balance +transferAmount
    i.e. the query should be like
    “account_balance=account_balance+ ? ”
    instead of
    “”account_balance=account_balance- ? “”
    as in this example we are transferring the amount from one account to another.

    regards
    sagar

    Reply
  6. Mohamed Shafi

    Can you please explain how a “JDBC Call Procedure Activity” inside a JDBC Transaction group behaves when any error occurs in the group.
    Will the JDBC Call Procedure activity commits/rollback along with other JDBC activities?? What if the function/procedure called by the JDBC Call procedure activity has it’s own commit inside it??

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *