SQL execution diagnostic registers

If an SQL statement execution failed, error description can be found in the SQLCA.SQLCODE, SQLSTATE, STATUS and SQLERRMESSAGE predefined registers.

Using SQLCA.SQLCODE

SQL error codes can be found in the SQLCA.SQLCODE register. This register contains always an IBM^® Informix^® error code, even when connected to a database different from IBM Informix.

STATUS is the global language error code register, set for any kind of error (even non-SQL). When an SQL error occurs, The error held in SQLCA.SQLCODE is copied into STATUS.

Use SQLCA.SQLCODE for SQL error management, and STATUS to detect errors with other language instructions.

By default SQL errors stop program execution. You must modify the exception handler to continue program execution and check for SQL errors in the SQLCA.SQLCODE register.

MAIN
  WHENEVER ERROR CONTINUE
  DATABASE stores
  WHENEVER ERROR STOP
  IF SQLCA.SQLCODE < 0 THEN
     DISPLAY "Could not connect to database:", SQLCA.SQLCODE
     DISPLAY SQLERRMESSAGE
     EXIT PROGRAM 1
  END IF
  ...
END MAIN

When connecting to a database different from IBM Informix, the database driver tries to convert the native SQL error to an IBM Informix error which will be copied into the SQLCA.SQLCODE and STATUS registers. If the native SQL error cannot be converted, SQLCA.SQLCODE and STATUS will be set to -6372 (a general SQL error), you can then check the native SQL error in SQLCA.SQLERRD[2]. The native SQL error code is always available in SQLCA.SQLERRD[2], even if it could not be converted to an IBM Informix error.

Using SQLSTATE

SQLSTATE contains an error code that follows ISO/ANSI standard error specification, but not all database servers support this register. Using SQLSTATE for SQL error checking should be the preferred way for portable SQL programming, as long as the target databases support this feature. For maximum SQL portability, centralize SQL error checking in functions, to test either SQLCA.SQLCODE or SQLSTATE, according to the target database.

MAIN
  WHENEVER ERROR CONTINUE
  DATABASE stores
  WHENEVER ERROR STOP
  IF check_sql_error() == MYSQLERR_CONNECTION THEN
     EXIT PROGRAM 1
  END IF
  ...
END MAIN

The SQLSTATE codes are defined by the ANSI/ISO standard specification, but no all database types support this standard.

Table 1. SQLSTATE error codes support per database server type
Database Server Type	Supports SQLSTATE errors
Genero db	Not in version 3.61
IBM DB2^® UDB (UNIX™)	Yes, since version 7.1
IBM Informix	Yes, since IDS 10
Microsoft™ SQL Server	Yes, since version 8 (2000)
MySQL	Yes
Oracle Database Server	Not in version 10.2
PostgreSQL	Yes, since version 7.4
Sybase ASE	Yes

SQL error identification requires quite complex code, which can be RDBMS-specific in some cases. Therefore, it is strongly recommended that you centralize SQL error identification in a function. This will allow you to write RDBMS-specific code, when needed, only once.

SQL error messages

SQLERRMESSAGE contains the database specific error message. These messages are different for every database type and should only be used to print or log SQL execution diagnostic information.

SQL warnings

Some SQL instructions can produce SQL Warnings which can be detected by using the SQLCA.SQLAWARN register. By default SQL Warnings do not stop the program execution. You can trap SQL Warnings with WHENEVER WARNING.

Display detailed debug information in case of internal driver error

If an unexpected problem happens within the database driver, the driver will return the error -6319 (internal error in the database library). When this SQL error occurs, set the FGLSQLDEBUG environment variable to get more details about the internal error.