SAP HANA: Workaround for using Commit & Rollback in exception handling block of a Stored Procedure

[former_member182302]

Hello Folks,

In this blog, i would like to share my experiences on using Commit & Rollback in exception handling block of a stored procedure in SAP HANA on versions < SP10.

Note: From SP10, Please find the details in the below link

COMMIT and ROLLBACK - SAP HANA SQLScript Reference - SAP Library

Firstly I want to thank Nelson my colleague who helped me in getting this work around. :smile:

Problem description:

If we are using a procedure to load data into any table and if the procedure encounters any error while loading data it may result in faulty data when we are reloading the same table.

To ensure consistency of the data we need to “Roll back” the previous transaction happened and not to commit the data into the table unless the procedure is completely successful.

In the below mentioned example, will be explaining on how to use “COMMIT” and “ROLLBACK” in exception handling block.

Let us take an example procedure, which does "insertion" of data into a table as shown below:

/* Creating a test table with column ‘EMPNO’ as Pk for our example */

CREATE TABLE EMPLOYEE_TEST  (EMPNO INTEGER PRIMARY KEY)

/* Creating a Procedure for testing Predefined Exception Handling */

CREATE PROCEDURE EXCEPTION_HANDLING_TEST AS

BEGIN

/*Pre Defined exception handler */

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT ::SQL_ERROR_CODE AS "Error Code", ::SQL_ERROR_MESSAGE AS "Error Message" FROM DUMMY; -- To display error code and message

INSERT INTO EMPLOYEE_TEST VALUES (1);

INSERT INTO EMPLOYEE_TEST VALUES (1); -- Again inserting 1 will result in unique violation error

END;

/*Calling the procedure*/

CALL EXCEPTION_HANDLING_TEST;

/*Output which shows the predefined error code along with the error message as shown below*/

We are able to handle the exception and show the error message but still the first insertion is completed and the table holds the data which is not desirable as shown below:

so we will use “ROLLBACK” to stop the insertion happening if the procedure encounters any error while loading as shown below:

/* Creating a Procedure for testing Predefined Exception Handling */

CREATE PROCEDURE EXCEPTION_HANDLING_TEST AS
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

INSERT INTO EMPLOYEE_TEST VALUES (1);

INSERT INTO EMPLOYEE_TEST VALUES (1); -- Again inserting 1 will result in unique violation error

COMMIT;

END;

We get an compilation error “Feature not supported” as shown above while trying to use “ROLLBACK” in the exception handler block or using "COMMIT" in the procedure as shown below:

Let us try taking the “ROLLBACK” ,”COMMIT” into a variable as a work around and try to execute them and see if it works fine as shown below:

DROP PROCEDURE EXCEPTION_HANDLING_TEST;

/* Creating a Procedure for testing Predefined Exception Handling */

CREATE PROCEDURE EXCEPTION_HANDLING_TEST (OUT Status_Code INTEGER ) AS

BEGIN

DECLARE  var_commit  VARCHAR(100) := 'COMMIT';

DECLARE var_rollback VARCHAR(100) := 'ROLLBACK' ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

EXEC (:var_rollback);

INSERT INTO "ERROR_LOGS" ( error_no,error_cd,error_msg ) VALUES ( <schemaname>.SNO.NEXTVAL,::SQL_ERROR_CODE,::SQL_ERROR_MESSAGE );

Status_Code := 1;

END;

INSERT INTO EMPLOYEE_TEST VALUES (1);

INSERT INTO EMPLOYEE_TEST VALUES (1); -- Again inserting 1 will result in unique violation error

EXEC (:var_commit);

Status_Code := 0;

END;

The work around has worked we are able to compile the procedure with out any errors.

Now let us call the procedure and check if the inserts are getting rolled back or still a record is getting inserted.

CALL EXCEPTION_HANDLING_TEST(?);

Caller of the procedure can understand if there is any failure in the load by the value of Out_status as shown below: ( if 1 = Failure else Success) as shown below:

You can check the ERROR_LOGS table to see the error code and message:

SELECT * FROM EMPLOYEE_TEST;

Thus we are able to achieve the intended result.

Hoping this document has helped you in understanding on how to use "COMMIT" and "ROLLBACK" in “Exception Handling Block” in SAP HANA.

Please let me know your feedback on this.

Note:  Please be informed that in the latest versions you need not use COMMIT or ROLLBACK in the procedure for consistency of your data it is handled implicitly. Also be informed that we came across side effects of redo log volume increasing due to some inconsistency caused while manually controlling commit. You may choose to use the above workaround if you need COMMIT/ROLLBACK for any-other purpose and also be mindful it is not supported by SAP.

Your's

Krishna Tangudu :smile: