Managing Exception Conditions
An exception is raised if an error occurs when executing an SQL statement. Every exception is identified by an exception condition, expressed in terms of its SQLSTATE value.
An SQLSTATE value is represented by the keyword SQLSTATE followed by a 5-character string containing only uppercase alphanumeric characters. The first two characters of the string identify the exception class and the last three the exception sub-class.
In Mimer SQL, the range of possible SQLSTATE values is divided into standard values and implementation-defined values. The implementation-defined values are those beginning with the characters J-R, T-Z, 5-6 and 8-9. For a list of the values, see Mimer SQL Reference Manual, Appendix E, SQLSTATE Return Codes.
Whenever an exception is raised, the exception condition is placed in the diagnostics area and the SQLSTATE value can be retrieved by using the RETURNED_SQLSTATE option of GET DIAGNOSTICS.
In addition to expressing an exception condition in terms of its SQLSTATE value, it is possible (within a compound SQL statement) to declare a condition name to represent it.
Whenever a condition name is used, it is immediately translated into the SQLSTATE value it represents. For more information, see Declaring Condition Names.
It is possible to raise an exception without an error occurring by using the SIGNAL statement. When the SIGNAL statement is used, the specified exception condition is placed in the cleared diagnostics area, expressed as its SQLSTATE value, and control proceeds as if an error had just occurred.
Example:SIGNAL SQLSTATE 'UE456';
Exception Handlers and Actions
It is possible to declare exception handlers in a compound SQL statement that perform some action when exceptions are raised. The action defined by the exception handler is associated with one or more specific exception conditions, or one or more exception class groups, specified when the exception handler is declared. For more information, see Declaring Exception Handlers.
If there is an exception handler action defined for an exception condition that is raised, the exception handler action is performed and execution continues in the manner defined by the type of the exception handler.
If no exception handler action has been defined for an exception condition that is raised, the default error handling mechanism is invoked (which usually makes the exception condition visible to the calling environment).
If the exception NOT FOUND or an SQLWARNING is raised in an unhandled situation, execution will continue and the exception will be cleared by execution of the next statement in the procedure. The GET DIAGNOSTICS statement can be used to test for the NOT FOUND exception and an SQLWARNING.
It may be necessary for an exception handler action to re-raise the current exception condition or to raise an alternative exception condition. The RESIGNAL statement is provided for this purpose and it may only be executed from within an exception handler.
If RESIGNAL is executed without specifying an exception condition, the current exception condition remains in the diagnostics area and the error handling mechanism proceeds to deal with the error as if the current exception handler action had not been found.
If an exception condition is specified (in the same way as for SIGNAL), this is pushed onto the top of the stack of exceptions in the diagnostics area, becoming the current SQLSTATE value, and the error handling mechanism proceeds as just described.
The size of the exceptions stack in the diagnostics area is set by using the SET TRANSACTION DIAGNOSTICS SIZE statement, see Exception Diagnostics Within Transactions.
Use of RESIGNAL is useful in situations where there are nested exception handler actions defined and it is required that an enclosing exception handler action be invoked from an inner one, or where the default error handling mechanism is to be allowed to proceed from some point within a defined exception handler action.
Examples:RESIGNAL; RESIGNAL SQLSTATE 'UE456';
Declaring Condition Names
As discussed in the previous section, exception conditions are identified by an SQLSTATE value. Whenever an exception is raised, the exception condition that identifies it is stored in the diagnostics area in the form of its SQLSTATE value.
It is always possible to specify an exception condition by using its SQLSTATE value, e.g. SQLSTATE VALUE 'S0700', however it is often desirable to declare a condition name that represents the SQLSTATE value in a way that more meaningfully describes the exception.
Condition names may be declared in a compound SQL statement, see the Mimer SQL Reference Manual, Chapter 12, COMPOUND STATEMENT, for a detailed description.
Example:DECLARE invalid_parameter CONDITION FOR SQLSTATE 'UE456'; ... SIGNAL invalid_parameter;
Following this declaration, the condition name INVALID_PARAMETER can be used instead of the SQLSTATE value SQLSTATE VALUE 'UE456' whenever there is a need to refer to this exception condition.
Whenever a condition name is used (in an exception handler declaration or when signaling an exception condition) it is immediately translated into the SQLSTATE value it represents, i.e. the condition name itself is never placed in the diagnostics area or registered against an exception handler action.
All SQLSTATE values in Mimer SQL that lie outside the range of standard values are treated as implementation-defined, so all SQLSTATE values are handled in the same way and may be specified explicitly in all situations.
Declaring Exception Handlers
Exception handlers may be declared in a compound SQL statement in order to define an action which will be executed if specified exceptions are raised within the scope of the exception handler.
The structure of the handler action is the same as the body of a routine, i.e. a single executable procedural SQL statement. The exceptions to which the handler action will respond may be specified as a list of exception conditions or by specifying one or more exception class groups.
The exception class groups are:
- SQLWARNING covers SQLSTATE values beginning with 01.
- NOT FOUND covers SQLSTATE values beginning with 02.
- SQLEXCEPTION covers all other SQLSTATE values (including those in the implementation defined range), excluding those beginning with 00.
An exception handler that is declared to respond to one or more exception class groups is referred to as a general exception handler.
An exception condition may be specified by its SQLSTATE value or a condition name declared to represent it. An exception handler which is declared to respond to one or more specific exception conditions is referred to as a specific exception handler.
The same exception condition must not be specified more than once in the same exception handler declaration.
An exception handler can either be a general exception handler or a specific exception handler, i.e. an exception handler declaration cannot contain both exception class groups and specific exception conditions.
Exception handlers are declared in the local handler declaration list of a compound SQL statement and the scope of an exception handler is that compound SQL statement plus all the SQL statements contained within it, including all other compound SQL statements nested within it.
The exception handler will be executed if one of the exceptions it is declared to respond to is raised within the scope of the handler.
A local handler declaration list can only contain one exception handler declared to respond to a particular exception condition or exception class group.
It is possible to declare a general and a specific exception handler, both of which cover the same scope, where an exception condition specified for the specific handler is in one of the exception class groups specified for the general handler. If the exception condition is raised in this situation, the specific handler is executed in preference to the general handler.
It is possible for the scope of two specific exception handlers, which respond to the same exception condition, to overlap. This will be the case if there are two nested compound SQL statements and each declares a specific exception handler for the same exception condition (this is permitted, provided the two exception handlers are not declared in the same local handler declaration list). In this situation the innermost exception handler action will be executed.
The same is true for two general exception handlers in this situation.
The RESIGNAL statement can be used in situations like this, in the inner exception handler action, to get the outer exception handler action to execute by propagating the exception out from the exception handler action which is currently executing.
Types of Exception Handlers
Exception handlers fall into the following types:
- Exit Handler:
This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a LEAVE, see Scope in Routines - the Compound SQL Statement.
- Continue Handler:
This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control continues by executing the SQL statement immediately following the SQL statement that raised the exception.
- Undo Handler:
The execution of this type of handler will be initiated when the exception condition(s) that apply to it are raised. Before the handler action executes, all changes made by the executed SQL statements in the compound SQL statement, or by any SQL statements triggered by them, are canceled. The handler action is then executed and flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a LEAVE, see Scope in Routines - the Compound SQL Statement.
- Note: An UNDO exception handler can only be declared in a compound SQL statement that has been defined as ATOMIC, see The ATOMIC Compound SQL Statement.
Examples of Exception Handlerss1: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ... END; ... END s1; s2: BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... END s2; s3: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... s4: BEGIN ATOMIC DECLARE UNDO HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... END s4; ... END s3;
Using the Get DIAGNOSTICS Statement
The GET DIAGNOSTICS statement can be used in a general exception handler to get the specific SQLSTATE value that provoked execution of the exception handler.
Example:DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN DECLARE v_state CHAR(5) DEFAULT '?????'; GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE; CASE v_state WHEN '22003' THEN ... WHEN '20000' THEN ... ELSE ... END CASE; END; -- of sqlexception handler
When the GET DIAGNOSTICS statement is used inside a routine it is important to keep in mind how exceptions are handled within routines, because this affects how and where the statement can be used.
When an exception is raised inside a routine, there are two possibilities:
- An exception handler has been declared to respond to the exception, it is invoked and the diagnostics area is cleared when the exception handler finishes.
In this situation the GET DIAGNOSTICS statement can be used as the first statement in the exception handler to get the SQLSTATE value of the exception (it must be the first statement, because executing any other SQL statement will clear the diagnostics area).
- If there is no exception handler declared to handle the exception, the default error handling mechanism will immediately resignal the exception to the calling environment (typically an enclosing compound SQL statement), therefore there is no opportunity to execute GET DIAGNOSTICS directly after the statement causing the error.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40