Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Handling Nested DB Transactions

  1. How-to
0
Votes
Undo
  1. CJ Lai
    CJ Lai
  2. PowerBuilder
  3. Tuesday, 5 October 2021 15:17 PM UTC

Hi All

I would like to get your input on how to handle a series of database transactions (Insert/Update) in PowerBuilder 2017, R3.

In my application, a business process calls for 3 different database tables' insert/update. (Let's call them tables T1, T2, and T3. The flow of the process is to insert/update rows in T1 then to T2, then to T3)

Here is the requirement: only when T3 is successfully committed then to commit T2. Only when T2 is successfully committed, then to commit T1.

If at any point an error occurs, a rollback will be executed for the current table AND the table/s before.

How should I go about it to be most efficient and not jamming up the application to database I/O?

Thank you for your input

CJ

 

FileCopy unable to copy from other folders
PowerBuilder and TFS
Accepted Answer
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 5 October 2021 22:13 PM UTC
  2. PowerBuilder
  3. # Permalink

This can be done using CHECKPOINTs; the exact syntax will depend on your PC.

THis can also be done manually with Semaphores or simply with flags.

long   ll_sql_change[], ll_idx, ll_max, ll_total

// I need three tables to update successfully in order to commit
ll_max = 1
ll_sql_change [1] = -1
ll_sql_change [2] = -1
ll_sql_change [3] = -1

// Update table_1;
ll_sql_change [1] = sqlca.sqlCode

// Update table_2;
ll_sql_change [2] = sqlca.sqlCode

// Update table_3;
ll_sql_change [3] = sqlca.sqlCode

// Check status
FOR ll_idx = 1 TO ll_max

   ll_total = ll_total + ll_sql_change [ll_idx]

NEXT

IF (ll_total = 0) THEN
   COMMIT;
ELSE
   ROLLBACK;
END IF

Comment
There are no comments made yet.
Responses (4)
Shekar Reddy
Shekar Reddy Accepted Answer Pending Moderation
  1. Tuesday, 5 October 2021 15:23 PM UTC
  2. PowerBuilder
  3. # 1

If you are using PFC, its Multi-Table update DW service is easy to use.

Otherwise, see PB Help on dw.Update() method for updating multiple tables using PowerScript.

Commit all or Rollback all will be handled by the dB if you use the example code in Update() method.

Using datawindows/datastores is easier than embedded SQL because just calling the dw.Update() method will take care of any and all inserts, deletes and updates!

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Wednesday, 6 October 2021 11:33 AM UTC
  2. PowerBuilder
  3. # 2

Hello CJ,

Could you explain better what you mean by 

*************************************************************
The flow of the process is to insert/update rows in T1 then to T2, then to T3
Here is the requirement: only when T3 is successfully committed then to commit T2. Only when T2 is successfully committed, then to commit T1.
**************************************************************

Do you want to separate the insert/update and commits?

I think there could be only one commit (or Rollback) per DB transaction.  

Happiness Always
BKR Sivaprakash

 

 

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 5 October 2021 15:22 PM UTC
  2. PowerBuilder
  3. # 3

Hi CJ;

  Sounds like the DB Commit should only occur after T3 is successfully updated and that would then encompass T1 & T2. Same for a rollback after T3 fails.

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.