SQLGetDiagField - Return diagnostic information (extensible)

SQLGetDiagField() returns the diagnostic information associated with the most recently called DB2® for i CLI function for a particular statement, connection, or environment handle.

The information consists of a standardized SQLSTATE, an error code, and a text message. Refer to Diagnostics in a DB2 for i CLI application for more information.

Call SQLGetDiagField() after receiving a return code of SQL_ERROR or SQL_SUCCESS_WITH_INFO from another function call.

Note: Some database servers might provide product-specific diagnostic information after returning SQL_NO_DATA_FOUND from the processing of a statement.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLGetDiagFieldW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLGetDiagField (SQLSMALLINT      htype,
                          SQLINTEGER        handle,
                          SQLSMALLINT       recNum,
                          SQLSMALLINT       diagId,
                          SQLPOINTER        diagInfo,
                          SQLSMALLINT       bLen,
                          SQLSMALLINT       *sLen);

Function arguments

Table 1. SQLGetDiagField arguments
Data type	Argument	Use	Description
SQLSMALLINT	hType	Input	Handle type.
SQLINTEGER	handle	Input	Handle for which the diagnostic information is wanted.
SQLSMALLINT	recNum	Input	If there are multiple errors, this indicates which one should be retrieved. If header information is requested, this must be 0. The first error record is number 1.
SQLSMALLINT	diagId	Input	See Table 2.
SQLPOINTER	diagInfo	Output	Buffer for diagnostic information.
SQLSMALLINT	bLen	Input	Length of diagInfo, if requested data is a character string; otherwise, unused.
SQLSMALLINT *	sLen	Output	Length of complete diagnostic information, If the requested data is a character string; otherwise, unused.

Table 2. diagId types
Descriptor	Type	Description
SQL_DIAG_MESSAGE_TEXT	CHAR(254)	The implementation-defined message text relating to the diagnostic record.
SQL_DIAG_NATIVE	INTEGER	The implementation-defined error code relating to the diagnostic record. Portable applications should not base their behavior on this value.
SQL_DIAG_NUMBER	INTEGER	The number of diagnostic records available for the specified handle.
SQL_DIAG_RETURNCODE	SMALLINT	Return code of the underlying function. Can be SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, or SQL_ERROR.
SQL_DIAG_ROW_COUNT	INTEGER	The number of rows for the specified handle, if the handle is a statement handle.
SQL_DIAG_SERVER_NAME	CHAR(128)	The server name that the diagnostic record relates to, as it is supplied on the `SQLConnect()` statement that establishes the connection.
SQL_DIAG_SQLSTATE	CHAR(5)	The 5-character SQLSTATE code relating to the diagnostic record. The SQLSTATE code provides a portable diagnostic indication.

Usage

The SQLSTATEs are those defined by the X/OPEN SQL CAE and the X/Open SQL CLI snapshot, augmented with SQLSTATE values.

If diagnostic information generated by one DB2 for i CLI function is not retrieved before a function other than SQLGetDiagField() is called with the same handle, the information for the previous function call is lost. This is true whether diagnostic information is generated for the second DB2 for i CLI function call.

Multiple diagnostic messages might be available after a given DB2 for i CLI function call. These messages can be retrieved one at a time by repeatedly calling SQLGetDiagField(). When there are no more messages to retrieve, SQL_NO_DATA_FOUND is returned.

Diagnostic information stored under a given handle is cleared when a call is made to SQLGetDiagField() with that handle, or when another DB2 for i CLI function call is made with that handle. However, information associated with a given handle type is not cleared by a call to SQLGetDiagField() with an associated but different handle type. For example, a call to SQLGetDiagField() with a connection handle input does not clear errors associated with any statement handles under that connection.

SQL_SUCCESS is returned even if the buffer for the error message (szDiagFieldMsg) is too short. This is because the application is not able to retrieve the same error message by calling SQLGetDiagField() again. The actual length of the message text is returned in the pcbDiagFieldMsg.

To avoid truncation of the first level error message, declare a buffer length of SQL_MAX_MESSAGE_LENGTH + 1. To avoid truncation of the second level error message, set the size of the buffer to a value greater than SQL_MAX_MESSAGE_LENGTH.

Return codes

SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND

SQL_NO_DATA_FOUND is returned if no diagnostic information is available for the input handle, or if all of the messages have been retrieved through calls to SQLGetDiagField().

SQL_ERROR is returned if the argument diagInfo or sLen is a null pointer.

Diagnostics

SQLSTATEs are not defined, because SQLGetDiagField() does not generate diagnostic information for itself.

Restrictions

Although ODBC also returns X/Open SQL CAE SQLSTATEs, only DB2 for i CLI returns the additional IBM® defined SQLSTATEs. The ODBC Driver Manager also returns SQLSTATE values in addition to the standard ones. For more information about ODBC specific SQLSTATEs refer to Microsoft ODBC Programmer's Reference.

Because of this, you should only build dependencies on the standard SQLSTATEs. This means any branching logic in the application should only rely on the standard SQLSTATEs. The augmented SQLSTATEs are most useful for debugging purposes.