Error Handling in HANA Sql

[kapil_kumar8]

Requirement -

This blog explains how we can Implement Error Handling in HANA Sql to maintain data reliability, durability & consistency during execution of multiple DML statements in a single code block.

Implementation Scenario -

Let's say, we have HANA stored procedure or Sql Code block and it has 5 Insert statements inserting data in a table (HXE_SAMPLE.ERROR_TEST) which has Primary Key defined on ID Column.

Sample Table Structure -

CREATE TABLE HXE_SAMPLE.ERROR_TEST (ID INTEGER PRIMARY KEY);

Insert Statements -

Below are the insert statements.

First 4 Statements will execute successfully but 5th statement will fail due to Primary Key violation as we are inserting same value again in ID column. But Primary Key column won't allow duplicate values.

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (2);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (3);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (4);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1); -- FAIL, DUE TO PRIMARY KEY VIOLATION

We can define EXIT HANDLER code block like below to capture the error.

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

SELECT ::SQL_ERROR_CODE AS "ERROR_CODE", ::SQL_ERROR_MESSAGE AS "ERROR_MESSAGE" FROM DUMMY;

END;

Scenario 1 - All Statements needs to be completed Successfully

Based on above insert statements, we want to Rollback all the statements if any of them is failed due to error. Below is the code for this scenario.

I used DO Begin\END block to run all Insert statements under one code block.

DO BEGIN



DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

-- Print ERROR CODE, ERROR MESSAGE

SELECT ::SQL_ERROR_CODE AS "ERROR_CODE", ::SQL_ERROR_MESSAGE AS "ERROR_MESSAGE" FROM DUMMY;

END;



INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (2);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (3);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (4);

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1); -- FAIL, DUE TO PRIMARY KEY VIOLATION

COMMIT;



END;

In above code, we will get error on 5th statement.

Although first 4 Insert statements executed without any error but still they will be rolled back due to ROLLBACK keyword added in EXIT HANDLER code block.

As we have added COMMIT keyword at the end of all Insert statements, so before doing final COMMIT Sql execution will wait for all the statements to completed successfully.

If there is any error then execution will jump to EXIT HANDLER block and we have added ROLLBACK keyword there so all transaction (1 to 4) will be rolled back.

Scenario 2 - Save Data of Statement which is completed Successfully


Considering example of above Insert statements, now we have to save data of those Insert statements which got executed successfully. So we want to save data for first 4 Inserts but not for the 5th Insert and we do not want to execute any other statement after 5th Insert.

DO BEGIN



DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;



-- Print ERROR CODE, ERROR MESSAGE

SELECT ::SQL_ERROR_CODE AS "ERROR_CODE", ::SQL_ERROR_MESSAGE AS "ERROR_MESSAGE" FROM DUMMY;

END;



INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1);

COMMIT;

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (2);

COMMIT;

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (3);

COMMIT;

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (4);

COMMIT;

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (1); -- FAIL, DUE TO PRIMARY KEY VIOLATION

COMMIT;

INSERT INTO HXE_SAMPLE.ERROR_TEST (ID) VALUES (5); -- SHOULD NOT EXECUTE AS THERE IS ERROR IN PREVIOUS INSERT

COMMIT;



END;

Here we have added COMMIT keyword at the end each Insert statement, so after each successful execution transaction will be committed and data will be saved into table.

Conclusion -

Error Handling is very helpful when we are dealing with multiple transactions in single code block. In case of any error it allow us to control the level of data which we want to save.

Any suggestion or work around are most Welcome.

Thanks.

Happy Learning!