Database Services / Database Access Best Practices

Hi,

I have a use case where I need to insert, update, and delete several rows from a database (potentially in different tables) and have this all treated as a single transaction. For example, if I delete some rows, and then update some rows, and then fail on inserting some new rows, I need to roll back the entire set.

I am running Sonic 8.5, and I found this explanation about transactions in the latest dbs_dev.pdf (http://documentation.progress.com/output/Sonic/8.5.0/Docs8.5/online_help/dbs_dev.pdf):

In order for a series of database operations defined in multiple Database Service steps of a process itinerary to be executed
in the same transaction, the following requirements must be met:

• The container hosting the ESB process must be configured with intra-container messaging enabled (see "Intra-container messaging" in Progress Sonic 8.5 Developer's Guide).
• The database operations must be executed by the same service configuration name.
• The database operations must be configured for consecutive steps in the itinerary.

This seems pretty restrictive, and I'm not sure how it would work in my current use case. I am receiving an XML file with a repeated set of elements. Each element has a child element that indicates the transaction type (delete, update, insert), and then more child elements that represent the data to be inserted, the identifiers of the data to be deleted, etc.

I need to be basically looping through these and taking the appropriate action on each element, but doing it in a way that still meets the above criteria for transactions.

I have 2 questions:

• Can anyone outline for me at a high level how to accomplish my use case and still have it be a transaction?

• Is there an alternate way to accomplish this without using the Sonic ESB Database services? We have other similar use cases where things need to be inserted/updated/deleted across several tables at once and we're looking for a way to make the transactions easier. We were investigating making a custom service that uses a combination of JAXB and JPA (Hibernate) to do our database operations instead of interacting using the Sonic built-in database services. Does anyone have experience with this or any other paths to accomplish this goal, and do you have any recommendations or best practices?

Thanks for the help.