SQLSTATES for CLI

SQLSTATES are alphanumeric strings of five characters (bytes) with a format of ccsss, where cc indicates class and sss indicates subclass.
Any SQLSTATE that has a class of:
  • '01', is a warning.
  • 'HY', is generated by the CLI or ODBC driver.
  • 'IM', is generated by the ODBC driver manager.
Note: Versions of CLI before Version 5 returned SQLSTATEs with a class of 'S1' rather than 'HY'. To force the CLI driver to return 'S1' SQLSTATEs, the application must set the environment attribute SQL_ATTR_ODBC_VERSION to the value SQL_OV_ODBC2.

CLI SQLSTATEs include both additional IBM defined SQLSTATEs that are returned by the database server, and CLI defined SQLSTATEs for conditions that are not defined in the ODBC version 3 and ISO SQL/CLI specifications. This allows for the maximum amount of diagnostic information to be returned. When running applications in an ODBC environment, it is also possible to receive ODBC defined SQLSTATEs.

Follow these guidelines for using SQLSTATEs within your application:
  • Always check the function return code before calling SQLGetDiagRec() to determine if diagnostic information is available.
  • Use the SQLSTATEs rather than the native error code.
  • To increase your application's portability, only build dependencies on the subset of CLI SQLSTATEs that are defined by the ODBC version 3 and ISO SQL/CLI specifications, and return the additional ones as information only. A dependency in an application is a logic flow decision based on specific SQLSTATEs.
    Note: It might be useful to build dependencies on the class (the first 2 characters) of the SQLSTATEs.
  • For maximum diagnostic information, return the text message along with the SQLSTATE (if applicable, the text message will also include the IBM defined SQLSTATE). It is also useful for the application to print out the name of the function that returned the error.
  • Ensure that the string allocated for the SQLSTATE includes space for the null termination character returned by CLI.
The code segment from utilcli.c shows how diagnostic information, such as SQLSTATEs, can be retrieved and displayed: 
void HandleDiagnosticsPrint(SQLSMALLINT htype, /* handle type identifier */
                            SQLHANDLE hndl /* handle */ )
{
  SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1];
  SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
  SQLINTEGER sqlcode;
  SQLSMALLINT length, i;

  i = 1;

  /* get multiple field settings of diagnostic record */
  while (SQLGetDiagRec(htype,
                       hndl,
                       i,
                       sqlstate,
                       &sqlcode,
                       message,
                       SQL_MAX_MESSAGE_LENGTH + 1,
                       &length) == SQL_SUCCESS)
  {
    printf("\n  SQLSTATE          = 
    printf("  Native Error Code = 
    printf("
    i++;
  }

  printf("-------------------------\n");
}

You can use the CLI/ODBC trace facility to gain a better understanding of how your application calls Db2®, including any errors that might occur.