After an SQL statement has been executed, the ESQL program should check whether execution was successful or whether an error occurred. The ESQL interface provides the host variables SQLSTATE and SQLCODE for this purpose. These are used to store information on the execution of an SQL statement. SQLSTATE contains an SQL status code and SQLCODE contains an SQL return code which has the same function as the SQL status code and which is supported for reasons of compatibility with earlier versions of SESAM/SQL.
The 5-character, alphanumeric SQL status codes are made up of a 2-character part for the class of the status code and a 3-character part for the subclass. If an SQL statement is executed without errors, this is indicated by values for SQLSTATE which have the classes “successful execution”, “warning” or “no data”. If execution was errored, SQLSTATE contains information on the type of error.
The numeric values for SQLCODE are as follows: 0 for successful execution, 100 if a table is empty or the end of the table has been reached and 10 or 50 if a warning is issued. In the event of an error, SQLCODE contains a negative value indicating the error type.
There are two ways of checking whether an SQL statement was executed properly and initiating appropriate action in the host program depending on the SQL status code or SQL return code. On the one hand, the ESQL programmer can use host language statements to control program execution on the basis of the value in SQLSTATE (or SQLCODE). Another option involves the use of the SQL statement WHENEVER.