Committment control with Commit and Rollback using SQL and ODBC in OpenEdge
Commitment control is an important part of a transactional system. OpenEdge supports the use of commit and rollback to allow for better control on transactions. Please use commitment control. By default, OpenEdge uses auto-commit ON, which means that insert, update and delete are immediately committed. However, Progress OpenEdge ODBC and JDBC driver allows turning auto-commit OFF and thereafter allow explicit commit and rollback statements. This article takes an example of record deletion with commit and rollback.
Delete data with auto-commit ON
Let’s delete data from department table. We will keep auto-commit ON as is. It is important to note that isolation level should be either READ COMMITTED, REPEATABLE READ or SERIALIZABLE for insert, update and delete to work. READ UNCOMMITTED will not allow insert, update and delete.
Create a file called delete-department.php with content as follows:
<?php putenv('ODBCINI=/etc/odbc.ini'); putenv('ODBCINST=/etc/odbcinst.ini'); putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib'); $newDeptCode = 900; // build SQL to create new department $sql = sprintf( "delete from pub.department where deptcode = %d", $newDeptCode ); // establish connection and prepare statement. Handle errors with preparing $db = new PDO('odbc:Sports', 'sysprogress','test'); $stmt = $db->prepare($sql); if ($stmt === false) { echo sprintf("Error: %s\n", print_r($db->errorInfo(), true)); exit; } // execute statement. Handle errors with execution $deleted = $stmt->execute(); if ($deleted === false) { echo sprintf("No record was deleted. Error: %s\n", print_r($stmt->errorInfo(), true)); exit; } // ensure that a record was impacted if ($stmt->rowCount() !== 1) { echo "Delete executed cleanly but no record was deleted\n"; exit; } // print outcome echo sprintf("%d department code deleted\n", $newDeptCode); ?>
Execute this script by typing php delete-department.php
Note the additional error handling in this piece of code. We report error if we cannot prepare a statement OR execution of the SQL statement fails. After reporting an error, we exit. If all goes well, we show that the department was deleted.
Let’s perform the same operation with auto-commit OFF
Delete data with auto-commit OFF without calling commit
Create a file called delete-department-parameters-no-commit.php with the following text:
<?php putenv('ODBCINI=/etc/odbc.ini'); putenv('ODBCINST=/etc/odbcinst.ini'); putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib'); $newDeptCode = 1200; // build SQL to create new department // NOTICE: There are ? instead of actual values $sql = 'delete from pub.department where deptcode = ?'; // establish connection and prepare statement. Handle errors with preparing $db = new PDO('odbc:Sports', 'sysprogress','test'); $stmt = $db->prepare($sql); if ($stmt === false) { echo sprintf("Error: %s\n", print_r($db->errorInfo(), true)); exit; } // execute statement. Handle errors with execution // NOTICE: execute now has an array that will replace the ? // NOTICE: we are starting a new transaction and ending it with commit $db->beginTransaction(); $updated = $stmt->execute(array($newDeptCode)); // NOTICE: we will NOT be executing this statement: $db->commit(); if ($updated === false) { echo sprintf("No record was deleted. Error: %s\n", print_r($stmt->errorInfo(), true)); exit; } // ensure that a record was impacted if ($stmt->rowCount() !== 1) { echo "Delete executed cleanly but no record was deleted\n"; exit; } // print outcome echo sprintf("%d department code deleted\n", $newDeptCode); // automatic rollback is called ?>
Execute this script with php delete-department-parameters-no-commit.php
You will notice that the data is still there after successful execution of this script. There is no explicit commit at the end of the script. Therefore data gets rolled back.
Delete data with auto-commit OFF with commit
Create a file called delete-department-parameters-commit.php with the following text:
<?php putenv('ODBCINI=/etc/odbc.ini'); putenv('ODBCINST=/etc/odbcinst.ini'); putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib'); $newDeptCode = 1200; // build SQL to create new department // NOTICE: There are ? instead of actual values $sql = 'delete from pub.department where deptcode = ?'; // establish connection and prepare statement. Handle errors with preparing $db = new PDO('odbc:Sports', 'sysprogress','test'); $stmt = $db->prepare($sql); if ($stmt === false) { echo sprintf("Error: %s\n", print_r($db->errorInfo(), true)); exit; } // execute statement. Handle errors with execution // NOTICE: execute now has an array that will replace the ? // NOTICE: we are starting a new transaction and ending it with commit $db->beginTransaction(); $updated = $stmt->execute(array($newDeptCode)); $db->commit(); if ($updated === false) { echo sprintf("No record was deleted. Error: %s\n", print_r($stmt->errorInfo(), true)); exit; } // ensure that a record was impacted if ($stmt->rowCount() !== 1) { echo "Delete executed cleanly but no record was deleted\n"; exit; } // print outcome echo sprintf("%d department code deleted\n", $newDeptCode); ?>
Execute this script with php delete-department-parameters-commit.php
Now you will notice that the data has been deleted as desired. To rollback data, use $db->rollback()
instead of $db->commit();
.
Where to use commitment control?
Whenever there are multiple transactions where one depends on the other, please use commitment control. For example:
- Add order header with customer information, date of order etc.
- Add order line items one by one ONLY if header was correctly inserted AND order line prior to the one being inserted was successful
This is a good case where transaction can be started prior to any inserts. Then, insert order header. Then, insert line items and finally issue a commit. If any of the statement fails, an automatic rollback can be issued. If any abnormality exists during the insert or a condition is not met, rollback can be issued.
Python, Ruby, C# and other languages have support to turn off auto commit and using commit and rollback, or similar, methods to achieve the same results as above.
Using commitment control will help make your code robust and reduce dangling/orphaned records – a record with no parent when a parent record is expected, or a record with no child when a child record is expected. This concludes this short article about commit and rollback in commitment control.
Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP