Handing SQL errors

After executing an SQL statement, check the STATUS, SQLSTATE, SQLERRMESSAGE and the SQLCA record to get the description of the error. When the statement has been executed with errors, STATUS and SQLCA.SQLCODE contain the SQL Error Code. If no error occurs, STATUS and SQLCA.SQLCODE are set to zero.

The execution of an SQL statement can be controlled by a WHENEVER ERROR exception handler:

MAIN
  DATABASE stores 
  WHENEVER ERROR CONTINUE
  SELECT COUNT(*) FROM customer
  IF sqlca.sqlcode THEN
     ERROR "SQL Error occurred:", sqlca.sqlcode
  END IF
  WHENEVER ERROR STOP
END MAIN

The SQL Error Codes are not standard. For example, ORACLE returns 903 when a table name does not exist.

By convention, the STATUS and SQLCA.SQLCODE variables always use IBM^® Informix^® SQL Error Codes. When using IBM Informix, both STATUS and SQLCA.SQLCODE variables contain the native Informix error code. When using other database servers, the database interface automatically converts native SQL Error Codes to IBM Informix Error Codes. If no equivalent Informix Error Code can be found, the interface returns -6372 in STATUS and SQLCA.SQLCODE.

If an SQL error occurs when using IBM Informix, the SQLCA variable is filled with standard information as described in the Informix documentation. When using other database servers, the native SQL Error Code is available in the SQLCA.SQLERRD[2] register. SQL Error Codes in SQLCA.SQLERRD[2] are always negative, even if the database server defines positives SQL Error Codes. Additionally, if the target database API supports ANSI SQL states, the SQLSTATE code is returned in SQLCA.SQLERRM.

The NOTFOUND (100) execution status is returned after a FETCH, when no rows are found.