Commitment control with Commit and Rollback with SQL in OpenEdge

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.

commitment control - commit and rollback transaction control

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:

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:

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:

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