The SQLSTATE variable

The SQLSTATE variable is a five-character string that the database server sets after it executes each SQL statement.

The header file, sqlca.h, declares SQLSTATE as a global variable. Since the preprocessor automatically includes sqlca.h in the program, you do not need to declare SQLSTATE.

After the database server executes an SQL statement, the database server automatically updates the SQLSTATE variable as follows:
  • The database server stores the exception value in the RETURNED_SQLSTATE field of the diagnostics area.
  • copies the value of the RETURNED_SQLSTATE field to the global SQLSTATE variable.
These updates to the SQLSTATE variable are equivalent to the execution of the following GET DIAGNOSTICS statement immediately after an SQL statement: 
EXEC SQL get diagnostics exception 1 :SQLSTATE = RETURNED_SQLSTATE;
Tip: At run time, automatically copies the value of the RETURNED_SQLSTATE field into the global SQLSTATE variable. Therefore, you do not usually need to access the RETURNED_SQLSTATE field directly.

The value in SQLSTATE is the status of the most-recently executed SQL statement before the GET DIAGNOSTICS statement executed. If the database server encounters an error when it executes the GET DIAGNOSTICS statement, it sets SQLSTATE to "IX001" and sets SQLCODE (and sqlca.sqlcode) to the value of the error number that corresponds to the error; the contents of the diagnostics area are undefined.

The SQLSTATE variable holds the ANSI-defined value for the exception. Each SQLSTATE value has an associated status code that is specific to HCL OneDB™. You can obtain the value of this status code, which is specific to HCL OneDB, from either of the following items:
  • The INFORMIX_SQLCODE field of the diagnostics area
  • The SQLCODE variable