Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Handling Errors and Exceptions


Errors may arise at three general levels in an embedded SQL (ESQL) program (not counting errors in the SQL-independent host language code). These are syntax, semantic and run-time errors.

See Managing Exception Conditions for information about managing exception conditions in routines and triggers.

Syntax Errors

Syntax errors are constructions that break the rules for formulating SQL statements. For example:

The preprocessor does not accept syntactically incorrect statements. The error must be corrected before the program can be successfully preprocessed.

Semantic Errors

Semantic errors arise when SQL statements are formulated in full accordance with the syntax rules, but do not reflect the programmer's intentions correctly.

Some semantic errors, e.g. incorrect references to database objects, are detected and reported by the ESQL preprocessor but other semantic errors will not become apparent until run-time.

Run-time Errors

Run-time errors and exception conditions (for example warnings) arising during execution of ESQL 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 SQLSTATE Return Codes.

The GET DIAGNOSTICS statement can be used to retrieve detailed information about an exception, see the Mimer SQL Reference Manual, GET DIAGNOSTICS, 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 (aka SQLCODE) and the descriptive text, respectively, relating to the exception, these are listed in Native Mimer SQL 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:

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:

      ...
       GOTO 1025
       EXEC SQL WHENEVER SQLERROR GOTO 1600
 1025  CONTINUE
       EXEC SQL DELETE FROM MYTABLE
      ...
 
Example using WHENEVER SQLERROR
 #include <stdlib.h>
 #include <wchar.h>
 
 int main()
 {
   exec sql BEGIN DECLARE SECTION;
   char sqlstate[6];
   nchar varying dbname[129];
   nchar varying dbtype[129];
   exec sql END DECLARE SECTION;
 
   exec sql WHENEVER SQLERROR GOTO get_diagn;
 
   exec sql CONNECT TO 'db' USER 'username' USING 'password';
 
   exec sql DECLARE c CURSOR FOR
            select databank_name, databank_type
            from information_schema.ext_databanks;
 
   exec sql WHENEVER NOT FOUND GOTO end_of_table;
 
   exec sql OPEN c;
 
   while (1)
   {
       exec sql FETCH c INTO :dbname, :dbtype;
       wprintf(L"Databank: %ls\nType:     %ls\n\n", dbname, dbtype);
   }
 
 end_of_table:
   exec sql CLOSE c;
 
   exec sql COMMIT;
   exec sql DISCONNECT ALL;
   exit(0); /* Exit with success */
 
 
 get_diagn:
 /* print diagnostics message(s) for the most recent statement */
   {
     exec sql BEGIN DECLARE SECTION;
     int i;
     int exceptions;
     int errcode;
     nchar varying message[255];
     exec sql END DECLARE SECTION;
 
     exec sql WHENEVER SQLERROR CONTINUE;
 
     exec sql GET DIAGNOSTICS :exceptions = NUMBER; /* How many exceptions? */
     for (i=1; i<=exceptions; i++) {
       exec sql GET DIAGNOSTICS EXCEPTION :i
         :message = MESSAGE_TEXT, :errcode = NATIVE_ERROR;
       wprintf(L"(%d)  %ls\n", errcode, message);
     }
     exec sql ROLLBACK;
     exec sql DISCONNECT;
     exit(-1); /* Error exit */
   }
 }
Example using explicit return code checking
 #include <stdlib.h>
 #include <wchar.h>
 void get_diagn();
 
 int main()
 {
   exec sql BEGIN DECLARE SECTION;
   int sqlcode;
   nchar varying dbname[129];
   nchar varying dbtype[129];
   exec sql END DECLARE SECTION;
 
   exec sql CONNECT TO 'db' USER 'username' USING 'password';
   if (sqlcode != 0) get_diagn();
 
   exec sql DECLARE c CURSOR FOR
            select databank_name, databank_type
            from information_schema.ext_databanks;
 
   exec sql OPEN c;
   if (sqlcode != 0) get_diagn();
 
   while (sqlcode == 0)
   {
       exec sql FETCH c INTO :dbname, :dbtype;
       if (sqlcode < 0) get_diagn();
       if (sqlcode != 100)
         wprintf(L"Databank: %ls\nType:     %ls\n\n", dbname, dbtype);
   }
 
   exec sql CLOSE c;
   if (sqlcode != 0) get_diagn();
 
   exec sql COMMIT;
   if (sqlcode != 0) get_diagn();
 
   exec sql DISCONNECT ALL;
   if (sqlcode != 0) get_diagn();
 
   exit(0); /* Exit with success */
 }
 
 void get_diagn()
 /* print diagnostics message(s) for the most recent statement */
 {
   exec sql BEGIN DECLARE SECTION;
   int i;
   int exceptions;
   int errcode;
   nchar varying message[255];
   int sqlcode;
   exec sql END DECLARE SECTION;
 
 
   exec sql GET DIAGNOSTICS :exceptions = NUMBER; /* How many exceptions? */
   for (i=1; i<=exceptions; i++) {
     exec sql GET DIAGNOSTICS EXCEPTION :i
         :message = MESSAGE_TEXT, :errcode = NATIVE_ERROR;
     wprintf(L"(%d)  %ls\n", errcode, message);
   }
   exec sql ROLLBACK;
   exec sql DISCONNECT;
   exit(-1); /* Error exit */
 }


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX