SQL error identification

Identify 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.

Database drivers map native SQL errors to Informix SQL errors, as listed in the following table:

Sometimes the native error code of the database cannot be converted to an Informix error code. In such case, the SQLCA.SQLCODE register will be set to -6372. To properly identify an SQL error, the native SQL error code is also provided in the SQLCA.SQLERRD[2] register.

-- 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