Question
Okahara
Last activity: 17 Jun 2018 20:54 EDT
Commit or roll back operation after update results on multiple tables
Hi,
We have multiple tables and want to commit after we make sure Pega is able to update / insert into all tables. If any of them fails for any reasons, then I want all tables to roll back. Let's say, we have following 2 classes:
Sandbox-Data-Customer (Customer table)
Sandbox-Data-Contract (Contract table)
In an activity, if I am able to update or insert into two of them successfully, I commit. If Customer table is updated but Contract table is for some reason unreachable and DML fails, then roll back.
#1. How would I implement such requirement?
#2. Does it make any difference if I am using Obj method or RDB method? In some scenario I would be using RDB method and need to make sure if it is still feasible.
Thanks,
Accepted Solution
Vodafone Shared Services India
IN
HI,
** RDB operations are implicit commit, you have to handle rollback by delete query or updating old values.
Please check below if it helps for Obj-save:
*** please commit here any un-committed operations.
1. Save Sandbox-Data-Customer {On jump, check StepStatusGood. If true, Jump to 2 else Jump to 4}
2. Save Sandbox-Data-Contract {On Jump, check StepStatusGood. If true, Jump to 3 else Jump to 4}
3. Commit {If successful, Exit activity}
4. Rollback
Pegasystems Inc.
JP
Hi,
Thanks, I am working together with the author. I understand in Obj method it is feasible by the step you described. For RDB method, I understand RDB operations are implicit commit, but in our projects some of the operations require RDB method - in that case do you think delete operation as rollback is the only way? Is there any better approach you can think of?
Thanks,
Vodafone Shared Services India
IN
Hi,
For RDB, if record is new, you can use delete operation for rollback.
Else, take old data on clipboard page and if rollback is to be done override recent update with old data.
This is what i can think of as of now.
Thanks
Pegasystems Inc.
JP
Hi,
Thanks for the info. One more question - is it possible to run PL/SQL script from Pega? If it is, which one do you think is better approach and why?
Thanks,
Vodafone Shared Services India
IN
You can call stored procedure in Connect-SQL browse tab. Create stored procedure in DB where Pega OOTB procedures are present. Name should start with "sppc_"
You can have output (not result list but single variable )from stored procedure to determine whether commit or rollback happened.
Based on this you can commit or rollback or skip other operations as well.
Note: This info is from docs, so just have a try and let us know it it works.
Pegasystems Inc.
JP
Thanks for the info.
#1. I see all out-of-the-box stored procedures are named starting with "SPPC_" or "SPPR_" but is this mandatory? If I name it differently it won't work?
#2. I have created a sample stored procedure in Oracle database. Now, how exactly would I pass IN parameter to stored procedure and receive OUT parameter back to Pega? Real sample codes would be greatly appreciated. If you are referring to any documentation that would be helpful too.
Thanks,
Okahara
#1. No, SPPC or SPPR prefix is not required. You can name whatever you like.
#2. You can call stored procedure by below syntax. You should also prepend schema name to procedure name.
\{call my_stored_proc({Values.In1}, {Values.Out1 out}, {Values.InOut1 in out }) \}
Thanks,