Guide to the exp_chk.ec file

The exp_chk.ec file contains exception-handling functions for the demonstration programs.

These functions support the following two types of exception handling:

A function that a WHENEVER SQLERROR CALL statement specifies performs exception handling.
Functions to support this type of exception handling include whenexp_chk(), sqlstate_err(), and disp_sqlstate_err(). The getdiag sample program in this chapter uses this form of exception handling.
A function that the program calls explicitly after each SQL statement performs exception handling.
Functions to support this type of exception handling include exp_chk(), exp_chk2(), sqlstate_err(), disp_sqlstate_err(), and disp_exception(). The dispcat_pic sample program (Simple large objects) uses exp_chk2() while the dyn_sql sample program (A system-descriptor area) uses exp_chk() to perform exception handling.

To obtain exception information, the preceding functions use the SQLSTATE variable and the GET DIAGNOSTICS statement. They use SQLCODE only when they need information specific to HCL OneDB™.

=======================================================================
1. EXEC SQL define SUCCESS 0;
2. EXEC SQL define WARNING 1;
3. EXEC SQL define NODATA  100;
4. EXEC SQL define RTERROR -1;
5. char statement[80];
6. /*
7.  * The sqlstate_err() function checks the SQLSTATE status variable 
    * to see
8.  * if an error or warning has occurred following an SQL statement.
9.  */
10. int4 sqlstate_err()
11. {
12.    int4 err_code = RTERROR;
13.    if(SQLSTATE[0] == '0') /* trap '00', '01', '02' */
14.       {
15.       switch(SQLSTATE[1])
16.          {
17.          case '0': /* success - return 0 */
18.             err_code = SUCCESS;
19.             break;
20.          case '1': /* warning - return 1 */
21.             err_code = WARNING;
22.             break;
23.          case '2': /* end of data - return 100 */
24.             err_code = NODATA;
25.             break;
26.          default: /* error - return -1*/
27.             break;
28.          }
29.       }
30.    return(err_code);
31. }
=======================================================================

Lines 1 - 4

These define directives create definitions for the success, warning, NOT FOUND, and runtime error exceptions. Several functions in this file use these definitions instead of constants to determine actions to take for a given type of exception.

Line 5

The statement variable is a global variable that the calling program (which declares it as extern) sets to the name of the most-recent SQL statement.

The whenexp_chk() function displays the SQL statement name as part of the error information (see lines 85 and 92).

Lines 6 - 31

The sqlstate_err() function returns a status of 0, 1, 100, or -1 to indicate if the current exception in SQLSTATE is a success, warning, NOT FOUND, or runtime error. The sqlstate_err() function checks the first two characters of the global SQLSTATE variable. Because automatically declares the SQLSTATE variable, the function does not need to declare it.

Line 13 checks the first character of the global SQLSTATE variable. This character determines whether the most-recently executed SQL statement has generated a non-error condition. Non-error conditions include the NOT FOUND condition (or END OF DATA), success, and warnings. Line 15 checks the second character of the global SQLSTATE variable (SQLSTATE[1]) to determine the type of non-error condition generated.

The sqlstate_err() function sets err_code to indicate the exception status as follows:

Lines 17 - 19: If SQLSTATE has a class code of "00", the most-recently executed SQL statement was successful. The sqlstate_err() function returns 0 (which line 1 defines as SUCCESS).
Lines 20 - 22: If SQLSTATE has a class code of "01", the most-recently executed SQL statement generated a warning. The sqlstate_err() function returns 1 (which line 2 defines as WARNING).
Lines 23 - 25: If SQLSTATE has a class code of "02", the most-recently executed SQL statement generated the NOT FOUND (or END OF DATA) condition. The sqlstate_err() function returns 100 (which line 3 defines as NODATA).

If SQLSTATE[1] contains any character other than '0', '1', or '2', then the most-recently executed SQL statement generated a runtime error. SQLSTATE also indicates a runtime error if SQLSTATE[0] contains some character other than '0'. In either case, line 30 returns a negative one (-1) (which line 4 defines as RTERROR).

