Using SQLGetDiagRec and SQLGetDiagField

Applications call SQLGetDiagRec or SQLGetDiagField to retrieve diagnostic information. These functions accept an environment, connection, statement, or descriptor handle and return diagnostics from the function that last used that handle. The diagnostics logged on a particular handle are discarded when a new function is called using that handle. If the function returned multiple diagnostic records, the application calls these functions multiple times; the total number of status records is retrieved by calling SQLGetDiagField for the header record (record 0) with the %SQL_DIAG_NUMBER option.

Applications retrieve individual diagnostic fields by calling SQLGetDiagField and specifying the field to retrieve. Certain diagnostic fields do not have any meaning for certain types of handles. For a list of diagnostic fields and their meanings, see the SQLGetDiagField function description.

Applications retrieve the SQLSTATE, native error code, and diagnostic message in a single call by calling SQLGetDiagRec; SQLGetDiagRec cannot be used to retrieve information from the header record.

The following function, included in ODBCAPI352.INC, uses SQLGetDiagRec and SQLGetDiagField to retrieve error information:

Code:

' ========================================================================================
' SQLGetErrorInfo uses SQLGetDiagRec to retrieve an error description.
' HandleType must be one of the following:
' %SQL_HANDLE_ENV
' %SQL_HANDLE_DBC
' %SQL_HANDLE_STMT
' %SQL_HANDLE_DESC
' hndl is the handle of the environment, connection, statement or descriptor.
' ========================================================================================
FUNCTION SQLGetErrorInfo (BYVAL HandleType AS INTEGER, BYVAL hndl AS DWORD, OPTIONAL BYVAL iErrorCode AS INTEGER) AS STRING

LOCAL iResult AS INTEGER
LOCAL szSqlState AS ASCIIZ * 6
LOCAL lNativeError AS DWORD
LOCAL szErrMsg AS ASCIIZ * %SQL_MAX_MESSAGE_LENGTH + 1
LOCAL strErrMsg AS STRING
LOCAL cbbytes AS INTEGER
LOCAL ErrorCount AS LONG
LOCAL i AS LONG

iResult = SQLGetDiagField(HandleType, hndl, 0, %SQL_DIAG_NUMBER, ErrorCount, %SQL_IS_INTEGER, cbbytes)

IF ErrorCount THEN
FOR i = 1 TO ErrorCount
iResult = SQLGetDiagRec(HandleType, hndl, 1, szSqlState, lNativeError, szErrMsg, SIZEOF(szErrMsg), cbbytes)
IF iResult <> %SQL_SUCCESS AND iResult <> %SQL_SUCCESS_WITH_INFO THEN EXIT FOR
strErrMsg = strErrMsg & "SqlState: " & szSqlState & $CRLF & _
"Native error: " & FORMAT$(lNativeError) & $CRLF & szErrMsg
NEXT
ELSEIF iErrorCode THEN
SELECT CASE iErrorCode
CASE -1 : strErrMsg = "SQL error" ' "SQL_ERROR"
CASE 2 : strErrMsg = "Still executing" ' "SQL_STILL_EXECUTING"
CASE -2 : strErrMsg = "Invalid handle" '"SQL_INVALID_HANDLE"
CASE 99 : strErrMsg = "Need data" ' "SQL_NEED_DATA"
CASE 100 : strErrMsg = "No data" '"SQL_NO_DATA"
CASE ELSE
strErrMsg = "Error " & FORMAT$(iErrorCode)
END SELECT
END IF

FUNCTION = strErrMsg

END FUNCTION
' ========================================================================================