Run-time errors and exception conditions (for example warnings) arising during execution of embedded SQL statements are signaled by the contents of the SQLSTATE status variable described in The SQLSTATE Variable. A list of possible SQLSTATE values is provided in Return Codes.
The GET DIAGNOSTICS statement can be used to retrieve detailed information about an exception, see the Mimer SQL Reference Manual for the syntax description.
The NATIVE_ERROR and MESSAGE_TEXT fields of the diagnostics area retrieved by using GET DIAGNOSTICS are used to get the internal Mimer SQL return code and the descriptive text, respectively, relating to the exception, these are listed in Return Codes.
Testing for Run-time Errors and Exception Conditions
The application program may test the outcome of a statement in one of two ways:
- by explicitly testing the content of the SQLSTATE variable
- by using the SQL statement WHENEVER, see the Mimer SQL Reference Manual for the syntax description), which tests the class of the SQLSTATE variable.
An application program may contain any number of WHENEVER statements, and the statements may be placed anywhere in the program. A separate WHENEVER statement must be issued for each situation (NOT FOUND, SQLERROR or SQLWARNING) which is to be tested. When an exception condition arises, action will be taken as specified in the WHENEVER statement most recently encountered in the code, for the respective condition.
WHENEVER statements are expanded by the preprocessor into explicit tests. These tests are placed after every subsequent SQL statement in that program until a new WHENEVER statement is issued for the same condition.
Two important consequences follow:
- WHENEVER statements are preprocessed strictly in the order in which they appear in the source code, regardless of execution order or conditional execution that the source code might imply.... GOTO 1025 EXEC SQL WHENEVER SQLERROR GOTO 1600 1025 CONTINUE EXEC SQL DELETE FROM MYTABLE ...
- Mixing explicit tests and WHENEVER statements requires care. As a general rule, it is advisable to use either hand-written tests or WHENEVER statements in a program module, and to avoid mixing them.
The condition handling defined by a WHENEVER statement applies to the SQL statements that follow it in the source code. If a "GOTO" action is defined, the pre-processor inserts an exception test and action directly after each SQL statement affected by it and thus before any hand-written tests in the source code. The hand-written test in this situation would never be executed.
If "CONTINUE" is specified in a WHENEVER statement, the pre-processor does not insert an exception test and action, thus no exception handling is defined by the WHENEVER statement. Any hand-written tests present in the source code will then take effect.
The interchange between hand-written exception handling and the implicit exception handling inserted by the pre-processor (or not) can be confusing. It is therefore advisable to make a clear coding decision to use one method or the other.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40