INFORMIX-ESQL/C Programmer's Manual Exception Handling

An alternative way to obtain diagnostic information is through the SQL Communications Area. When an SQL statement executes, the database server automatically returns information about the success or failure of the statement in a C structure that is called sqlca. To obtain exception information, your ESQL/C program can access the sqlca structure or the SQLCODE variable as follows:

• The sqlca structure. You can use C statements to obtain additional exception information. You can also obtain information relevant to performance or the nature of the data that is handled. For some statements, the sqlca structure contains warnings.
• The SQLCODE variable directly. You can obtain the status code of the most-recently executed SQL statement. SQLCODE holds an Informix-specific error code, which is copied from the sqlca.sqlcode field.

Important: ESQL/C supports the sqlca structure for backward compatibility. Informix recommends, however, that new applications use the SQLSTATE variable with the GET DIAGNOSTICS statement to perform exception checking. This method conforms to X/Open and ANSI SQL standards and supports multiple exceptions.

The next three sections describe how to use the SQLCODE variable and the sqlca structure to perform exception handling. These sections cover the following topics:

• Understanding the sqlca structure
• Using the SQLCODE variable to obtain error codes
• Checking for the different types of exceptions with the sqlca structure

Figure 11-13 contains the declaration of the sqlca structure from the sqlca.h header file. The ESQL/C preprocessor automatically includes the sqlca.h header file in an ESQL/C program.

Figure 11-14 illustrates the fields of the sqlca structure.

Using the SQLCODE Variable

The SQLCODE variable is an int4 that indicates whether the SQL statement succeeded or failed. The ESQL/C header file, sqlca.h, declares SQLCODE as a global variable. Since the ESQL/C preprocessor automatically includes sqlca.h in an ESQL/C program, you do not need to declare SQLCODE.

When the database server executes an SQL statement, the database server automatically updates the SQLCODE variable as follows:

1. The database server stores the exception value in the sqlcode field of the sqlca structure.
2. ESQL/C copies the value of sqlca.sqlcode to the global SQLCODE variable.

Tip: For readability and brevity, use SQLCODE in your ESQL/C program in place of sqlca.sqlcode.

The SQLCODE value can indicate the following types of exceptions:

For information about the values of SQLCODE (and sqlca.sqlcode) and their corrective actions, use the finderr or Find Error utility or view Informix Error Messages in Answers OnLine. For information about how to handle these exceptions, see Checking for Exceptions with sqlca.

The following sections provide additional information about SQLCODE.

To return the same values that the SQLCODE status variable in ESQL/C modules returns, you can use SQLCODE in pure C modules (modules with the .c extension) that you link to an ESQL/C program. To use SQLCODE in a pure C module, declare SQLCODE as an external variable, as follows:

extern int4 SQLCODE;

To return an error code to a parent process, do not attempt to use the SQLCODE value as an argument to the exit() system call. When ESQL/C passes back the argument of exit() to the parent, it passes only the lower eight bits of the value. Since SQLCODE is a four-byte (long) integer, the value that ESQL/C returns to the parent process might not be what you expect.

To pass error information between processes, use the exit value as an indication that some type of error has occurred. To obtain information on the actual error, use a temporary file, a database table, or some form of interprocess communication.

The DESCRIBE statement returns information about a prepared statement before the statement executes. It operates on a statement ID that a PREPARE statement has previously assigned to a dynamic SQL statement.

After a successful DESCRIBE statement, the database server sets SQLCODE (and sqlca.sqlcode) to a nonnegative integer value that represents the type of SQL statement that DESCRIBE has examined. The sqlstype.h header file declares constant names for each of these return values. For a list of possible SQLCODE values after a DESCRIBE statement, see Determining the Statement Type.

Because the DESCRIBE statement uses the SQLCODE field differently than any other statement, you might want to revise your exception-handling routines to accommodate this difference.

After an SQL statement executes, the sqlca structure can indicate one of the four possible conditions that Figure 11-15 shows.

