Handling SQL Exceptions

There are couple of ways --

1. Using ESQL to access database state indicators


DECLARE SQLState1 CHARACTER;   
DECLARE SQLErrorText1 CHARACTER;   
DECLARE SQLCode1 INTEGER;   
DECLARE SQLNativeError1 INTEGER;  
  
-- Make a database insert to a table that does not exist --
INSERT INTO Database.DB2ADMIN.NONEXISTENTTABLE (KEY,QMGR,QNAME) 
                              VALUES (45,'REG356','my TESTING 2');

--Retrieve the database return codes --
SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;

--Use the THROW statement to back out the database and issue a user exception--
THROW USER EXCEPTION MESSAGE 2950 VALUES
( 'The SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , 
SQLErrorText1 );


2. Using  handler


DECLARE HANDLER statement creates an error handler for handling exceptions.


If processing of the handler's code completes without throwing further unhandled exceptions, execution of the normal code is resumed as follows:

• For EXIT handlers, the next statement processed is the first statement after the handler's scope.
• For CONTINUE handlers, it is the first directly-contained statement after the one that produced the exception.

Handlers absorb exceptions, preventing their reaching the input node and thus causing the transaction to be committed rather than rolled back. A handler can use a RESIGNAL or THROW statement to prevent this.




DECLARE retryCount INTEGER 0;
DECLARE afterCount INTEGER 0;

WHILE retryCount <= 10 DO
  DECLARE EXIT HANDLER FOR SQLSTATE VALUE 'U11222'
  BEGIN
    /* This demonstrates how to pass data to the HANDLER in the SQL 
       special registers */
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLCODE = SQLCODE;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLSTATE = SQLSTATE;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLNATIVEERROR = SQLNATIVEERROR;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLERRORTEXT = SQLERRORTEXT;

    SET retryCount = retryCount + 1;

    /* If we are an EXIT HANDLER, control is now passed to back to the 
       WHILE statement */
  END;

  /* In a real scenario this could be a PROPAGATE statement, and the exception
     could be thrown by a 'downstream' node. In this case the HANDLER would 
     normally cope with a wider range of exception, for example, using LIKE '%' */
  THROW USER EXCEPTION VALUES( -1, 'U11222', 42, 'error text' );
          
  /* This is the next statement executed if it is a CONTINUE HANDLER */
  SET afterCount = afterCount + 1;
          
END WHILE;
        
SET OutputRoot.XMLNSC.Top.WHILE.retryCount = retryCount;
SET OutputRoot.XMLNSC.Top.WHILE.afterCount = afterCount;   

With EXIT (as above) the output is:
<Top>
  <WHILE>
    <mySQLCODE>-1</mySQLCODE>
    <mySQLSTATE>U11222</mySQLSTATE>
    <mySQLNATIVEERROR>42</mySQLNATIVEERROR>
    <mySQLERRORTEXT>error text</mySQLERRORTEXT>
    <retryCount>11</retryCOUNT>
    <afterCount>0</afterCOUNT>
  </WHILE>
</Top>

Changing the HANDLER to be CONTINUE (DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE
'U11222') then the output is:
<Top>
  <WHILE>
    <mySQLCODE>-1</mySQLCODE>
    <mySQLSTATE>U11222</mySQLSTATE>
    <mySQLNATIVEERROR>42</mySQLNATIVEERROR>
    <mySQLERRORTEXT>error text</mySQLERRORTEXT>
    <retryCount>11</retryCOUNT>
    <afterCount>11</afterCOUNT>
  </WHILE>
</Top>



You see the difference in afterCount in the output message.







RESIGNAL statement rethrows the current exception, if one exists.

You can use RESIGNAL only in error handlers.

Typically, RESIGNAL is used when an error handler catches an exception that it cannot handle. The handler uses RESIGNAL to rethrow the original exception so that a handler in higher-level scope has the opportunity to handle it.

Because the handler throws the original exception, rather than a new (and therefore different) one:
The higher-level handler is not affected by the presence of the lower-level handler.
If no higher-level handler is present, you get a full error report in the event log.


RESIGNAL;