SQL Warnings with non-Informix databases

SQL Warnings are now propagated for all database drivers, and can set the SQLCA.SQLAWARN, SQLSTATE and SQLERRMESSAGE registers.

Before version 2.20, is was impossible for a non-Informix driver to return SQL Warning information in SQLCA, SQLSTATE and SQLERRMESSAGE.

This new behavior will have no impact if you test SQL Errors with STATUS or SQLCA.SQLCODE, as these registers remain zero if an SQL Warning is raised. However, if you are using SQLSTATE to check for SQL Errors, you must now distinguish SQLSTATE of class 01: These are SQL Warnings, not SQL errors.

In this example, when connected to IBM® DB2®, the SQLSTATE register will get the value 01504 indicating that all rows of the table have been deleted. As a result, testing SQLSTATE against 00000 will evaluate to false, and run into the error handling block, which is unexpected:

MAIN
   DATABASE stores
   WHENEVER ERROR CONTINUE
   DELETE FROM customer 
   IF SQLSTATE <> "00000" THEN
      -- handle error 
   END IF
END MAIN

To check for successful SQL execution with or without warning, you can, for example, code:

MAIN
   DATABASE stores 
   WHENEVER ERROR CONTINUE
   DELETE FROM customer 
   IF NOT (SQLSTATE=="00000" OR SQLSTATE MATCHES "01*") THEN
      -- handle error 
   END IF
END MAIN