For a general introduction to these four conditions, see Types of Database Exceptions. The following sections discuss how sqlca indicates each condition.

When the database server executes an SQL statement successfully, it sets SQLCODE (sqlca.sqlcode) to zero (0). The database server might also set one or more of the following informational fields in sqlca after a successful SQL statement:

• After a PREPARE for a SELECT, DELETE, INSERT, or UPDATE:
• sqlca.sqlerrd[0] indicates an estimated number of rows affected.
• sqlca.sqlerrd[3] contains the estimated weighted sum of disk accesses and total rows processed.
• After an INSERT, sqlca.sqlerrd[1] contains the value that the database server has generated for a SERIAL column.
• After a SELECT, INSERT, DELETE, or UPDATE:
• sqlca.sqlerrd[2] contains the number of rows that the database server processed.
• sqlca.sqlerrd[5] contains the rowid (physical address) of the last row that was processed. Whether this rowid value corresponds to a row that the database server returns to the user depends on how the database server processes a query, particularly for SELECT statements.
• After a CONNECT, SET CONNECTION, DATABASE, CREATE DATABASE, or START DATABASE, the sqlca.sqlwarn.sqlwarn0 field is set to W and other fields of sqlca.sqlwarn provide information about the database and connection. For more information, see Warnings in sqlca.sqlwarn.

For more information on these additional fields, see Fields of the sqlca Structure. In addition, the SQLCODE value for some SQL statements has special meaning. For more information, see Using the SQLCODE Variable.

When a SELECT or FETCH statement encounters NOT FOUND (or END OF DATA), the database server sets SQLCODE (sqlca.sqlcode) to 100. Figure 11-16 lists conditions that cause SQL statements to yield NOT FOUND.

Figure 11-16 on page 11-35 shows that what the NOT FOUND condition generates depends, in some cases, on whether the database is ANSI compliant.

In the following example, the INSERT statement inserts into the hot_items table any stock item that has an order quantity greater than 10,000. If no items have an order quantity that great, the SELECT part of the statement fails to insert any rows. The database server returns 100 in an ANSI-compliant database and 0 if the database is not ANSI compliant.

EXEC SQL insert into hot_items

	select distinct stock.stock_num,

			stock.manu_code,description

		from items, stock

		where stock.stock_num = items.stock_num 

			and stock.manu_code = items.manu_code 

			and quantity > 10000;

For readability, use the constant SQLNOTFOUND for the END OF DATA value of 100. The sqlca.h header file defines the SQLNOTFOUND constant. The following comparison checks for the NOT FOUND and END OF DATA conditions:

if(SQLCODE == SQLNOTFOUND)

When the database server executes an SQL statement successfully, but encounters a warning condition, it updates the following two fields in the sqlca.sqlwarn structure:

• It sets the sqlca.sqlwarn.sqlwarn0 field to the letter W.
• It sets one other field within the sqlwarn structure (sqlwarn1 to sqlwarn7) to the letter W to indicate the specific warning condition.

These warnings are Informix specific. Figure 11-14 on page 11-29 contains two sets of warning conditions that can occur in the fields of the sqlca.sqlwarn structure. The first set of warnings, shown in Figure 11-14, occur after the database server opens a database or establishes a connection. For more information on these conditions, see Determining Features of the Database Server. The second set of warnings are for conditions that can occur as a result of other SQL statements.

To test for warnings, check whether the first warning field (sqlwarn0) is set to W. Once you determine that the database server has generated a warning, you can check the values of the other fields in sqlca.sqlwarn to identify the specific condition. For example, if you want to find out what kind of database a CONNECT statement has opened, you can use the code that Figure 11-17 shows.

int trans_db, ansi_db, us_db = 0;


msg = "CONNECT stmt";

EXEC SQL connect to 'stores7';

if(SQLCODE < 0)     /* < 0 is an error */

	err_chk(msg);

