INFORMIX-ESQL/C Programmer's Manual 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 SQLSTATE variable and the GET DIAGNOSTICS statement to obtain exception information.
• The SQLWARNING and SQLERROR keywords of the WHENEVER statement to call the whenexp_chk() function for warnings and errors.

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-50) includes the exp_chk.ec file (page 11-53).

Use the following command to compile the getdiag program:

esql -o getdiag getdiag.ec

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.

The annotations in this section primarily describe the exception-handling statements.

#include <stdio.h>

EXEC SQL define FNAME_LEN 15;

EXEC SQL define LNAME_LEN 15;

int4 sqlstate_err();

extern char statement[20];

main()

{

EXEC SQL BEGIN DECLARE SECTION;

char fname[ FNAME_LEN + 1 ];

char lname[ LNAME_LEN + 1 ];

EXEC SQL END DECLARE SECTION;

EXEC SQL whenever sqlerror CALL whenexp_chk;

EXEC SQL whenever sqlwarning CALL whenexp_chk;

printf("GETDIAG Sample ESQL program running.\n\n");

strcpy (statement, "CONNECT stmt");

EXEC SQL connect to 'stores7';

strcpy (statement, "DECLARE stmt");

EXEC SQL declare democursor cursor for

select fname, lname

into :fname, :lname;

from customer

where lname < 'C';

strcpy (statement, "OPEN stmt");

EXEC SQL open democursor;

strcpy (statement, "FETCH stmt");

for (;;)

{

EXEC SQL fetch democursor;

if(sqlstate_err() == 100)

break;

printf("%s %s\n", fname, lname);

}

strcpy (statement, "CLOSE stmt");

EXEC SQL close democursor;

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 169 to 213).

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.

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 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.

strcpy (statement, "FREE stmt");

EXEC SQL free democursor;

strcpy (statement, "DISCONNECT stmt");

EXEC SQL disconnect current;

printf("\nGETDIAG Sample Program Over.\n");

} /* End of main routine */

EXEC SQL include exp_chk.ec;

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.

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 source file, exp_chk.ec. The getdiag program must include this file with the ESQL/C include directive because the exception-handling functions use ESQL/C statements.

Tip: You should consider putting functions such as whenexp_chk() into a library and include this library on the command line when you compile the ESQL/C program.

The following section describes the exp_chk.ec source file.

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:

• 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 an ESQL/C 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 (Chapter 7) 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.

EXEC SQL define SUCCESS 0;

EXEC SQL define WARNING 1;

EXEC SQL define NODATA 100;

EXEC SQL define RTERROR -1;

char statement[80];

/*

* The sqlstate_err() function checks the SQLSTATE status variable to see

* if an error or warning has occurred following an SQL statement.

*/

int4 sqlstate_err()

{

int4 err_code = RTERROR;

if(SQLSTATE[0] == '0') /* trap '00', '01', '02' */

{

switch(SQLSTATE[1])

{

case '0': /* success - return 0 */

err_code = SUCCESS;

break;

case '1': /* warning - return 1 */

err_code = WARNING;

break;

case '2': /* end of data - return 100 */

err_code = NODATA;

break;

default: /* error - return -1*/

break;

}

}

return(err_code);

}

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.

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).

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:

• Lines 17 to 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 to 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 to 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).

/*

* The disp_sqlstate_err() function executes the GET DIAGNOSTICS

* statement and prints the detail for each exception that is

* returned.

*/

void disp_sqlstate_err()

{

mint j;

EXEC SQL BEGIN DECLARE SECTION;

mint exception_count;

char overflow[2];

int exception_num=1;

char class_id[255];

char subclass_id[255];

char message[8191];

mint messlen;

char sqlstate_code[6];

mint i;

EXEC SQL END DECLARE SECTION;

printf("---------------------------------");

printf("-------------------------\n");

printf("SQLSTATE: %s\n",SQLSTATE);

printf("SQLCODE: %d\n", SQLCODE);

printf("\n");

EXEC SQL get diagnostics :exception_count = NUMBER,

:overflow = MORE;

printf("EXCEPTIONS: Number=%d\t", exception_count);

printf("More? %s\n", overflow);

for (i = 1; i <= exception_count; i++)

{

EXEC SQL get diagnostics exception :i

:sqlstate_code = RETURNED_SQLSTATE,

:class_id = CLASS_ORIGIN, :subclass_id = SUBCLASS_ORIGIN,

:message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;

printf("- - - - - - - - - - - - - - - - - - - -\n");

printf("EXCEPTION %d: SQLSTATE=%s\n", i,

sqlstate_code);

message[messlen-1] = '\0';

printf("MESSAGE TEXT: %s\n", message);

j = byleng(class_id, stleng(class_id));

class_id[j] = '\0';

printf("CLASS ORIGIN: %s\n",class_id);

j = byleng(subclass_id, stleng(subclass_id));

subclass_id[j] = '\0';

printf("SUBCLASS ORIGIN: %s\n",subclass_id);

}

printf("---------------------------------");

printf("-------------------------\n");

}

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.

