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.