Warnings in SQLSTATE

When the database server executes an SQL statement successfully, but encounters a warning condition, it sets the class code of SQLSTATE to "01". The subclass code then indicates the cause of the warning. This warning can be either of the following types:

An ANSI or X/Open warning message has a subclass code in the range "000" to "006".
The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "ISO 9075" to indicate ANSI or X/Open as the source of the warning.
A warning message specific to HCL Informix® has a subclass code in the range "I01" to "I11" (see Table 1).
The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "IX000" to indicate an exception, which is specific to Informix, as the source of the warning.
A user-defined warning message from a user-defined routine has a subclass code of "U01".
The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "U0001" to indicate a user-defined routine as the source of the warning.

The following table lists the warning messages specific to Informix and the SQL statements and conditions that generate the warning.

Table 1. SQL statements that set a warning specific to Informix for a given condition
Warning value	SQL statement	Warning condition
"01I01"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that uses transactions.
"01I03"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened an ANSI-compliant database.
"01I04"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that the Informix manages.
"01I05"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that is on a host database server that requires float-to-decimal conversion for FLOAT columns (or smallfloat-to-decimal conversions for SMALLFLOAT columns).
"01I06"	All statements	The statement executed contains the Informix extension to SQL (only when the DBANSIWARN environment variable is set).
"01I07"	PREPARE DESCRIBE	A prepared UPDATE or DELETE statement has no WHERE clause. The operation affects all rows of the table.
"01I09"	FETCH SELECT...INTO EXECUTE...INTO	The number of items in the select list does not equal the number of host variables in the INTO clause.
"01I10"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	The database server is currently running in secondary mode. The database server is a secondary server in a data-replication pair; therefore, the database server is available only for read operations.
"01I11"	Other statements (when your application activates the DATASKIP feature)	A data fragment (a dbspace) was skipped during query processing.

To check for a warning, your code only needs to verify the first two characters of SQLSTATE. However, to identify the particular warning, you need to examine the subclass code. You might also want to use the GET DIAGNOSTICS statement to obtain the warning message from the MESSAGE_TEXT field.

For example, the following block of code determines what database a CONNECT statement has opened.

int trans_db, ansi_db, online_db = 0;
⋮

msg = "CONNECT stmt";
EXEC SQL connect to 'stores7';
if(!strncmp(SQLSTATE, "02", 2)) /* < 0 is an error */
   err_chk(msg);
if (!strncmp(SQLSTATE, "01", 2))
   {
   if (!strncmp(SQLSTATE[2], "I01", 3))
      trans_db = 1;
   if (!strncmp(SQLSTATE[2], "I03", 3)) 
      ansi_db = 1;
   if (!strncmp(SQLSTATE[2], "I04", 3)) 
      online_db = 1;
   }

The preceding code fragment checks SQLSTATE with the system strncmp() function. The getdiag sample program (Guide to the getdiag.ec file) uses the sqlstate_err() function to check the success of an SQL statement by comparing the first two characters of SQLSTATE with the string "01". For more information about the values of SQLSTATE that the CONNECT, CREATE DATABASE, DATABASE, and SET CONNECTION statements set, see Determine features of the database server.