Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 11: Exception Handling

Home Contents Index Master Index New Book

Exception Handling with the sqlca Structure

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 (page 11-6).

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

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


struct  sqlca_s

        {

        long  sqlcode;

        char  sqlerrm[72];  /*  error  message  parameters  */

        char  sqlerrp[8];

        long  sqlerrd[6];

			                /*  0  -  estimated  number  of  rows  returned  */

			                /*  1  -  serial  value  after  insert  or  ISAM  error  code  */

			                /*  2  -  number  of  rows  processed  */

			                /*  3  -  estimated  cost  */

			                /*  4  -  offset  of  the  error  into  the  SQL  statement  */

			                /*  5  -  rowid  after  insert    */

        struct  sqlcaw_s

		{

	char  sqlwarn0;  /*  =  W  if  any  of  sqlwarn[1-7]  =  W  */

	char  sqlwarn1;  /*  =  W  if  any  truncation  occurred  or

					        database  has  transactions  */

	char  sqlwarn2;  /*  =  W  if  a  null  value  returned  or

					      ANSI  database  */

	char  sqlwarn3;  /*  =  W  if  no.  in  select  list  !=  no.  in  into  list  or

					      OnLine  backend  */

	char  sqlwarn4;  /*  =  W  if  no  where  clause  on  prepared  update, delete, or

					    incompatible  float  format  */

	char  sqlwarn5;  /*  =  W  if  non-ANSI  statement  */

	char  sqlwarn6;  /*  =  W  if server is in data replication secondary mode  */

	char  sqlwarn7;  /*  reserved  */

		}  sqlwarn;

        };



extern  struct  sqlca_s  sqlca;



extern  long  SQLCODE;



extern char SQLSTATE[];

Figure 11-13
Declaration of sqlca in the sqlca.h Header File

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

Figure 11-14
Fields of the sqlca Structure

Using the SQLCODE Variable

The SQLCODE variable is a long integer 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:


SQLCODE = `0`	Success
SQLCODE = `100`	NOT FOUND condition
SQLCODE < `0`	Runtime error

For information about the values of SQLCODE (and sqlca.sqlcode) and their corrective actions, refer to the Informix Error Messages manual. For information about how to handle these exceptions, see "Checking for Exceptions with sqlca".

The following sections provide additional information about SQLCODE.

SQLCODE in Pure C Modules

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 long SQLCODE;

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)

Warnings in sqlca.sqlwarn

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 contains two tables that describe the fields of the sqlca.sqlwarn structure and their associated warning conditions. The first sqlwarn table in Figure 11-14 lists the warnings that occur after the database server opens a database or establishes a connection. For more information, see "Determining Features of the Database Server".

The second sqlwarn table in Figure 11-14 lists warnings that other SQL statements might generate.

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 block of 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;
}

Figure 11-17
Code Fragment That Checks for Warnings After a CONNECT Statement

Runtime Errors in SQLCODE

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 Informix Error Messages manual lists these Informix-specific error codes and their corrective actions. You can also use the command-line finderr utility to obtain information about an Informix error. For more information about finderr, see the Informix Error Messages manual.

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

Errors After a PREPARE 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 one:

" 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 new-line 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.

SQLCODE After an EXECUTE Statement

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.

WIN NT/95

Displaying Error Text

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.


WinHelp Parameter	Data
`HELP_CONTEXT`	Error number from `SQLCODE` or sqlca.sqlcode
`HELP_CONTEXTPOPUP`	Error number from `SQLCODE` or sqlca.sqlcode
`HELP_KEY`	Pointer to string that contains error number from `SQLCODE` or sqlca.sqlcode and is converted to `ASCII` with sprintf() or wsprintf()
`HELP_PARTIALKEY`	Pointer to string that contains error number from `SQLCODE` or sqlca.sqlcode and is converted to `ASCII` with sprintf() or wsprintf()