Syntax
>>-DECLARE--+-CONTINUE-+--HANDLER FOR--| State |--Stmt--------->< '-EXIT-----' State .-<<-- , --<<------------------------------------------------. V | |------SQLSTATE--+-+-------+--' Text '----------------------+---+--| | '-VALUE-' | '---LIKE--' Text '--+------------------+---' '-ESCAPE--' Text '-'
You can declare handlers in both explicitly declared (BEGIN...END) scopes and implicitly declared scopes (for example, the ELSE clause of an IF statement). However, all handler declarations must be together at the top of the scope, before any other statements.
If there are no exceptions, the presence of handlers has no effect on the behavior or performance of an SQL program. If an exception occurs, IBM® Integration Bus compares the SQL state of the exception with the SQL states associated with any relevant handlers, until either the exception leaves the node (just as it would if there were no handlers) or a matching handler is found. Within any one scope, handlers are searched in the order they are declared; that is, first to last. Scopes are searched from the innermost to outermost.
The SQL state values provided in DECLARE... HANDLER... statements can be compared directly with the SQL state of the exception or can be compared using wildcard characters. To compare the state values directly, specify either VALUE or no condition operator. To make a wildcard comparison, use the underscore and percent characters to represent single and multiple character wildcards, respectively, and specify the LIKE operator. The wildcard method allows all exceptions of a general type to be handled without having to list them exhaustively.
If a matching handler is found, the SQLSTATE and other special registers are updated (according to the rules described later in this section) and the handler's statement is processed.
As the handler's statement must be a single statement, it is typically a compound statement (such as BEGIN...END) that contains multiple other statements. There is no special behavior associated with these inner statements and there are no special restrictions. They can, for example, include RETURN, ITERATE, or LEAVE; these affect their containing routines and looping constructs in the same way as if they were contained in the scope itself.
Handlers can contain handlers for exceptions occurring within the handler itself
- 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.
Each handler has its own SQLCODE, SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT special registers. These come into scope and their values are set just before the handler's first statement is executed. They remain valid until the handler's last statement has been executed. Because there is no carry over of SQLSTATE values from one handler to another, handlers can be written independently.
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.
See SQLSTATE function for a list of the valid SQLSTATES that you can use in a HANDLER.
Example 1
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>Example 2
-- Drop the tables so that they can be re-created with the latest definition.
-- If the program has never been run before, errors will occur because you
-- can't drop tables that don't exist. We ignore these.
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE'%' BEGIN END;
PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Invoices' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Sales' TO Database.DSN1;
PASSTHRU 'DROP TABLE Shop.Parts' TO Database.DSN1;
END;
Last updated Friday, 21 July 2017