SQLGetDiagRec - Return diagnostic information (concise)

SQLGetDiagRec() 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, the error code, and a text message. See Diagnostics in a DB2 for i CLI application for more information.

Call SQLGetDiagRec() 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 SQLGetDiagRecW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLGetDiagRec (SQLSMALLINT  hType,
                        SQLINTEGER    handle,
                        SQLSMALLINT   recNum,
                        SQLCHAR       *szSqlState,
                        SQLINTEGER    *pfNativeError,
                        SQLCHAR       *szErrorMsg,
                        SQLSMALLINT   cbErrorMsgMax,
                        SQLSMALLINT   *pcbErrorMsg);

Function arguments

Table 1. SQLGetDiagRec 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.
SQLCHAR *	szSqlState	Output	SQLSTATE as a string of 5 characters terminated by a null character. The first 2 characters indicate error class; the next 3 indicate subclass. The values correspond directly to SQLSTATE values defined in the X/Open SQL CAE specification and the ODBC specification, augmented with IBM® specific and product specific SQLSTATE values.
SQLINTEGER *	pfNativeError	Output	Error code. In DB2 for i CLI, the pfNativeError argument contains the SQLCODE value returned by the Database Management System (DBMS). If the error is generated by DB2 for i CLI and not the DBMS, then this field is set to -99999.
SQLCHAR *	szErrorMsg	Output	Pointer to buffer to contain the implementation defined message text. In DB2 for i CLI, only the DBMS generated messages are returned; DB2 for i CLI itself does not return any message text describing the problem.
SQLSMALLINT	cbErrorMsgMax	Input	Maximum (that is, the allocated) length of the buffer szErrorMsg. The recommended length to allocate is SQL_MAX_MESSAGE_LENGTH + 1.
SQLSMALLINT *	pcbErrorMsg	Output	Pointer to total number of bytes available to return to the szErrorMsg buffer. This does not include the null termination character.

Usage

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

If diagnostic information generated by one DB2 for i CLI function is not retrieved before a function other than SQLGetDiagRec() 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 SQLGetDiagRec(). When there are no more messages to retrieve, SQL_NO_DATA_FOUND is returned, the SQLSTATE is set to "00000", pfNativeError is set to 0, and pcbErrorMsg and szErrorMsg are undefined.

Diagnostic information stored under a given handle is cleared when a call is made to SQLGetDiagRec() 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 SQLGetDiagRec() with an associated but different handle type. For example, a call to SQLGetDiagRec() 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 (szErrorMsg) is too short, because the application is not able to retrieve the same error message by calling SQLGetDiagRec() again. The actual length of the message text is returned in the pcbErrorMsg.

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 SQLGetDiagRec().

SQL_ERROR is returned if the argument szSqlState, pfNativeError, szErrorMsg , or pcbErrorMsg is a null pointer.

Diagnostics

SQLSTATEs are not defined because SQLGetDiagRec() 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.

References

SQLGetDiagField - Return diagnostic information (extensible)