Managing Exception Conditions

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.

About SQLSTATES

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 the GET DIAGNOSTICS statement.

Condition Names

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.

SIGNAL Statements

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.

It possible to return specific error messages with the SIGNAL statement by using the optional SET clause.

Example:
 SIGNAL SQLSTATE 'UE456'
     SET message_text = 'The specified horse, ' || horse ||
         ' does not exist in the database';
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.

RESIGNAL Statements

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. As with the SIGNAL statement it is possible to supply a specific message text.

Examples:
 RESIGNAL;
 
 RESIGNAL SQLSTATE 'UE456'
     SET message_text = 'The horse ' || horse || ' does not exist';
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, 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.

If a condition name is used in a signal statement the associated SQLSTATE value and the condition name is placed in the diagnostics area. If the condition does not have an associated SQLSTATE value, the SQLSTATE value 45000 is used. A condition is always local to a routine, i.e. consider the following example:
 create procedure p2()
 begin
     declare condition c1;
     ...
     signal c1;
 end
 
 create procedure p1()
 begin
     declare condition c1;
     declare exit handler for c1
     begin
         ...
         call p2();
         ...
     end
 end
 
In this case the exit handler in the procedure p1 will not be invoked when the statement signal c1 is executed.In order to catch a signaled condition the associated SQLSTATE must be used. The condition identifier can be propagated by using a RESIGNAL statement.

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 except when another routine is invoked. When a user defined routine is invoked all exception handlers in the calling routine will get out of scope and they will get into scope again when the invoked routine has finished executing, e.g:
 CREATE PROCEDURE innerMost(INT x)
 BEGIN
     -- no handlers in this routine
     IF  x > 0 THEN
         SIGNAL SQLSTATE 'UE345';
     ELSE
         SIGNAL SQLSTATE 'UE543';
     END IF;
 END
 
 CREATE PROCEDURE outerMost()
 BEGIN
     DECLARE EXIT HANDLER FOR SQLSTATE 'UE345' BEGIN END;
     DECLARE CONTINUE HANDLER FOR SQLSTATE 'UE543' BEGIN END;
     CALL innerMost(0);
     CALL innerMost(1);
     CALL innerMost(2);
 END
 
When the signal statement with exception UE543 in the innerMost routine is executed, the execution of this routine will be stopped as there is no handlers declared. The exception will the be propagated to the outerMost routine which has a continue handler for this exception. This means that the execution will proceed with the next call statement. This will cause a new exception (UE345) being signalled. Again this exception will be propagated to the calling routine and the first exception handler will be invoked. As this is an exit handler the execution will continue after the end of the compound statement in the outerMost routine, i.e. the statement call innerMost(2) will never be executed.

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 Handlers
 s1:
     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 an 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 RESIGNAL;
      END CASE;
  END;  -- of sqlexception handler
 
Note that GET DIAGNOSTICS must be the first statement in the exception handler as the diagnostics area always contains information about the latest statement.

The GET DIAGNOSTICS statement can also be used to get information about warnings and not found exceptions.

Example:
 SELECT format into v_format FROM formats where category_id = v_category_id;
 GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE;
 IF  v_state = '02000' THEN
 -- not found
 ELSE
 -- found
 END IF;
 
It is not meaningful to use the GET DIAGNOSTICS statement to check for errors after a statement.

Example:
 BEGIN
     DECLARE v_state CHAR(5);
     DECLARE EXIT HANDLER FOR sqlexception
     BEGIN
         ...
     END;
     INSERT INTO format(format,category_id) VALUES (v_format,v_category_id);
     GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE;
 END
 
If an exception occurs in the insert statement this will be catched by the exception handler and as this is an exit handler the execution will resume after the compound statement. Thus, the diagnostics statements will never be invoked in this case. Even if it is a continue handler the get diagnostics statement is superfluous as the handler would clear the diagnostics information.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se