How to process ODBC errors (ODBC)
Two ODBC function calls can be used to retrieve ODBC messages: SQLGetDiagRec and SQLGetDiagField. To obtain primary ODBC-related information in the SQLState, pfNative, and ErrorMessage diagnostic fields, call SQLGetDiagRec until it returns SQL_NO_DATA. For each diagnostic record, SQLGetDiagField can be called to retrieve individual fields. All driver-specific fields must be retrieved using SQLGetDiagField.
SQLGetDiagRec and SQLGetDiagField are processed by ODBC Driver Manager, not an individual driver. ODBC Driver Manager does not cache driver-specific diagnostic fields until a successful connection has been made. Calling SQLGetDiagField for driver-specific diagnostic fields is not possible before a successful connection. This includes the ODBC connection commands, even if they return SQL_SUCCESS_WITH_INFO. Driver-specific diagnostic fields will not be available until the next ODBC function call.
Examples
The following example shows a simple error handler that calls SQLGetDiagRec for the standard ODBC information. It then tests for a valid connection, and if there is, it calls SQLGetDiagField for the Microsoft® SQL Server™ ODBC driver-specific diagnostic fields.
// Example of SQL Server ODBC driver-specific options
// on SQLGetDiagField.
//
// This application assumes the existence of the following
// stored procedure:
//
// CREATE PROCEDURE BadOne AS SELECT * FROM NotThere
//
// where no object named NotThere exists.
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#define MAXBUFLEN 256
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
char logstring[MAXBUFLEN] = "";
void ProcessLogMessages(SQLSMALLINT plm_handle_type,
SQLHANDLE plm_handle, char *logstring,
int ConnInd);
int main() {
RETCODE retcode;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if( (retcode != SQL_SUCCESS_WITH_INFO) &&
(retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(Env) Failed\n\n");
return(9);
}
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
if( (retcode != SQL_SUCCESS_WITH_INFO) &&
(retcode != SQL_SUCCESS)) {
printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
return(9);
}
// Allocate ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
if( (retcode != SQL_SUCCESS_WITH_INFO) &&
(retcode != SQL_SUCCESS)) {
printf("SQLAllocHandle(hdbc1) Failed\n\n");
return(9);
}
retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,
"sa", SQL_NTS, "MyPassWord", SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
"SQLConnect() Failed\n\n", FALSE);
return(9);
}
else {
ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
"\nConnect Successful\n\n", FALSE);
}
// Allocate statement handle, and then execute command.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
"SQLAllocHandle(hstmt1) Failed\n\n",
TRUE);
return(9);
}
retcode = SQLExecDirect(hstmt1, "exec BadOne", SQL_NTS);
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
ProcessLogMessages(SQL_HANDLE_STMT, hstmt1,
"SQLExecute() Failed\n\n", TRUE);
return(9);
}
// Clear any result sets generated.
while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
;
/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);
}
void ProcessLogMessages(SQLSMALLINT plm_handle_type,
SQLHANDLE plm_handle,
char *logstring, int ConnInd)
{
RETCODE plm_retcode = SQL_SUCCESS;
UCHAR plm_szSqlState[MAXBUFLEN] = "",
plm_szErrorMsg[MAXBUFLEN] = "";
SDWORD plm_pfNativeError = 0L;
SWORD plm_pcbErrorMsg = 0;
SQLSMALLINT plm_cRecNmbr = 1;
SDWORD plm_SS_MsgState = 0, plm_SS_Severity = 0;
SQLINTEGER plm_Rownumber = 0;
USHORT plm_SS_Line;
SQLSMALLINT plm_cbSS_Procname, plm_cbSS_Srvname;
SQLCHAR plm_SS_Procname[MAXNAME], plm_SS_Srvname[MAXNAME];
printf(logstring);
while (plm_retcode != SQL_NO_DATA_FOUND) {
plm_retcode = SQLGetDiagRec(plm_handle_type, plm_handle,
plm_cRecNmbr, plm_szSqlState, &plm_pfNativeError,
plm_szErrorMsg, MAXBUFLEN - 1, &plm_pcbErrorMsg);
// Note that if the application has not yet made a
// successful connection, the SQLGetDiagField
// information has not yet been cached by ODBC
// Driver Manager and these calls to SQLGetDiagField
// will fail.
if (plm_retcode != SQL_NO_DATA_FOUND) {
if (ConnInd) {
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_ROW_NUMBER, &plm_Rownumber,
SQL_IS_INTEGER,
NULL);
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_SS_LINE, &plm_SS_Line,
SQL_IS_INTEGER,
NULL);
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_SS_MSGSTATE, &plm_SS_MsgState,
SQL_IS_INTEGER,
NULL);
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_SS_SEVERITY, &plm_SS_Severity,
SQL_IS_INTEGER,
NULL);
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_SS_PROCNAME, &plm_SS_Procname,
sizeof(plm_SS_Procname),
&plm_cbSS_Procname);
plm_retcode = SQLGetDiagField(
plm_handle_type, plm_handle, plm_cRecNmbr,
SQL_DIAG_SS_SRVNAME, &plm_SS_Srvname,
sizeof(plm_SS_Srvname),
&plm_cbSS_Srvname);
}
printf("szSqlState = %s\n",plm_szSqlState);
printf("pfNativeError = %d\n",plm_pfNativeError);
printf("szErrorMsg = %s\n",plm_szErrorMsg);
printf("pcbErrorMsg = %d\n\n",plm_pcbErrorMsg);
if (ConnInd) {
printf("ODBCRowNumber = %d\n", plm_Rownumber);
printf("SSrvrLine = %d\n", plm_Rownumber);
printf("SSrvrMsgState = %d\n",plm_SS_MsgState);
printf("SSrvrSeverity = %d\n",plm_SS_Severity);
printf("SSrvrProcname = %s\n",plm_SS_Procname);
printf("SSrvrSrvname = %s\n\n",plm_SS_Srvname);
}
}
plm_cRecNmbr++; //Increment to next diagnostic record.
} // End while.
}