How to handle transaction using try block

Transaction Management Using Try – In this use case you will learn how to handle the transaction using try block (either all success or all failure)

In this use case we are inserting the data in 2 Database tables customers and address in sequential manner, if the Insert got failed in Address table due to some error,data should be rollback from Customers table which was previously inserted

Create a project in Anypoint studio

Create input to insert data in table

[
    {
        "Customerid": 1,
        "Firstname": "TEST1",
        "Lastname": "TEST1",
        "Address1": "Flat 1ddsdfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff",
        "Address2": "CP 1",
        "Postalcode": "201020",
        "City": "Delhi",
        "Updateddate": "12-01-2010"
    },
    {
        "Customerid": 2,
        "Firstname": "TEST2",
        "Lastname": "TEST2",
        "Address1": "Flat 2",
        "Address2": "CP 2",
        "Postalcode": "201020",
        "City": "Delhi",
        "Updateddate": "12-01-2010"
    },
    {
        "Customerid": 3,
        "Firstname": "TEST3",
        "Lastname": "TEST3",
        "Address1": "Flat 1",
        "Address2": "CP 1",
        "Postalcode": "201020",
        "City": "Delhi",
        "Updateddate": "12-01-2010"
    },
    {
        "Customerid": 4,
        "Firstname": "TEST4",
        "Lastname": "TEST4",
        "Address1": "Flat 1",
        "Address2": "CP 1",
        "Postalcode": "201020",
        "City": "Delhi",
        "Updateddate": "12-01-2010"
    }
]

Prepare the payload to insert data in Customer table

%dw 2.0
output application/java
---
payload map((item,index) ->{
	"CUSTOMER_ID":item.Customerid,
	"FIRSTNAME":item.Firstname,
	"LASTNAME":item.Lastname,
	"UPDATEDDATE":item.Updateddate
})

Prepare the payload to insert data in Address Table

%dw 2.0
output application/java
---
payload map((item,index) ->{
	"CUSTOMER_ID":item.Customerid,
	"ADDRESS1":item.Address1,
	"ADDRESS2":item.Address2,
	"POSTALCODE":item.Postalcode,
	"CITY":item.City,
	"UPDATEDDATE":item.Updateddate
})

Drag and drop the Try block and configure the Transaction Action as ALWAYS_BEGIN to start the Transaction

Drag and drop Bulk Insert Operation and configure the connection details

Provide the input parameter and sql quey text

Select the Bulk Insert Operation and Go to Advanced tab and configure the Transaction Action as ALWAYS_JOIN

Similarly configure the Bulk Insert operation for Address Details and Transaction action as ALWAYS_JOIN

Deploy the project and test the same from postman, Data rollback successfully from Customer table in case of any error while inserting the data in to Address table

Sample Transaction Management Using Try – download sample zip