Exception handling with SQLSTATE

It is recommended that you obtain diagnostic information about SQL statements with the SQLSTATE variable and the GET DIAGNOSTICS statement.
Important: SQLSTATE is a more effective way to detect and handle error messages than the SQLCODE variable because SQLSTATE supports multiple exceptions. SQLSTATE is also more portable because it conforms to ANSI and X/Open standards. supports the sqlca structure and SQLCODE for compatibility with earlier versions and for exceptions specific to HCL OneDB™.

After the database server executes an SQL statement, it sets SQLSTATE with a value that indicates the success or failure of the statement. From this value, your program can determine if it needs to perform further diagnostic tests. If SQLSTATE indicates a problem, you can use the GET DIAGNOSTICS statement to obtain more information.

This section describes how to use the SQLSTATE variable and the GET DIAGNOSTICS statement to perform exception handling. It describes the following topics:
  • Using the GET DIAGNOSTICS statement to access fields of the diagnostics area
  • Understanding the format of the SQLSTATE values
  • Using SQLSTATE to check for the different types of exceptions