=======================================================================
32. /*
33.  * The disp_sqlstate_err() function executes the GET DIAGNOSTICS
34.  * statement and prints the detail for each exception that is
35.  * returned.
36.  */
37. void disp_sqlstate_err()
38. {
39. mint j;
40. EXEC SQL BEGIN DECLARE SECTION;
41.     mint exception_count;
42.     char overflow[2];
43.     int exception_num=1;
44.     char class_id[255];
45.     char subclass_id[255];
46.     char message[8191];
47.     mint messlen;
48.     char sqlstate_code[6];
49.     mint i;
50. EXEC SQL END DECLARE SECTION;
51.     printf("---------------------------------");
52.     printf("-------------------------\n");
53.     printf("SQLSTATE: %s\n",SQLSTATE);
54.     printf("SQLCODE: %d\n", SQLCODE);
55.     printf("\n");
56.    EXEC SQL get diagnostics :exception_count = NUMBER,
57.         :overflow = MORE;
58.     printf("EXCEPTIONS:  Number=%d\t", exception_count);
59.     printf("More? %s\n", overflow);
60.     for (i = 1; i <= exception_count; i++)
61.       {
62.       EXEC SQL get diagnostics  exception :i
63.          :sqlstate_code = RETURNED_SQLSTATE,
64.          :class_id = CLASS_ORIGIN, :subclass_id = SUBCLASS_ORIGIN,
65.          :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;
66.       printf("- - - - - - - - - - - - - - - - - - - -\n");
67.       printf("EXCEPTION %d: SQLSTATE=%s\n", i,
68.          sqlstate_code);
69.       message[messlen-1] = '\0';
70.       printf("MESSAGE TEXT: %s\n", message);
71.       j = byleng(class_id, stleng(class_id));
72.       class_id[j] = '\0';
73.       printf("CLASS ORIGIN: %s\n",class_id);
74.       j = byleng(subclass_id, stleng(subclass_id));
75.       subclass_id[j] = '\0';
76.       printf("SUBCLASS ORIGIN: %s\n",subclass_id);
77.       }
78.    printf("---------------------------------");
79.    printf("-------------------------\n");
80. }
=======================================================================

Lines 32 - 80

The disp_sqlstate_err() function uses the GET DIAGNOSTICS statement to obtain diagnostic information about the most-recently executed SQL statement.

Lines 40 - 50 declare the host variables that receive the diagnostic information. The GET DIAGNOSTICS statement copies information from the diagnostics area into these host variables. Line 48 includes a declaration for the SQLSTATE value (called sqlstate_code) because the disp_sqlstate_err() function handles multiple exceptions. The sqlstate_code variable holds the SQLSTATE value for each exception.

Lines 53 - 55 display the values of the SQLSTATE and SQLCODE variables. If SQLSTATE contains "IX000" (an error specific to HCL OneDB), SQLCODE contains the error code that is specific toHCL OneDB.

The first GET DIAGNOSTICS statement (lines 56 and 57) stores the statement information in the :exception_count and :overflow host variables. Lines 58 and 59 then display this information.

The for loop (lines 60 - 77) executes for each exception that the most-recently executed SQL statement has generated. The :exception_count host variable, which holds the number of exceptions, determines the number of iterations that this loop performs.

The second GET DIAGNOSTICS statement (lines 62 - 65) obtains the exception information for a single exception. Lines 67 - 70 print the SQLSTATE value (sqlstate_code) and its corresponding message text. In addition to SQL error messages, disp_sqlstate_err() can display ISAM error messages because the MESSAGE_TEXT field of the diagnostics area also contains these messages. The function uses the MESSAGE_LENGTH value to determine where to place a null terminator in the message string. This action causes only the portion of the message variable that contains text to be output (rather than the full 255-character buffer).

Declare both the class- and the subclass-origin host variables as character buffers of size 255. However, often the text for these variables fills only a small portion of the buffer. Rather than display the full buffer, lines 71 - 73 use the byleng() and stleng() library functions to display only that portion of :class_id that contains text; lines 74 - 76 perform this same task for :subclass_id.

