A Program That Uses Exception Handling
The getdiag.ec program contains exception handling on each of the SQL statements that the program executes. This program is a modified version of the demo1.ec program, which Chapter 1 of this manual explains. The version that this section lists and describes uses the following exception-handling methods:
The whenexp_chk() function displays the error number and the accompanying ISAM error, if one exists. The exp_chk.ec source file contains this function and its exception-handling functions. The getdiag.ec source file (page 11-57) includes the exp_chk.ec file (page 11-60).
Compiling the Program
Use the following command to compile the getdiag program:
The -o getdiag option tells esql to name the executable program getdiag. Without the -o option, the name of the executable program defaults to a.out. For more information on the esql preprocessor command, see "Using the esql Command".
Guide to the getdiag.ec File
This section describes only the exception-handling statements. For information about the other statements, refer to "A Sample INFORMIX-ESQL/C Program" in Chapter 1 of this manual.
1 #include <stdio.h>
2 EXEC SQL define FNAME_LEN 15;
3 EXEC SQL define LNAME_LEN 15;
4 extern char statement[20];
5 main()
6 {
7 EXEC SQL BEGIN DECLARE SECTION;
8 char fname[ FNAME_LEN + 1 ];
9 char lname[ LNAME_LEN + 1 ];
10 EXEC SQL END DECLARE SECTION;
11 EXEC SQL whenever sqlerror CALL whenexp_chk;
12 EXEC SQL whenever sqlwarning CALL whenexp_chk;
13 printf("GETDIAG Sample ESQL program running.\n\n");
14 strcpy (statement, "CONNECT stmt");
15 EXEC SQL connect to 'stores7';
16 strcpy (statement, "DECLARE stmt");
17 EXEC SQL declare democursor cursor for
18 select fname, lname
19 into :fname, :lname;
20 from customer
21 where lname < 'C';
22 strcpy (statement, "OPEN stmt");
23 EXEC SQL open democursor;
24 strcpy (statement, "FETCH stmt");
25 for (;;)
26 {
27 EXEC SQL fetch democursor;
28 if(sqlstate_err() == 100)
29 break;
30 printf("%s %s\n", fname, lname);
31 }
32 strcpy (statement, "CLOSE stmt");
33 EXEC SQL close democursor;
Continued on page 11-59
Line 4
Line 4 declares an external global variable to hold the name of the most-recently executed SQL statement. The exception-handling functions use this information (see "Lines 171 to 215").
Lines 11 and 12
The WHENEVER SQLERROR statement tells the ESQL/C preprocessor to add code to the program to call the whenexp_chk() function whenever an SQL statement generates an error. The WHENEVER SQLWARNING statement tells the ESQL/C preprocessor to add code to the program to call the whenexp_chk() function whenever an SQL statement generates a warning. The whenexp_chk() function is in the exp_chk.ec file, which line 40 includes.
Line 14
The strcpy() function copies the string "CONNECT stmt" to the global statement variable. If an error occurs, the whenexp_chk() function uses this variable to print the name of the statement that caused the failure.
Lines 16, 22, 24, and 32
Lines 16, 22, 24, and 32 copy the name of the current SQL statement into the statement variable before the DECLARE, OPEN, FETCH, and CLOSE statements, respectively, execute. This action enables the whenexp_chk() function to identify the statement that failed if an error occurs.
34 strcpy (statement, "FREE stmt");
35 EXEC SQL free democursor;
36 strcpy (statement, "DISCONNECT stmt");
37 EXEC SQL disconnect current;
38 printf("\nGETDIAG Sample Program Over.\n");
39 } /* End of main routine */
40 EXEC SQL include exp_chk.ec;
Lines 34 and 36
These lines copy the name of the current SQL statement into the statement variable before the FREE and DISCONNECT statements, respectively, execute. The whenexp_chk() function uses the statement variable to identify the statement that failed if an error occurs.
Line 40
The whenexp_chk() function examines the SQLSTATE status variable to determine the outcome of an SQL statement. Because several demonstration programs use the whenexp_chk() function with the WHENEVER statement for exception handling, the whenexp_chk() function and its supporting functions are placed in a separate exp_chk.ec source file. The getdiag program must include this file with the ESQL/C include directive because the exception-handling functions use ESQL/C statements. For a description of the exp_chk.ec source file, see page 11-60.
Guide to the exp_chk.ec File
The exp_chk.ec file contains exception-handling functions for the ESQL/C demonstration programs. These functions support the following two types of 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.
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 (Chapter 8) uses exp_chk2() while the dyn_sql sample program (Chapter 16) 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 Informix-specific information.
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 int sqlstate_err()
11 {
12 int 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 }
Continued on page 11-63
Lines 1 to 4
These ESQL/C 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 to 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, respectively. The sqlstate_err() function checks the first two characters of the global SQLSTATE variable. Because ESQL/C 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 nonerror condition. Nonerror 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 nonerror condition generated.
The sqlstate_err() function sets err_code to indicate the exception status as follows:
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 int j;
40 EXEC SQL BEGIN DECLARE SECTION;
41 int 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 int messlen;
48 char sqlstate_code[6];
49 int 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 }
Continued on page 11-65
Lines 32 to 80
The disp_sqlstate_err() function uses the GET DIAGNOSTICS statement to obtain diagnostic information about the most-recently executed SQL statement.
Lines 40 to 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 to 55 display the values of the SQLSTATE and SQLCODE variables. If SQLSTATE contains "IX000" (an Informix-specific error), SQLCODE contains the Informix-specific error code.
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 to 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 to 65) obtains the exception information for a single exception. Lines 67 to 70 print out 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 to 73 use the ESQL/C byleng() and stleng() library functions to display only that portion of :class_id that contains text; lines 74 to 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 int sqlerr_code;
98 int 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 }
Continued on page 11-67
Lines 81 to 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 to 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 to 117
The disp_exception() function handles the display of the exception information. It expects the following three arguments:
Lines 102 to 104 handle the SUCCESS and NOData conditions. For either of these cases, the function displays no diagnostic information. Lines 105 to 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 to 142). Lines 109 to 111 notify the user that a runtime error has occurred. The disp_err() function actually handles display of the diagnostic information.
118
119 * The exp_chk() function calls sqlstate_err() to check the SQLSTATE
120 * status variable to see if an error or warning has occurred following
121 * an SQL statement. If either condition has occurred, exp_chk()
122 * calls disp_sqlstate_err() to print the detailed error information.
123 *
124 * This function handles exceptions as follows:
125 * runtime errors - call exit()
126 * warnings - continue execution, returning "1"
127 * success - continue execution, returning "0"
128 * Not Found - continue execution, returning "100"
129 */
130 long exp_chk(stmt, warn_flg)
131 char *stmt;
132 int warn_flg;
133 {
134 int sqlerr_code = SUCCESS;
135 sqlerr_code = sqlstate_err();
136 disp_exception(stmt, sqlerr_code, warn_flg);
137 if(sqlerr_code == RTERROR) /* Exception is a runtime error */
138 {
139 /* Exit the program after examining the error */
140 printf("********Program terminated********\n\n");
141 exit(1);
142 }
143 /* else /* Exception is "success", "Not Found", */
144 return(sqlerr_code); /* or "warning" */
145 }
Continued on page 11-69
Lines 118 to 145
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 designed to be called explicitly after each SQL statement. This design requires the following features:
Because the WHENEVER statement does not invoke the function, the function is not restricted to using a global variable.
Because the calling program explicitly calls exp_chk(), the calling program can handle the return value.
Because warnings can indicate nonserious 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 dyn_sql sample program (see "The dyn_sql Program") uses exp_chk() to handle exceptions.
The sqlstate_err() function (line 135) determines the type of exception that SQLSTATE contains. The function then calls disp_exception() (line 136) 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 137 to 142) to terminate the program. This behavior is the same as what the whenexp_chk() function (see lines 171 to 215) provides for runtime errors.
146
147 * The exp_chk2() function calls sqlstate_err() to check the SQLSTATE
148 * status variable to see if an error or warning has occurred following
149 * an SQL statement. If either condition has occurred, exp_chk2()
150 * calls disp_sqlstate_err() to print the detailed error information.
151 *
152 * This function handles exceptions as follows:
153 * runtime errors - continue execution, returning SQLCODE (<0)
154 * warnings - continue execution, returning one (1)
155 * success - continue execution, returning zero (0)
156 * Not Found - continue execution, returning 100
157 */
158 long exp_chk2(stmt, warn_flg)
159 char *stmt;
160 int warn_flg;
161 {
162 int sqlerr_code = SUCCESS;
163 long sqlcode;
164 sqlcode = SQLCODE; /* save SQLCODE in case of error */
165 sqlerr_code = sqlstate_err();
166 disp_exception(stmt, sqlerr_code, warn_flg);
167 if(sqlerr_code == RTERROR)
168 /* sqlerr_code = sqlcode;
169 return(sqlerr_code);
170 }
Continued on page 11-71
Lines 146 to 170
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 designed to be 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 141), while the exp_chk2() function returns the SQLCODE value to the calling program (lines 167 to 168).
The exp_chk2() function returns SQLCODE rather than SQLSTATE to allow the program to check for particular Informix-specific error codes. A possible enhancement might be to return both the SQLSTATE and SQLCODE values.
The dyn_sql sample function, which Chapter 12 describes, uses exp_chk2() to handle exceptions.
171 *
172 * The whenexp_chk() function calls sqlstate_err() to check the SQLSTATE
173 * status variable to see if an error or warning has occurred following
174 * an SQL statement. If either condition has occurred, whenerr_chk()
175 * calls disp_sqlstate_err() to print the detailed error information.
176 *
177 * This function is expected to be used with the WHENEVER SQLERROR
178 * statement: it executes an exit(1) when it encounters a negative
179 * error code. It also assumes the presence of the "statement" global
180 * variable, set by the calling program to the name of the statement
181 * encountering the error.
182 */
183 whenexp_chk()
184 {
185 int sqlerr_code = SUCCESS;
186 int disp = 0;
187 sqlerr_code = sqlstate_err();
188 if(sqlerr_code == WARNING)
189 {
190 disp = 1;
191 printf("\n********Warning encountered in %s********\n",
192 statement);
193 }
194 else
195 if(sqlerr_code == RTERROR)
196 {
197 printf("\n********Error encountered in %s********\n",
198 statement);
199 disp = 1;
200 }
201 if(disp)
202 disp_sqlstate_err();
203 if(sqlerr_code == RTERROR)
204 {
205 /* Exit the program after examining the error */
206 printf("********Program terminated*******\n\n");
207 exit(1);
208 }
209 else
210 {
211 if(sqlerr_code == WARNING)
212 printf("\n********Program execution continues********\n\n");
213 return(sqlerr_code);
214 }
215 }
Lines 171 to 215
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 designed to be called with the following WHENEVER statements:
The WHENEVER statement imposes the following restrictions on the design of the whenexp_chk() function:
To use arguments with the whenexp_chk() function, you could 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);
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 could 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 (page 11-57).
The sqlstate_err() function (line 187) returns an integer that indicates the success of the most-recently executed SQL statement. This return value is based on the SQLSTATE value.
Lines 188 to 200 display a special line to bring attention to the exception information that has been 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 191 and 197) 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 201 and 202) displays the information that the diagnostics area contains only if SQLSTATE indicates a warning or a runtime error (disp = 1).
Lines 203 to 208 handle a runtime error. They notify the user of the program termination and then use the exit() system call (line 207) to terminate the program. The call to the disp_sqlstate_err() function (line 202) has already displayed information about the cause of the runtime error.
|
