SQL error identification

Identify SQL exceptions in your programs with SQLCA.SQLCODE.

Every database type has its own set of 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 aims at simplifying 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
PUBLIC CONSTANT SQLERRTYPE_UNDEF = -999

FUNCTION lastSqlErrorType() 
   CASE
     WHEN SQLCA.SQLCODE == -201
            OR SQLCA.SQLERRD[2] == ... 
       RETURN SQLERRTYPE_FATAL 
     WHEN SQLCA.SQLCODE == -263
            OR SQLCA.SQLCODE == -244
            OR SQLCA.SQLERRD[2] == ... 
       RETURN SQLERRTYPE_LOCK 
     OTHERWISE
       RETURN SQLERRTYPE_UNDEF
   END CASE
END FUNCTION

Then you can then easily use this function after every SQL statement:

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