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


  1. 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

  2. Select the entire CALL statement, and click “Run”

    call statement

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

    error

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

    select statement

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

    sql tab

  6. 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

  7. 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

  8. After the INSERT statement with in the EXIT HANDLER, add a COMMIT statement.

    insert statement

  9. Click “Save”.

    save

  10. Use what you have learned already and perform a build on your hdb module. Then return to the HRTT page and make sure the input parameters are as shown and run the CALL statement again.

    HRTT

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

    select statement

  12. 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

  13. Highlight the SELECT statement for log.messages and click “Run”.

    run select

  14. 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

Next Steps

Updated 09/19/2016

Time to Complete

15 Min.

Intermediate

Prerequisites

Next
Back to top