Skip to Content
Previous

Using COMMIT Transactions

By Craig Cmehil

Leveraging SQLScript in Stored Procedures & User Defined Functions through the use of COMMIT

You will learn

In this exercise will show the impact of a runtime error on DML statements and how to prevent it using COMMIT.

Details

Please note - This tutorial is based on SPS11


Step 1: Edit previous procedure

Return to the SQL tab and change the filter value for the first input parameter. Here you are adding a filter on a column which we know does not exists in hopes of causing an error and transaction rollback.

sql tab
Please log in to access this content.
Step 2: Run the CALL ststement

Select the entire CALL statement, and click Run

call statement

Of course we get the error “invalid column name”.

error
Please log in to access this content.
Step 3: Run log errors SELECT

Select the SELECT statement for log.errors again and click Run to check the table contents.

select statement

You will notice that a new row was not inserted into the log table due to transaction rollback.

sql tab
Please log in to access this content.
Step 4: Add COMMIT

Return to the procedure called get_product_by_filter. To avoid the deletion of the log entry in case of transaction rollback, we will use an explicit COMMIT.

procedure editor

Insert a DML statement for the sake of showing the behavior of COMMIT. Insert this INSERT statement with BEGIN and END blocks after the DECLARE statements as shown.

insert DML statement

After the INSERT statement within the EXIT HANDLER, add a COMMIT statement.

insert statement
Please log in to access this content.
Step 5: Save and build

Click Save.

save

Use what you have learned already and perform a build on your hdb module.

HRTT
Please log in to access this content.
Step 6: Run the CALL statement

Then return to the HRTT page and make sure the input parameters are as shown and run the CALL statement again.

You will still get the error for invalid column. Select the SELECT statement for log.errors and click Run to execute it.

select statement

You will now notice that the new row has been inserted into the log table even though there was an error and a ROLLBACK was executed.

rollback
Please log in to access this content.
Step 7: Run log messages SELECT

Highlight the SELECT statement for log.messages and click Run.

run select
Please log in to access this content.
Step 8: Examine results

As you can see not only was the new record inserted into the log.errors table, but also “Chuck Norris” found its way into our log.messages table. The complete transaction will be committed, meaning any modification happened in this transaction will be persisted. A better solution for this are the autonomous transaction.

inserted records
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

15 Min.

Intermediate

Prerequisites

Next
Back to top