void disp_error(stmt)

char *stmt;

{

printf("\n********Error encountered in %s********\n",

stmt);

disp_sqlstate_err();

}

void disp_warning(stmt)

char *stmt;

{

printf("\n********Warning encountered in %s********\n",

stmt);

disp_sqlstate_err();

}

void disp_exception(stmt, sqlerr_code, warn_flg)

char *stmt;

int4 sqlerr_code;

mint warn_flg;

{

switch(sqlerr_code)

{

case SUCCESS:

case NODATA:

break;

case WARNING:

if(warn_flg)

disp_warning(stmt);

break;

case RTERROR:

disp_error(stmt);

break;

default:

printf("\n********INVALID EXCEPTION STATE for %s********\n",

stmt);

/* break;

}

}

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.

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

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.

* The exp_chk() function calls sqlstate_err() to check the SQLSTATE

* status variable to see if an error or warning has occurred following

* an SQL statement. If either condition has occurred, exp_chk()

* calls disp_sqlstate_err() to print the detailed error information.

*

* This function handles exceptions as follows:

* runtime errors - call exit()

* warnings - continue execution, returning "1"

* success - continue execution, returning "0"

* Not Found - continue execution, returning "100"

*/

long exp_chk(stmt, warn_flg)

char *stmt;

int warn_flg;

{

int4 sqlerr_code = SUCCESS;

sqlerr_code = sqlstate_err();

disp_exception(stmt, sqlerr_code, warn_flg);

if(sqlerr_code == RTERROR) /* Exception is a runtime error */

{

/* Exit the program after examining the error */

printf("********Program terminated********\n\n");

exit(1);

}

/* else /* Exception is "success", "Not Found", */

return(sqlerr_code); /* or "warning" */

}

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:

• 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 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 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 to 141) to terminate the program. This behavior is the same as what the whenexp_chk() function (see lines 170 to 214) provides for runtime errors.

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

* The exp_chk2() function calls sqlstate_err() to check the SQLSTATE

* status variable to see if an error or warning has occurred following

* an SQL statement. If either condition has occurred, exp_chk2()

* calls disp_sqlstate_err() to print the detailed error information.

*

* This function handles exceptions as follows:

* runtime errors - continue execution, returning SQLCODE (<0)

* warnings - continue execution, returning one (1)

* success - continue execution, returning zero (0)

* Not Found - continue execution, returning 100

*/

int4 exp_chk2(stmt, warn_flg)

char *stmt;

mint warn_flg;

{

int4 sqlerr_code = SUCCESS;

int4 sqlcode;

sqlcode = SQLCODE; /* save SQLCODE in case of error */

sqlerr_code = sqlstate_err();

disp_exception(stmt, sqlerr_code, warn_flg);

if(sqlerr_code == RTERROR)

/* sqlerr_code = sqlcode;

return(sqlerr_code);

}

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 140), while the exp_chk2() function returns the SQLCODE value to the calling program (lines 165 to 166).

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 program, described on page 16-54, also uses exp_chk2() to handle exceptions.

*

* The whenexp_chk() function calls sqlstate_err() to check the SQLSTATE

* status variable to see if an error or warning has occurred following

* an SQL statement. If either condition has occurred, whenerr_chk()

* calls disp_sqlstate_err() to print the detailed error information.

*

* This function is expected to be used with the WHENEVER SQLERROR

* statement: it executes an exit(1) when it encounters a negative

* error code. It also assumes the presence of the "statement" global

* variable, set by the calling program to the name of the statement

* encountering the error.

*/

whenexp_chk()

{

int4 sqlerr_code = SUCCESS;

mint disp = 0;

sqlerr_code = sqlstate_err();

if(sqlerr_code == WARNING)

{

disp = 1;

printf("\n********Warning encountered in %s********\n",

statement);

}

else

if(sqlerr_code == RTERROR)

{

printf("\n********Error encountered in %s********\n",

statement);

disp = 1;

}

if(disp)

disp_sqlstate_err();

if(sqlerr_code == RTERROR)

{

/* Exit the program after examining the error */

printf("********Program terminated*******\n\n");

exit(1);

}

else

{

if(sqlerr_code == WARNING)

printf("\n********Program execution continues********\n\n");

return(sqlerr_code);

}

}

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:

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 on page 11-50.

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 to 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 to 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.