SESAM/SQL provides diagnostic information in routines. The SQL standard uses the term “diagnostics management” for this.
Diagnostic information is provided in a diagnostics area for an SQL statement executed beforehand. In the case of routines in SESAM/SQL, multiple diagnostics areas can exist at one time (for an SQL statement, for calling an (exception) routine), in particular for nested routines.
The following SQL statements, which may only be used in routines, enable a diagnostics area to be accessed in read and/or write mode:
SQL statement | Function | see |
GET DIAGNOSTICS | Outputs diagnostic information about a statement | |
SIGNAL | Reports exception in routine | |
RESIGNAL | Reports exception in local exception routine |
Table 29: Control and diagnostic statements of routines
You can improve the programming of routines using these diagnostic statements and the self-defined SQLSTATEs described below. You can analyze exceptions which occur more precisely and respond to these in a differentiated manner.
Success of an SQL statement in a routine
To simplify the description, the success of an SQL statement in a routine is defined as follows in this manual:
The SQL statement was successful if it was terminated with SQLSTATE '00000'.
The SQL statement was error-free if it was terminated with SQLSTATE '00000', an SQLSTATE of the classes '01xxx' (warning) or '02xxx' (no data).
The SQL statement in a routine was errored if it was not terminated error-free.
Self-defined SQLSTATEs
SESAM/SQL V9.0 and higher enables you to define SQLSTATEs yourself. The class '46Sxx' (where x is a number or an uppercase letter) is reserved. In this class you can define up to 1296 SQLSTATEs yourself. This class is used neither by the SQL standard nor by SESAM/SQL.
You can specify self-defined SQLSTATEs in the diagnostic statements SIGNAL and RESIGNAL.You can call a specific exception routine on a targeted basis in the SIGNAL diagnostic routine using a self-defined SQLSTATE. In the exception routine you can use the RESIGNAL diagnostic statements to abort the routine specifically. In both statements you can also enter additional diagnostic information in the diagnostics area.
There are no ready-made SESAM message texts for self-defined SQLSTATEs. When a self-defined SQLSTATE occurs in the application program as an unspecified SQLSTATE, SESAM/SQL generates the message SEW46xx (&00) from it. The MESSAGE_TEXT from the diagnostics area then appears as insert (&00). This enables you to generate a message text of your own (without an accompanying help text) indirectly in the diagnostic statements SIGNAL and RESIGNAL.
SQLSTATE '45000' (unhandeled SQLSTATE)
With SESAM/SQL you can define a local exception name for an SQLSTATE in a COMPOUND statement, see section "Local data".
However, you can also define an exception name with no link to an SQLSTATE.
With this exception name you can call a specific exception routine in the SIGNAL diagnostic routine. If this exception routine does not exist or is exited with RESIGNAL (without specifying an SQLSTATE), the routine is terminated with the SQLSTATE '45000'.
SESAM/SQL then generates the following message:
SEW4500 UNHANDLED USER DEFINED EXCEPTION (&00). (&01)
Insert (&00) contains the exception name. If a MESSAGE_TEXT was specified for SIGNAL or RESIGNAL, (&01) appears as an insert.
When an appropriate exception name and possibly a corresponding MESSAGE_TEXT is selected, the user then receives an informative message.
GET DIAGNOSTICS
GET DIAGNOSTICS ascertains information on an SQL statement executed beforehand in a routine and enters this in a procedure parameter (output) or a local variable. The information relates to the statement itself or to the database objects affected by it.
GET DIAGNOSTICS changes neither the content nor the sequence of diagnostics areas. In other words GET DIAGNOSTICS statements which follow each other directly evaluate the same diagnostic information.
A detailed description of the GET DIAGNOSTICS statement is provided on "GET DIAGNOSTICS - Output diagnostic information".
SIGNAL
SIGNAL reports, in a routine, am exception or a self-defined SQLSTATE.
A detailed description of the SIGNAL statement is provided on "SIGNAL - Report exception in routine".
SIGNAL deletes the current diagnostics area and optionally enters the following diagnostic information into the current diagnostics area:
When an exception name is specified, it is entered as CONDITION_IDENTIFIER. Otherwise a string with the length 0 is assigned.
The RETURNED_SQLSTATE is supplied:
When an SQLSTATE is specified, it is entered as RETURNED_SQLSTATE.
When an SQLSTATE is defined for the specified exception name, the defined SQLSTATE is entered for RETURNED_SQLSTATE.
Otherwise SQLSTATE '45000' is entered.
When MESSAGE_TEXT is specified, MESSAGE_TEXT, MESSAGE_LENGTH, and MESSAGE_OCTET_LENGTH are supplied accordingly. Otherwise MESSAGE_TEXT is assigned a string with the length 0.
The routine is continued or terminated with an exception routine:
When RETURNED_SQLSTATE ≠ '45000' and a local exception routine is defined for the RETURNED_SQLSTATE, this exception routine is executed.
When RETURNED_SQLSTATE
='45000' and a local exception routine is defined for the exception name entered CONDITION_IDENTIFIER, this exception routine is executed.Otherwise an unspecified SQLSTATE exists. The routine is terminated with the SQLSTATE entered in RETURNED_SQLSTATE.
Further information:
Execution of a specific exception routine can be achieved with SIGNAL.
An SQL statement immediately after the SIGNAL statement is then executed only if the exception routine called by SIGNAL is defined with CONTINUE and was terminated without error.
If the values (e.g. MESSAGE_TEXT) entered in the diagnostics area for SIGNAL are to be read, GET CURRENT DIAGNOSTICS must be located either immediately after SIGNAL (see preceding note) or it must be used in the exception routine GET STACKED DIAGNOSTICS which is called. This exception routine need not necessarily be part of the current COMPOUND statement. It can also be an exception routine of a higher-ranking routine which has used the routine with the SIGNAL statement. In the latter case, the diagnostics area of the calling statement is then evaluated.
A routine is continued after an SQL statement which is error-free but not successful. Even if an exception routine was executed with EXIT or UNDO in such a case, the routine terminates with SQLSTATE '00000’ unless an SQL statement terminated with an error in the exception routine itself. In such a case, the SIGNAL statement enables the routine to be terminated with a self-defined SQLSTATE.
RESIGNAL
RESIGNAL reports a condition or an SQLSTATE in a local exception routine. In contrast to SIGNAL, the specification of an exception name or SQLSTATE is optional.
A detailed description of the RESIGNAL statement is provided on "RESIGNAL - Report exception in local exception routine".
RESIGNAL uses the diagnostics area of the SQL statement which has activated the exception routine, and if necessary modifies the following diagnostic information:
If neither an exception name nor SQLSTATE was specified, CONDITION_IDENTIFIER and RETURNED_SQLSTATE remain unchanged. The following applies:
RETURNED_SQLSTATE may not contain an SQLSTATE of class '01xxx' or '02xxx'. Otherwise RESIGNAL is terminated with an error.
When MESSAGE_TEXT= is specified, RETURNED_SQLSTATE must contain either a self-defined SQLSTATE or the value '45000'. Otherwise RESIGNAL is terminated with an error.
The current diagnostics area will possibly be modified:
When an exception name is specified, it is entered as CONDITION_IDENTIFIER. Otherwise a string with the length 0 is assigned.
When an SQLSTATE is specified, it is entered as RETURNED_SQLSTATE.
When an SQLSTATE is defined for the specified exception name, the defined SQLSTATE is entered for RETURNED_SQLSTATE. Otherwise SQLSTATE '45000' is entered.
When MESSAGE_TEXT is specified, MESSAGE_TEXT, MESSAGE_LENGTH, and MESSAGE_OCTET_LENGTH are supplied accordingly. Otherwise MESSAGE_TEXT is assigned a string with the length 0.
The routine in which the local exception routine of the RESIGNAL statement was executed is terminated with the SQLSTATE entered in RETURNED_SQLSTATE.
Further information:
Even after an exception routine defined with EXIT or UNDO has been executed, a routine is terminated with SQLSTATE '00000’ unless an SQL statement terminated with an error in the exception routine itself. RESIGNAL enables you to return the SQLSTATE which triggered the exception routine.
A SIGNAL statement which is called in an exception routine has the same effect as a RESIGNAL statement with explicitly specified exception name or SQLSTATE.
Examples of the use of diagnostic statements
Different situations when querying the SQLSTATE
CREATE PROCEDURE proc1() MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE state1, state2, state3 CHAR(5);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DELETE FROM tab1; ------------------------------------------- (3)
GET STACKED DIAGNOSTICS CONDITION state2 = RETURNED_SQLSTATE;
GET CURRENT DIAGNOSTICS CONDITION state3 = RETURNED_SQLSTATE;
... --------------------------------------------------------- (2)
END;
...
UPDATE tab2 SET ...;
GET CURRENT DIAGNOSTICS CONDITION state1 = RETURNED_SQLSTATE; ----- (1)
...
END
(1) | The local variable |
(2) | If the UPDATE statement was executed with an error and the DELETE statement was executed without an error, |
(3) | If the DELETE statement of the exception routine was also executed with an error, the procedure is immediately aborted because of the unspecified SQLSTATE. None of the GET DIAGNOSTICS statements is executed. |
If the exception routine is defined with CONTINUE (instead of with EXIT) and is executed without error, state1 is also supplied after an UPDATE statement which was executed with an error. state1 is then assigned the SQLSTATE of the UPDATE statement which caused the error.
Special handling of the SQLSTATE '02000'
After SQLSTATE '02000' (no data), a routine is normally continued. In the example below, this is accepted in one case and is intended to lead to an error in another.
CREATE PROCEDURE proc2(OUT par1 INTEGER, OUT par2 INTEGER) MODIFIES SQL DATA
BEGIN ATOMIC
DELETE FROM tab1;
GET DIAGNOSTICS par1 = ROW_COUNT;
DELETE FROM tab2;
GET DIAGNOSTICS par2 = ROW_COUNT;
IF par2 = 0
THEN SIGNAL SQLSTATE '46SA1'
SET MESSAGE_TEXT = 'tab2 must contain at least one record';
END IF;
END
If the DELETE statement was executed without error, the relevant number of deleted records is entered in the two output parameters. In table tab1, the number may also be 0. However, when table tab2 is empty, the procedure is aborted. Because of the ATOMIC clause, the deletions in table tab1 are also undone. SESAM/SQL generates the message:
SEW46A1 TAB2 MUST CONTAIN AT LEAST ONE RECORD
Noting the SQLSTATE which occurred
After an unspecified SQLSTATE, a procedure is aborted and precisely this SQLSTATE is reported. If you also wish to log this event in a table, define, for example, the following exception routine. The RESIGNAL statement returns the SQLSTATE which occurred. Without the RESIGNAL statement, the procedure terminates with SQLSTATE '00000'.
CREATE PROCEDURE proc3() MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE error CHAR(5);
DECLARE UNDO HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION error = RETURNED_SQLSTATE;
INSERT INTO logging_tab
VALUES (CURRENT_TIMESTAMP(3),'SQLSTATE ' || error || ' occurred');
RESIGNAL;
END;
-- procedure body
...
END
Search for empty tables
The number of empty tables is to be determined by means of a User Defined Function. If the number of empty tables exceeds the number entered, the search should be aborted with an error.
CREATE FUNCTION check_tables(IN max_nbr INTEGER)
RETURNS INTEGER READS SQL DATA
BEGIN
DECLARE "TABLE ERROR" CONDITION;
DECLARE nbr_empty_tables integer DEFAULT 0;
DECLARE CONTINUE HANDLER FOR "TABLE ERROR"
BEGIN
nbr_empty_tables = nbr_empty_tables + 1;
IF nbr_empty_tables > max_nbr
THEN RESIGNAL SET MESSAGE_TEXT = 'TOO MANY EMPTY TABLES';
END IF;
END;
IF (SELECT COUNT(*) FROM tab1) = 0 THEN SIGNAL "TABLE ERROR";
END IF;
IF (SELECT COUNT(*) FROM tab2) = 0 THEN SIGNAL "TABLE ERROR";
END IF;
IF (SELECT COUNT(*) FROM tab3) = 0 THEN SIGNAL "TABLE ERROR";
END IF;
RETURN nbr_empty_tables;
END
SELECT check_tables(2) INTO :NBR-EMPTY-TABLES FROM TABLE(DEE)
If the number of empty tables does not exceed the number entered, the number of empty tables is stored in the user variable :NBR-EMPTY-TABLES.
However, if more than two tables exist, the search is terminated with SQLSTATE '45000'. SESAM/SQL then generates the following message:
SEW4500 UNHANDLED USER DEFINED EXCEPTION (TABLE ERROR). TOO MANY EMPTY TABLES