SQL error identification

Identifying SQL exceptions in your programs with SQLCA.SQLCODE.

Every database type has its own error numbers. Portable SQL code must take care of this when checking for SQL errors in programs.

The IBM^® Informix^® compatible error code is stored in the SQLCA.SQLCODE register. This is done to simplify migration to another database type. Existing code based on Informix error numbers does not need to be modified.

However, sometimes the native error code of the database cannot be converted to an Informix error code. To properly identify an SQL error, the native error code is also provided, and available in the SQLCA.SQLERRD[2] register.

You should centralize SQL error identification in a function:

-- sqlerr.4gl module

PUBLIC CONSTANT SQLERRTYPE_FATAL = -1 
PUBLIC CONSTANT SQLERRTYPE_LOCK  = -2 
PUBLIC CONSTANT SQLERRTYPE_CONN  = -3 

FUNCTION lastSqlErrorType() 
   CASE
     WHEN SQLCA.SQLCODE == -201
            OR SQLCA.SQLERRD[2] == ... 
       RETURN SQLERR_FATAL 
     WHEN SQLCA.SQLCODE == -263
            OR SQLCA.SQLCODE == -244
            OR SQLCA.SQLERRD[2] == ... 
       RETURN SQLERR_LOCK 
     ...
   END CASE
END FUNCTION

You can then easily use this function after every SQL statement, in your programs:

IMPORT FGL sqlerr
MAIN
   DATABASE stores
   WHENEVER ERROR CONTINUE
   UPDATE customer SET cust_address = NULL
     WHEN cust_name IS NULL
   IF lastSqlErrorType() == SQLERRTYPE_LOCK THEN
       ... 
   END IF 
   ...
END MAIN