if (sqlca.sqlwarn.sqlwarn0 == 'W')

	{

	if (sqlca.sqlwarn.sqlwarn1 == 'W' )

		trans_db = 1;

	if (sqlca.sqlwarn.sqlwarn2 == 'W' ) 

		ansi_db = 1;

	if (sqlca.sqlwarn.sqlwarn3 == 'W' ) 

		us_db = 1;

	}

When an SQL statement results in a runtime error, the database server sets SQLCODE (and sqlca.sqlcode) to a negative value. The actual number identifies the particular error. The error message documentation lists the Informix-specific error codes and their corrective actions. To see the error-message documentation, use the finderr or Find Error utility or view Informix Error Messages in Answers OnLine.

From within your ESQL/C program, you can retrieve error message text that is associated with a negative SQLCODE (sqlca.sqlcode) value with the rgetlmsg() or rgetmsg() library function. See Library Functions for Retrieving Error Messages.

When the database server encounters a runtime error, it might also set the following other fields in the sqlca structure:

• sqlca.sqlerrd[1] to hold the additional ISAM error return code. You can also use the rgetlmsg() and rgetmsg() library functions to obtain ISAM error message text.
• sqlca.sqlerrd[2] to indicate the number of rows processed before the error occurred in a multirow INSERT, UPDATE, or DELETE statement.
• sqlca.sqlerrm to save an error message parameter. This value occupies a %s parameter in the error message.

For example, in the following error message, the name of the table (sam.xyz) is saved in sqlca.sqlerrm:

310: Table (sam.xyz) already exists in database.

• sqlca.sqlerrd[4] after a PREPARE, EXECUTE IMMEDIATE, or DECLARE statement that encountered an error. For more information, see Errors After a PREPARE Statement.

Tip: You can also test for errors with the WHENEVER SQLERROR statement. For more information, see The WHENEVER Statement.

When the database server returns an error for a PREPARE statement, this error is usually because of a syntax error in the prepared text. When this occurs, the database server returns the following information:

• The SQLCODE variable indicates the cause of the error.
• The sqlca.sqlerrd[4] field contains the offset into the prepared statement text at which the error occurs. Your program can use the value in sqlca.sqlerrd[4] to indicate where the syntax of the dynamically prepared text is incorrect.

If you prepare multiple statements with a single PREPARE statement, the database server returns an error status on the first error in the text, even if it encounters several errors.

Important: The sqlerrd[4] field, which is the offset of the error into the SQL statement, might not always be correct because the ESQL/C preprocessor converts the embedded SQL statements into host-language format. In so doing, the preprocessor might change the relative positions of the elements within the embedded statement.

For example, consider the following statement, which contains an invalid WHERE clause:

EXEC SQL INSERT INTO tab VALUES (:x, :y, :z) 

	WHERE i = 2;

The preprocessor converts this statement to a string like the following string:

" insert into tab values ( ? , ? , ? ) where i = 2 "

This string does not have the EXEC SQL keywords. Also, the characters ?, ?, ? have replaced :x, :y, :z (five characters instead of eight). The ESQL/C preprocessor has also dropped a newline character between the left parenthesis (")") and the WHERE keyword. Thus, the offset of error in the SQL statement that the database server sees is different than the offset of the error in the embedded SQL statement.

The sqlca.sqlerrd[4] field also reports statement-offset values for errors in the EXECUTE IMMEDIATE and DECLARE statements.

After an EXECUTE statement, the database server sets SQLCODE to indicate the success of the prepared statement as follows:

• If the database server cannot execute a prepared statement successfully, it sets SQLCODE to a value less than 0. The SQLCODE variable holds the error that the database server returns from the statement that failed.
• If the database server can successfully execute the prepared statement in the block, it sets SQLCODE to 0; if the prepared block includes multiple statements, all of the statements succeeded.

Your ESQL/C application can use the Informix ERRMESS.HLP file to display text that describes an error and its corrective action. You can call the Windows API WinHelp() with the following WinHelp parameters.