Local error handler statement

Start of change

This statement enhances error handling within the broker by incorporating an exception handler.

End of change

Syntax

Start of change

You can declare handlers in both explicitly declared (BEGIN...END) scopes and implicitly declared scopes, for example, the ELSE clause of an IF statement. The handler declaration list must be the first item within any scope.

In the absence of exceptions, the presence of handlers has no effect on the behavior or performance of an SQL program. If an exception occurs, the SQL state of the exception is compared 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 (LIKE not specified) or can be compared using wild card characters (LIKE specified).

In the latter case, underscore and percent characters represent single and multiple character wild cards respectively and, the state values are matched according to the rules of the LIKE operator. This allows all exceptions of a general type to be handled without having to exhaustively list them.

If a matching handler is found, the SQLSTATE and other special registers are updated (according to rules described below) and the handler's statement is processed.

As this is in principle a single statement, it is likely to be a compound statement (BEGIN...END) containing many statements. There is no special behavior associated with these statements and there are no special restrictions. They can, for example, contain RETURN, ITERATE, or LEAVE statements and these affect their containing routines and looping constructs in the same way as if these statements were contained in the scope itself. Handlers can contain handlers for exceptions occurring within the handler itself

If processing of the handler's code completes without throwing further unhandled exceptions, processing 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 can absorb exceptions so preventing their reaching an input node and, therefore, causing the transaction to be committed rather than rolled back.

End of change
Related concepts
ESQL
Related tasks
Developing ESQL
Related reference
Syntax preference
ESQL statements