=======================================================================
81. void disp_error(stmt)
82. char *stmt;
83. {
84.    printf("\n********Error encountered in %s********\n",
85.       stmt);
86.    disp_sqlstate_err();
87. }
88. void disp_warning(stmt)
89. char *stmt;
90. {
91.    printf("\n********Warning encountered in %s********\n",
92.       stmt);
93.    disp_sqlstate_err();
94. }
95. void disp_exception(stmt, sqlerr_code, warn_flg)
96. char *stmt;
97. int4 sqlerr_code;
98. mint warn_flg;
99. {
100.    switch(sqlerr_code)
101.       {
102.       case SUCCESS:
103.       case NODATA:
104.          break;
105.       case WARNING:
106.          if(warn_flg)
107.             disp_warning(stmt);
108.          break;
109.       case RTERROR:
110.          disp_error(stmt);
111.          break;
112.       default:
113.          printf("\n********INVALID EXCEPTION STATE for 
     %s********\n",
114.             stmt);
115. /*         break;
116.       }
117. }
=======================================================================

Lines 81 - 87

The disp_error() function notifies the user of a runtime error. It calls the disp_sqlstate_err() function (line 86) to display the diagnostic information.

Lines 88 - 94

The disp_warning() function notifies the user of a warning. It calls the disp_sqlstate_err() function (line 93) to display the diagnostic information.

Lines 95 - 117

The disp_exception() function handles the display of the exception information. It expects the following three arguments:

stmt: The name of the most-recently executed SQL statement.
sqlerr_code: The code that sqlstate_err() returns to indicate the type of exception encountered.
warn_flg: A flag to indicate whether to display the diagnostic information for a warning.

Lines 102 - 104 handle the SUCCESS and NOData conditions. For either of these cases, the function displays no diagnostic information. Lines 105 - 108 notify the user that a warning has occurred. The function checks the warn_flg argument to determine whether to call the disp_warning() function to display warning information for the most-recently executed SQL statement (lines 137 - 142). Lines 109 - 111 notify the user that a runtime error has occurred. The disp_err() function actually handles display of the diagnostic information.

=======================================================================
118.  * The exp_chk() function calls sqlstate_err() to check the SQLSTATE
119.  * status variable to see if an error or warning has occurred 
      * following
120.  * an SQL statement. If either condition has occurred, exp_chk()
121.  * calls disp_sqlstate_err() to print the detailed error 
      * information.
122.  *
123.  * This function handles exceptions as follows:
124.  *    runtime errors - call exit()
125.  *    warnings - continue execution, returning "1"
126.  *    success - continue execution, returning "0"
127.  *    Not Found - continue execution, returning "100"
128.  */
129. long exp_chk(stmt, warn_flg)
130. char *stmt;
131. int warn_flg;
132.  {
133.    int4 sqlerr_code = SUCCESS;
134.    sqlerr_code = sqlstate_err();
135.    disp_exception(stmt, sqlerr_code, warn_flg);
136.    if(sqlerr_code == RTERROR)    /* Exception is a runtime error */
137.    {
138.       /* Exit the program after examining the error */
139.       printf("********Program terminated********\n\n");
140.       exit(1);
141.    }
142. /*   else                  /* Exception is "success", "Not Found", */
143 .      return(sqlerr_code);   /* or "warning" */
144. }
=======================================================================

Lines 118 - 144

The exp_chk() function is one of three wrapper functions that handle exceptions. It analyzes the SQLSTATE value to determine the success or failure of the most-recent SQL statement. This function is called explicitly after each SQL statement. This design requires the following features:

The exp_chk() function passes as an argument the name of the SQL statement that generated the exception.
Because the WHENEVER statement does not invoke the function, the function is not restricted to using a global variable.
The exp_chk() function returns a value in the event of a successful execution of the SQL statement (0), the NOT FOUND condition (100), or a warning (1).
Because the calling program explicitly calls exp_chk(), the calling program can handle the return value.
The exp_chk() function uses a flag argument (warn_flg) to indicate whether to display warning information to the user.
Because warnings can indicate non-serious errors and, after a CONNECT, can be informational, displaying warning information can be both distracting and unnecessary to the user. The warn_flg argument allows the calling program to determine whether to display warning information that SQL statements might generate.

The sqlstate_err() function (line 134) determines the type of exception that SQLSTATE contains. The function then calls disp_exception() (line 135) and passes the warn_flg argument to indicate whether to display warning information. To handle a runtime error, the sqlstate_err() function calls the exit() system function (lines 136 - 141) to terminate the program. This behavior is the same as what the whenexp_chk() function (see lines 170 - 214) provides for runtime errors.

The dyn_sql sample program also uses exp_chk() to handle exceptions.

=======================================================================
145.  * The exp_chk2() function calls sqlstate_err() to check the 
      * SQLSTATE
146.  * status variable to see if an error or warning has occurred 
      * following
147.  * an SQL statement. If either condition has occurred, exp_chk2()
148.  * calls disp_sqlstate_err() to print the detailed error 
      * information.
149.  *
150.  * This function handles exceptions as follows:
151.  *    runtime errors - continue execution, returning SQLCODE (<0)
152.  *    warnings - continue execution, returning one (1)
153.  *    success - continue execution, returning zero (0)
154.  *    Not Found - continue execution, returning 100
155.  */
156. int4 exp_chk2(stmt, warn_flg)
157. char *stmt;
158. mint warn_flg;
159.  {
160.    int4 sqlerr_code = SUCCESS;
161.    int4 sqlcode;
162.    sqlcode = SQLCODE;    /* save SQLCODE in case of error */
163.    sqlerr_code = sqlstate_err();
164.    disp_exception(stmt, sqlerr_code, warn_flg);
165.    if(sqlerr_code == RTERROR)
166. /*      sqlerr_code = sqlcode;
167.    return(sqlerr_code);
168. }
=======================================================================

Lines 145 - 168

The exp_chk2() function is the second of the three exception-handling wrapper functions in the exp_chk.ec file. It performs the same basic task as the exp_chk() function. Both functions are called after each SQL statement and both return a status code. The only difference between the two is in the way they respond to runtime errors. The exp_chk() function calls exit() to terminate the program (line 140), while the exp_chk2() function returns the SQLCODE value to the calling program (lines 165 - 166).

The exp_chk2() function returns SQLCODE rather than SQLSTATE to allow the program to check for particular error codes that are specific to HCL OneDB. A possible enhancement might be to return both the SQLSTATE and SQLCODE values.

The dyn_sql sample program also uses exp_chk2() to handle exceptions.

=======================================================================
169.    *
170. * The whenexp_chk() function calls sqlstate_err() to check the 
     * SQLSTATE
171.  * status variable to see if an error or warning has occurred 
      * following
172.  * an SQL statement. If either condition has occurred, whenerr_chk() 
173.  * calls disp_sqlstate_err() to print the detailed error 
      * information.
174.  *
175.  * This function is expected to be used with the WHENEVER SQLERROR
176.  * statement: it executes an exit(1) when it encounters a negative
177.  * error code. It also assumes the presence of the "statement" 
      * global
178.  * variable, set by the calling program to the name of the statement
179.  * encountering the error.
180. */
181. whenexp_chk()
182. {
183.    int4 sqlerr_code = SUCCESS;
184.    mint disp = 0;
185.    sqlerr_code = sqlstate_err();
186.    if(sqlerr_code == WARNING)
187.       {
188.         disp = 1;
189.         printf("\n********Warning encountered in %s********\n",
190.             statement);
191.       }
192.    else
193.         if(sqlerr_code == RTERROR)
194.         {
195.             printf("\n********Error encountered in %s********\n",
196.                 statement);
197.             disp = 1;
198.         }
199.    if(disp)
200.         disp_sqlstate_err();
201.    if(sqlerr_code == RTERROR)
202.       {
203.         /* Exit the program after examining the error */
204.         printf("********Program terminated*******\n\n");
205.         exit(1);
206.       }
207.    else
208.       {
209.      if(sqlerr_code == WARNING)
210.          printf("\n********Program execution 
     continues********\n\n");
211.      return(sqlerr_code);
212.       }
213. }
=======================================================================

Lines 169 - 213

The whenexp_chk() function is the third exception-handling wrapper function in the exp_chk.ec file. It too analyzes the SQLSTATE values and uses the GET DIAGNOSTICS statement for exception handling. However, this function is called with the following WHENEVER statements:

EXEC SQL whenever sqlerror call whenexp_chk;
EXEC SQL whenever sqlwarning call whenexp_chk;

The WHENEVER statement imposes the following restrictions on the design of the whenexp_chk() function:

The whenexp_chk() function cannot receive arguments; therefore, the function uses a global variable, statement, to identify the SQL statement that generated the exception (lines 190 and 196).
To use arguments with the whenexp_chk() function, you can use the GOTO clause of the WHENEVER statement.
```
EXEC SQL whenever sqlerror goto :excpt_hndlng;
```
where the label :excpt_hndlng would have the following code:
```
:excpt_hndlng
   whenexp_chk(statement);
```
The whenexp_chk() function cannot return any value; therefore, it cannot return the particular exception code to the main program.
For this reason, whenexp_chk() handles runtime errors instead of the main program; whenexp_chk() calls the exit() function when it encounters a runtime error. To have the main program access the error code, you can modify whenexp_chk() to set a global variable.

The getdiag sample program, which this chapter describes, uses whenexp_chk() to handle exceptions. See lines 11 and 12 of the getdiag.ec file in Guide to the getdiag.ec file.

The sqlstate_err() function (line 185) returns an integer that indicates the success of the most-recently executed SQL statement. This return value is based on the SQLSTATE value.

Lines 186 - 198 display a special line to bring attention to the exception information that was generated. The disp variable is a flag that indicates whether to display exception information. The function displays exception information for warnings (WARNING) and runtime errors (RTERROR) but not for other exception conditions. The calls to the printf() function (lines 189 and 195) display the name of the SQL statement that generated the warning or error. A global variable (called statement) must store this statement name because the function cannot receive it as an argument.

The disp_sqlstate_err() function (lines 199 and 200) displays the information that the diagnostics area contains only if SQLSTATE indicates a warning or a runtime error (disp = 1).

Lines 201 - 206 handle a runtime error. They notify the user of the program termination and then use the exit() system call (line 205) to terminate the program. The call to the disp_sqlstate_err() function (line 200) has already displayed information about the cause of the runtime error.