SQL Transaction support in MII 14.0

[Former Member]

Recently I faced a scenario where I had to insert values in 2 different tables and both the insert statements should be in a transaction i.e. either the values are inserted in both the table or should fail for both. To be more specific about the scenario, first I had to insert values in one table then fetch some values from ERP System and then insert these values into the second table with Foreign Key Relation to the first table.

So I used the SQL Transaction Support feature of MII which got released with 14.0 SP0 Release.

This feature enables the user to group SQL queries within a JTA transaction.

You can find these action blocks under the 'SQL Transation Support' category.

As we can see, there are 4 actions available and their names suggest what they do.

1. SQL Transaction Begin: This action block starts the Transaction. Here you have to specify the data server on which this transaction would run.

2. SQL Transaction Commit: Commit the transaction

3. SQL Transaction Rollback: Rollback the transaction

4. SQL Transaction End: End the transaction

Here is the sample transaction I used for my scenario:

The logic shown in the screen shot of transaction is the simplest way of showing how to use the MII transaction support in MII.

If any exception happens any where in the transaction then it is cached by Catch action block and the SQL transaction is Rollbacked by the Rollback action block.

Another point to take care of is mapping the query to the SQL transaction i.e. add the query in the SQL transaction. For this go to the Link Editor of SQL query and map the 'SQLTransaction' link of the query to the 'SQLTransaction' link of the 'SQL Transaction Begin' action block.

If this linking of 'SQLTransaction' links is not done then that particular query would not be part of the SQL transaction and would be executed independent of other queries.

I also tested these action blocks with MDO Connector and MDO Query and it worked as expected.

The only short coming this functionality has is that it lacks support for KPI Connector and KPI query and I am hopeful that this would be done in future releases.

What I felt was that in my case I only had 2 insert statements and not a huge logic to handle thus I was not in the favor of writing a stored procedure. At this time these action blocks came to my rescue.