I have a situation where I have to insert data into two tables using RDB-Save. The issue is if second insert fails then the first insert should also be reverted. Basically I don't want to insert data in the first table if the second one fails as it happens during two phase commit. There is a Primary- Foreign Key relation in the tables. Is it possible to achieve this using RDB since RDB commits automatically?
Thanks in advance to all the great minds!
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
"Enter an SQL statement to be executed by an activity that includes the RDB-Save method.
As a best practice, include only one SQL statement on this tab. If your situation requires multiple statements, place one statement each in separate Connect SQL rules. Your activity can cause the statements to be executed in the desired sequence. If your situation requires many statements executed in sequence, consider using a stored procedure. (Pega 7 Platform uses prepared statements and submits the SQL code in a single call to database software. Some database software versions may allow multiple statements in one call, but other software versions do not; use of multiple statements on this tab reduces portability and flexibility.)"
We have the same requirement. Unfortunately, the external database we are connecting to is a black box and we do not have the ability to create new stored procedures. We only have permissions to execute the original procedures the old system (that we are replacing with Pega) had access to.
I tried calling two stored procedures within one Connect-SQL rule, but I haven't been able to make this work yet. I know it's possible on some database versions, but seemingly not on this one (older DB2).
We REALLY need for Pega to add Transaction management in an Activity around multiple RDB-Save actions.