Diagnostics refers to dealing with warning or error conditions generated within an application. There are two levels of diagnostics when calling DB2 CLI functions :
Each CLI function returns the function return code as a basic diagnostic. Both SQLGetDiagRec() and SQLGetDiagField() functions provide more detailed diagnostic information. The SQLGetSQLCA() function provides access to the SQLCA, if the diagnostic is reported by the data source. This arrangement lets applications handle the basic flow control based on Return Codes, and the SQLSTATES allow determination of the specific causes of failure and specific error handling.
Both SQLGetDiagRec() and SQLGetDiagField() return three pieces of information:
For the detailed function information and example usage, refer to SQLGetDiagRec - Get Multiple Fields Settings of Diagnostic Record and SQLGetDiagField - Get a Field of Diagnostic Data.
SQLGetSQLCA() returns the SQLCA for access to specific fields, but should never be used as a substitute for SQLGetDiagRec() or SQLGetDiagField().
The following table lists all possible return codes for DB2 CLI functions.
Each function description in Chapter 5. "Functions" lists the possible codes returned for each function.
Table 2. DB2 CLI Function Return Codes
| Return Code | Explanation |
|---|---|
| SQL_SUCCESS | The function completed successfully, no additional SQLSTATE information is available. |
| SQL_SUCCESS_WITH_INFO | The function completed successfully, with a warning or other information. Call SQLGetDiagRec() to receive the SQLSTATE and any other informational messages or warnings. The SQLSTATE will have a class of '01', see Table 196. |
| SQL_STILL_EXECUTING | The function is running asynchronously and has not yet completed. The DB2 CLI driver has returned control to the application after calling the function, but the function has not yet finished executing. See "Asynchronous Execution of CLI" for complete details. |
| SQL_NO_DATA_FOUND | The function returned successfully, but no relevant data was found. When this is returned after the execution of an SQL statement, additional information may be available and can be obtained by calling SQLGetDiagRec(). |
| SQL_NEED_DATA | The application tried to execute an SQL statement but DB2 CLI lacks parameter data that the application had indicated would be passed at execute time. For more information, refer to "Sending/Retrieving Long Data in Pieces". |
| SQL_ERROR | The function failed. Call SQLGetDiagRec() to receive the SQLSTATE and any other error information. |
| SQL_INVALID_HANDLE | The function failed due to an invalid input handle (environment, connection or statement handle). This is a programming error. No further information is available. |
SQLSTATEs are alphanumeric strings of 5 characters (bytes) with a format of ccsss, where cc indicates class and sss indicates subclass. Any SQLSTATE that has a class of:
| Note: | Previous versions of DB2 CLI returned SQLSTATEs with a class of 'S1' rather than 'HY'. To force the CLI driver to return 'S1' SQLSTATEs, the application should set the environment attribute SQL_ATTR_ODBC_VERSION to the value SQL_OV_ODBC2. See SQLSetEnvAttr - Set Environment Attribute and Appendix B. "Migrating Applications" for more information. |
DB2 CLI SQLSTATEs include both additional IBM defined SQLSTATEs that are returned by the database server, and DB2 CLI defined SQLSTATEs for conditions that are not defined in the ODBC v3 and ISO SQL/CLI specification. This allows for the maximum amount of diagnostic information to be returned. When running applications in a ODBC environment, it is also possible to receive ODBC defined SQLSTATEs.
Follow these guidelines for using SQLSTATEs within your application:
| Note: | It may be useful to build dependencies on the class (the first 2 characters) of the SQLSTATEs. |
Refer to Table 196 for a listing and description of the SQLSTATEs explicitly returned by DB2 CLI.
To gain a better understanding of how your application calls DB2, including any errors that may occur, refer to the CLI/ODBC configuration keyword "TRACE" for information on using the CLI/ODBC trace facility.
Embedded applications rely on the SQLCA for all diagnostic information. Although DB2 CLI applications can retrieve much of the same information by using SQLGetDiagRec(), there may still be a need for the application to access the SQLCA related to the processing of a statement. (For example, after preparing a statement, the SQLCA will contain the relative cost of executing the statement.) The SQLCA only contains meaningful information if there was an interaction with the data source on the previous request (for example: connect, prepare, execute, fetch, disconnect).
The SQLGetSQLCA() function is used to retrieve this structure. Refer to SQLGetSQLCA - Get SQLCA Data Structure for more information.
SQLGetSQLCA() should never be used as a substitute for SQLGetDiagRec() or SQLGetDiagField().