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

 

Transaction Management Using Try

 

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

 

Transaction Management Using Try

 

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

Transaction Management Using Try

 

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

  
Thank you for taking out time to read the above post. Hope you found it useful. In case of any questions, feel free to comment below. Also, if you are keen on knowing about a specific topic, happy to explore your recommendations as well.
 
For any latest updates or posts on our website, you can follow us on LinkedIn. Look forward to connecting with you there.


Share this:
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Tamilmani Anandan
Tamilmani Anandan
1 month ago

Nice article, now I know how to handle this error. Keep up the good work team