When it comes to transaction management, there are notable differences between PowerServer and PowerBuilder applications. One key distinction lies in how errors are handled during the commitment of multiple updates when autocommit=false.
In PowerBuilder traditional C/S applications, if multiple updates are committed within the same transaction (when autocommit=false) and an error occurs during one of the updates, only the failed update is rolled back. The remaining updates are committed successfully without being affected by the error.
However, in PowerServer installable cloud apps, when multiple updates are committed in a single transaction (when autocommit=false) and an error occurs during any update, not only is the failed update rolled back but also all preceding updates are rolled back to maintain data consistency. Subsequent updates following the failed one are processed in a new transaction and are committed independently.
In the following code example, updates from three DataWindows are committed to the database.
In the case of PowerBuilder traditional C/S application, dw_1 and dw_3 updates will be committed successfully, while dw_2 update will be rolled back.
In the case of PowerServer installable cloud app, after the failure of dw_2 update, both dw_2 and dw_1 updates will be immediately rolled back, and only the update for dw_3 will be committed successfully.
SQLCA.Autocommit = false; dw_1.update() //Assuming update is successful dw_2.update() //Assuming update failed dw_3.update() //Assuming update is successful Commit using SQLCA;
Workaround:
To address these differences and ensure proper transaction management, it is advisable to check the status of each update after execution and then decide whether to commit or rollback.
The above code can be modified as follows:
SQLCA.Autocommit = false; If dw_1.update() = 1 then Commit using SQLCA; Else Rollback using SQLCA; End if If dw_2.update() = 1 then Commit using SQLCA; Else Rollback using SQLCA; End if If dw_3.update() = 1 then Commit using SQLCA; Else Rollback using SQLCA; End if
By incorporating this approach, developers can ensure that each update operation is individually handled based on its success or failure, leading to more predictable and reliable transaction management in both PowerBuilder and PowerServer.
