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:

Table 1. Native SQL error to Informix SQL error mapping
Informix SQL Oracle® DB SQL Server IBM DB2® PostgreSQL (SQLSTATE) Oracle MySQL SAP HANA® SQLite IBM Netezza
-201 900:902, 905:911, 914, 917, 920:931, 933:936, 938:940, 946, 950, 954, 957, 958, 962, 964, 966:971, 978:979, 982, 984, 985, 990, 992:996, 998:999 102, 170, 101, 1103, 3005, 3014 -101, -104, -106, -108, -109, -127, -142, -143 03000, 42000, 42501, 42601 1064, 1121 -257 N/A 21
-204 N/A 3016 -103 N/A N/A N/A N/A N/A
-206 903, 942 3701, 4004 -204 42P01 1146, 1051 -259 N/A 29
-217 904 4005 -205, -206 42703 1054 -260 N/A 31
-236 913, 947 1200 -117 N/A N/A -269 N/A N/A
-244 N/A 1222 N/A N/A N/A -131 N/A N/A
-251 N/A N/A -125 N/A N/A N/A N/A N/A
-253 972 2014 -107 N/A N/A N/A N/A N/A
-254 1008, 1475 N/A N/A N/A N/A -270 N/A N/A
-255 N/A N/A N/A 25P01 N/A N/A N/A N/A
-257 1000 N/A N/A N/A N/A N/A N/A N/A
-263 54 N/A N/A N/A N/A N/A N/A N/A
-268 1 2601, 2627 -803 23505 1062 -301 N/A N/A
-280 N/A N/A -102 N/A N/A -274 N/A N/A
-282 N/A N/A -105 N/A N/A N/A N/A N/A
-294 937 N/A -119, -122 N/A N/A -277 N/A N/A
-316 N/A N/A -605 N/A N/A -289 N/A N/A
-324 960 N/A -203 N/A N/A N/A N/A N/A
-350 1408 N/A N/A N/A N/A N/A N/A N/A
-360 N/A N/A -118 N/A N/A N/A N/A N/A
-371 1452 N/A -603, -673 N/A N/A N/A N/A N/A
-382 1756 N/A N/A N/A N/A N/A N/A N/A
-387 1017, 1045 715, 4002, 4003, 4008 -1403, -1404 N/A 1045 N/A N/A 24
-388 1536 N/A N/A N/A N/A N/A N/A N/A
-391 1400, 1407 N/A -407 22004, 23502 N/A -287 N/A N/A
-400 1002 N/A N/A N/A N/A N/A N/A N/A
-517 N/A N/A -602 N/A N/A N/A N/A N/A
-530 2290 N/A -193 23514 N/A -677 N/A N/A
-551 N/A N/A -613 N/A N/A N/A N/A N/A
-674 N/A 2812 N/A N/A N/A -328 N/A N/A
-681 N/A 2812 -121 N/A N/A -272 N/A N/A
-691 2291 547 -530 23503 1452 -461 19 N/A
-743 955 6000, 6006, 6008 N/A N/A N/A -288 N/A N/A
-930 1033, 1034, 12154, 12203, 12224, 12500, 12560 11, 17, 708, 709, 711, 4014, 17142 -1013 08000, 08001, 08004, 08006, 08007, 08000 1044 -10, -10709 N/A N/A
-942 N/A N/A -903 N/A N/A N/A N/A N/A
-1202 N/A N/A -801 N/A N/A -304 N/A N/A
-1218 N/A 3048, 3049, 3050 -180, -181 N/A N/A -303 N/A N/A
-1260 932 N/A -190 N/A N/A -339 N/A N/A
-1279 1401 N/A -433, -99998 N/A N/A N/A N/A N/A
-1349 1722 N/A N/A N/A N/A -339 N/A N/A

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.

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