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 in order 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 that is different from IBM Informix.

STATUS is the global error code register that can be set for any kind of error (even non-SQL). When an SQL error occurs, both SQLCA.SQLCODE and STATUS hold the SQL error code.

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

When connecting to a database that is 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 cannot be converted to an IBM Informix error.

Using SQLSTATE

SQLSTATE is a register that contains an error code following ISO standards. However, not all database servers support this standard.

Preferably use SQLSTATE for SQL error checking, as long as the target databases support this feature.

Centralize SQL error checking

SQL error identification sometimes requires complex code to check several RDBMS-specific error numbers. Therefore, it is strongly recommended that you centralize SQL error identification in a function. When needed, this allows you to write the RDBMS-specific code only once.

For maximum SQL portability, centralize SQL error checking in functions, to test either SQLCA.SQLCODE or SQLSTATE, depending on the database server type. Furthermore, consider defining 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 is a register that contains the database-specific error message. These messages are different for every database type.

Only use SQLERRMESSAGE to print or log SQL execution diagnostics.

SQL warnings

Some SQL instructions can produce SQL warnings. Unlike SQL errors, 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 connection was opened. Another warning may also be returned if the database supports transactions. None of these facts are critical, but that information may help with program execution.

If an SQL warning is raised, SQLCA.SQLCODE / STATUS remain zero, and the program flow continues. To detect if an SQL warning has occurred, the SQLCA.SQLAWARN register must be checked. SQLCA.SQLAWARN is defined as a CHAR(7) variable. If SQLCA.SQLAWARN[1] contains the W letter, it means that the last SQL instruction returned a warning. The other character positions (SQLCA.SQLAWARN[2-8]) may contain W letters too, depending on the database server type and the type of SQL instruction that was executed.

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

When connecting to an IBM Informix database with transactions, the program will display the following:

[WW W ]

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 the above example uses two columns in the select list, while only one INTO variable is provided. This is legal and does not raise an SQL error. However, SQLCA.SQLAWARN is set 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, it will return the error -6319 (indicating an internal error in the database driver). When this SQL error occurs, set the FGLSQLDEBUG environment variable to get more details about the internal error.

Determine the number of processed rows

SQL statements such as UPDATE and DELETE can affect zero, one or several rows.

If you need to check how many rows are processed by an SQL statement, check the SQLCA.SQLERRD[3] register.

TYPE t_country RECORD
  c_code CHAR(5),
  c_desc VARCHAR(50)
END RECORD

FUNCTION update_country_description(rec t_country) RETURNS INTEGER
  UPDATE country SET desc = rec.c_desc WHERE code = rec.c_code
  CASE
    WHEN SQLCA.SQLCODE < 0
      MESSAGE SFMT("SQL error:%1 [%2]", SQLCA.SQLCODE, SQLERRMESSAGE)
      RETURN -2
    WHEN SQLCA.SQLCODE == 0
      IF SQLCA.SQLERRD[3] == 0 THEN
        MESSAGE SFMT("No country was found with code: %1", rec.c_code)
        RETURN -1
      ELSE
        MESSAGE SFMT("Updated country with code: %1", rec.c_code)
      END IF
  END CASE
  RETURN 0
END FUNCTION

For more details see The SQLCA diagnostic record.