SQL execution diagnostics

Trapping SQL errors

By default, SQL errors stop program execution and display the error message to the standard output. Most SQL statements executed by a program should not return an error and thus do not require error trapping. However, in some cases, a program must keep the control when an SQL error occurs. For example, when connecting to the database, the user might enter an invalid password that will raise a login denied error. The program must trap such SQL connection error to return to the login dialog and let the user enter a new login and password.

-- WHENEVER ERROR handler
WHENEVER ERROR CONTINUE
  INSERT INTO orders VALUES ( rec_ord. * )
  IF SQLCA.SQLCODE = -75623 THEN
     ...
  END IF
WHENEVER ERROR STOP -- restore the default

-- TRY/CATCH block
TRY
  INSERT INTO orders VALUES ( rec_ord. * )
CATCH
  IF SQLCA.SQLCODE = -75623 THEN
     ...
  END IF
END TRY

Using SQLCA.SQLCODE

SQL error codes are provided in the SQLCA.SQLCODE register. This register always contains 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.

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.

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

Centralize SQL error checking

SQL error identification sometimes requires complex code, checking different error numbers that can be RDBMS-specific. 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.

For maximum SQL portability, centralize SQL error checking in functions, to test either SQLCA.SQLCODE or SQLSTATE, according to the target database, and define your own error identifiers with constants:

CONSTANT SQLERR_INVALID_DATABASE = -1001,
         SQLERR_INVALID_USER = -1002,
         ...

FUNCTION do_connect()
  DEFINE uname, upswd VARCHAR(100)
  WHILE TRUE
    CALL login() RETURNING uname, upswd
    TRY
       CONNECT TO "stores" USER uname USING upswd
    CATCH
       CASE check_sql_error()
       WHEN SQLERR_INVALID_DATABASE
          DISPLAY SQLERRMESSAGE
          EXIT PROGRAM 1 -- Fatal error: Stop!
       WHEN SQLERR_INVALID_USER
          ERROR "Invalid login, try again"
          CONTINUE WHILE
       END CASE
    END TRY
    EXIT WHILE
  END WHILE
END FUNCTION

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. Compared to SQL Errors which do normally stop the program execution, SQL Warnings indicate a minor issue that can often be ignored. For example, when connecting to an IBM Informix database, a warning is returned to indicate that a database was opened, and an other warning might be returned if that database supports transactions. None of these facts are critical problems, but knowing that information can help for further program execution.

If an SQL Warning is raised, SQLCA.SQLCODE / STATUS remain zero, and the program flow continues. To detect if an SQL Warning occurs, the SQLCA.SQLAWARN register must be used. SQLCA.SQLAWARN is defined as a CHAR(7) variable. If SQLCA.SQLAWARN[1] contains the W letter, it means that the last SQL instruction has returned a warning. The other character positions (SQLCA.SQLAWARN[2-8]) may contain W letters. Each position from 2 to 8 has a special meaning according to the database server type, and the SQL instructions type.

If SQLCA.SQLAWARN is set, you can also check the SQLSTATE and SQLCA.SQLERRD[2] registers to get more details about the warning. The SQLERRMESSAGE register might also contain the warning description.

MAIN
  DATABASE stores 
  DISPLAY "[", sqlca.sqlawarn, "]"
END MAIN

MAIN
  DEFINE cust_name VARCHAR(50)
  DATABASE stores 
  WHENEVER WARNING STOP
  SELECT cust_lname, cust_address INTO cust_name 
       FROM customer WHERE cust_id = 101
  WHENEVER WARNING CONTINUE
END MAIN

The SELECT statement in this example uses two columns in the select list, but only one INTO variable is provided. This is legal and does not raise an SQL Error, however, it will set the SQLCA.SQLAWARN register to indicate that the number of target variables does not match the select-list items.

See also WHENEVER WARNING exception.

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.