Handling SQL warnings

Some SQL instructions can produce SQL Warnings. Compared to SQL Errors which do normally stop the program execution, SQL Warnings indicate a minor issue that can often be ignored. For example, when connecting to an IBM^® Informix^® database, a warning is returned to indicate that a database was opened, and an other warning might be returned if that database supports transactions. None of these facts are critical problems, but knowing that information can help for further program execution.

If an SQL Warning is raised, SQLCA.SQLCODE / STATUS remain zero, and the program flow continues. To detect if an SQL Warning occurs, the SQLCA.SQLAWARN register must be used. SQLCA.SQLAWARN is defined as a CHAR(7) variable. If SQLCA.SQLAWARN[1] contains the W letter, it means that the last SQL instruction has returned a warning. The other character positions (SQLCA.SQLAWARN[2-8]) may contain W letters. Each position from 2 to 8 has a special meaning according to the database server type, and the SQL instructions type.

If SQLCA.SQLAWARN is set, you can also check the SQLSTATE and SQLCA.SQLERRD[2] registers to get more details about the warning. The SQLERRMESSAGE register might also contain the warning description.

In the next example, the program connects to a database and displays the content of the SQLCA.SQLAWARN register. When connecting to an IBM Informix database with transactions, the program will display [WW W ]:

MAIN
  DATABASE stores 
  DISPLAY "[", sqlca.sqlawarn, "]"
END MAIN

By default SQL Warnings do not stop the program execution. To trap SQL Warnings with an exception handle, use the WHENEVER WARNING instruction, as shown in this example.

MAIN
  DEFINE cust_name VARCHAR(50)
  DATABASE stores 
  WHENEVER WARNING STOP
  SELECT cust_lname, cust_address INTO cust_name 
       FROM customer WHERE cust_id = 101
  WHENEVER WARNING CONTINUE
END MAIN

The SELECT statement in this example uses two columns in the select list, but only one INTO variable is provided. This is legal and does not raise an SQL Error, however, it will set the SQLCA.SQLAWARN register to indicate that the number of target variables does not match the select-list items.