INFORMIX-ESQL/C Programmer's Manual Exception Handling

After your ESQL/C program executes an SQL statement, the database server returns information about the success of the statement. This section summarizes the following information:

• The types of diagnostic information that are available to an ESQL/C program
• The two methods that your ESQL/C program can use to obtain diagnostic information

The database server can return the following types of diagnostic information:

• Database exceptions are conditions that the database server returns to describe how successful the execution of the SQL statement was.
• Descriptive information, such as the DESCRIBE and GET DIAGNOSTICS statements can provide about certain SQL statements.

When the database server executes an SQL statement, it can return one of four types of database exceptions to the application program:

• Success

The SQL statement executed successfully. When a statement that might return data into host variables executes, a success condition means that the statement has returned the data and that the program can access it through the host variables.

• Success, but warning generated

A warning is a condition that does not prevent successful execution of an SQL statement; however, the effect of the statement is limited and the statement might not produce the expected results. A warning can also provide additional information about the executed statement.

• Success, but no rows found

The SQL statement executed without errors, with the following exceptions:

• No rows matched the search criteria (the NOT FOUND condition).
• The statement did not operate on a row (the END OF DATA condition).

• Error

The SQL statement did not execute successfully and made no change to the database. Runtime errors can occur at the following levels:

• Hardware errors include controller failure, bad sector on disk, and so on.
• Kernel errors include file-table overflow, insufficient semaphores, and so on.
• Access-method errors include duplicated index keys, SQL null inserted into non-null columns, and so on.
• Parser errors include invalid syntax, unknown objects, invalid statements, and so on.
• Application errors include user or lock-table overflow, and so on.

The following SQL statements can return information about SQL statements:

• A DESCRIBE statement returns information about a prepared SQL statement. This information is useful when you execute dynamic SQL. For more information, see SQLCODE After a DESCRIBE Statement.
• A GET DIAGNOSTICS statement, when you call it after you have established a connection to a database environment, can return the name of the database server and the connection. For more information, see Using GET DIAGNOSTICS.

The Informix Guide to SQL: Syntax fully describes these two statements.

The following methods obtain diagnostic information about the outcome of an SQL statement:

• Access the SQLSTATE variable, a five-character string that contains status values that conform to the ANSI and X/Open standards
• Access the SQLCODE variable, an int4 integer that contains Informix-specific status values

When you create applications that must conform to either the ANSI or X/Open standard, use the SQLSTATE variable as your primary exception-handling method.