Understanding Database Server Exceptions

A database server exception is an unexpected condition that occurs within the database server. A database server exception can occur in any of the following tasks:

• Within an SQL statement

  The mi_exec( ) and mi_exec_prepared_statement( ) functions execute SQL statements. An exception can occur when the database server executes an SQL statement.

• Within a UDR

  The mi_routine_exec( ) function executes UDRs through the Fastpath interface. An exception can occur when this UDR executes.

• By the DataBlade API function, mi_db_error_raise( )

  The mi_db_error_raise( ) function can explicitly raise a database server exception within a DataBlade API module. For more information, see Raising an Exception.

• Within the execution of some other DataBlade API function

  Other functions in the DataBlade API might raise exceptions when they execute.

When the database server encounters a database server exception, it raises the MI_Exception event.

Warnings and Errors

The MI_Exception event indicates which of the following status conditions has caused the database server exception:

• 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 runtime error (or failure) indicates that the SQL statement or DataBlade API function did not execute successfully and it 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.
  • DataBlade API library errors are usually caused by invalid arguments.

The following list describes the most common DataBlade API library errors:

• The DataBlade API might not have been initialized.
• One of the DataBlade API initialization functions must be the first DataBlade API call in the module. For more information, see Initializing the DataBlade API.
• A function that passes a connection descriptor (MI_CONNECTION) passes an invalid connection, one that was closed or dropped.
• A function that passes a row descriptor (MI_ROW_DESC) can pass an invalid row descriptor.
• A function that passes a row structure (MI_ROW) passes an invalid row.
• A function that passes a column name passes a column name that does not exist in the object or objects being accessed.
• A function that passes a column number passes a column number that is out of range (greater or less than the number of columns in the object).
• A function that passes an event type (MI_EVENT_TYPE) passes a nonexistent event type.
• A function that passes a save set (MI_SAVE_SET) passes an invalid save set.
• A function that passes a buffer passes a null buffer or a buffer that is too small.
• A function that passes a pointer passes an invalid pointer.
• If the pointer_checks_enabled field of the parameter information structure is set, a pointer might not be within the process heap space.

Potential exceptions other than these types of common invalid arguments are mentioned in the Return Values section of the individual function descriptions in the IBM Informix: DataBlade API Function Reference.

An error descriptor for an MI_Exception event indicates the status condition of the event with one of the following exception levels.

The mi_error_level( ) function returns the exception level from an error descriptor for the MI_Exception event.

Status Variables

To identify the particular cause of an exception, the database server sets the following status variables:

• The SQLSTATE status variable holds a five-character code that is compliant with ANSI and X/Open standards.
• The SQLCODE status variable holds an integer value that is Informix specific.

SQLSTATE Status Value

SQLSTATE is a five-character string that the database server sets after it executes each DataBlade API function. The value of SQLSTATE indicates the status of the function execution.

This five-character code consists of a two-character class code and a three-character subclass code. In Figure 58, "IX" is the class code and "000" is the subclass code. The SQLSTATE value "IX000" indicates that an Informix-specific error has occurred.

Figure 58. The Structure of the SQLSTATE Code with the Value "IX000"

SQLSTATE can contain only digits and capital letters. The class code is unique but the subclass code is not. The meaning of the subclass code depends on the associated class code. The initial character of the class code indicates the source of the exception, as Table 69 summarizes.

After the database server executes a DataBlade API function, it sets SQLSTATE to indicate one of the status conditions, as Table 70 shows.

"000" to "006"

"I01" to "I11"

"U01"

For a list of reserved ANSI and X/Open values for SQLSTATE, see the description of the GET DIAGNOSTICS statement in the IBM Informix: Guide to SQL Syntax. For more information on DataBlade API literal exceptions ("U0001" and "01U01"), see Passing Literal Messages. For more information on DataBlade API custom exceptions, see Raising Custom Messages.

Identifying Warnings with SQLSTATE

When the database server executes a DataBlade API function successfully but encounters a warning condition, it takes the following actions:

• Sets the class code of SQLSTATE to "01"
• Sets the subclass code of SQLSTATE to a unique value that indicates the cause of the warning (see Table 70)
• Throws an MI_Exception event with an MI_MESSAGE exception level

Identifying Runtime Errors with SQLSTATE

When an SQL statement results in a runtime error, the database server takes the following actions:

• Sets the class code of SQLSTATE to a value greater than "02" (see Table 70)
• Sets the subclass code of SQLSTATE to a unique value that indicates the cause of the error
• Raises an MI_Exception event with an MI_EXCEPTION exception level

The actual class and subclass codes of SQLSTATE identify the particular error. For Informix-specific errors (SQLSTATE is "IX000"), you can also check the value of the SQLCODE variable to identify the error.

SQLCODE Status Value

Each SQLSTATE value also has an associated Informix-specific status code. The database server saves this Informix-specific status code in the SQLCODE status variable. The SQLCODE variable is an integer that indicates whether the SQL statement succeeded or failed.

When the database server executes an SQL statement, the database server automatically updates the SQLCODE variable. After an SQL statement executes, the SQLCODE variable can indicate one of the status conditions that Table 71 shows.

Identifying Warnings with SQLCODE

When the database server executes an SQL statement successfully but encounters a warning condition, it takes the following actions:

• Sets the SQLSTATE variable to a five-character warning value
• Sets the SQLCODE variable to zero (success)
• Raises the MI_Exception event with the MI_MESSAGE exception level

To identify warnings, examine the value of SQLSTATE. The SQLCODE value does not indicate the cause of a warning. For more information, see Identifying Warnings with SQLSTATE.

Identifying Runtime Errors with SQLCODE

When an SQL statement results in a runtime error, the database server takes the following actions:

• Sets SQLCODE to a negative value
• Raises an MI_Exception event with an MI_EXCEPTION exception level

The actual number in SQLCODE identifies the particular Informix runtime error. The finderr or Error Messages utility lists error messages and describes corrective actions.