Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

ODBC diagnostics and error status codes

Contents

Introduction

This article describes how to retrieve and interpret ODBC diagnostic information. This guide also links to a page that provides a complete list of SQLSTATE status return codes and the ODBC functions that return them.

ODBC status returns

SQLGetDiagRec and SQLGetDiagField return SQLSTATE values. SQLSTATE values are strings that contain five characters. The appendices in this article list the SQLSTATE values that an ODBC driver can return for SQLGetDiagRec.

The character string value returned in an SQLSTATE consists of a two-character class value followed by a three-character subclass value. A class value of 01 indicates a warning and is accompanied by a return code of SQL_SUCCESS_WITH_INFO. Class values other than 01, except for the class IM, indicate an error and are accompanied by a return value of SQL_ERROR. The class IM is specific to warnings and errors that derive from the implementation of ODBC itself. The subclass value 000 in any class indicates that there is no subclass for that SQLSTATE. The assignment of class and subclass values is defined by SQL-92.

Note Although the successful execution of a function is normally indicated by a return value of SQL_SUCCESS, the SQLSTATE 00000 also indicates success.

All ODBC APIs return a status value that you can use to check whether the function succeeded. In C, you can test the return value from an ODBC function using the SQL_SUCCEEDED macro. For example:

SQLRETURN fsts;
/* Assume for this example the environment has already been allocated */
SQLHENV envh;
SQLHDBC dbch;
fsts = SQLAllocHandle(SQL_HANDLE_DBC, envh, &dbch);
if (!SQL_SUCCEEDED(fsts))
{
  /* an error occurred allocating the database handle */
}
else
{
  /* Database handle allocated OK */
}

The SQL_SUCCEEDED macro is defined as:

#define SQL_SUCCEEDED(rc) (((rc)&(~1))==0)

Virtually all ODBC functions can return two values that indicate success:

Both of these return values cause the SQL_SUCCEEDED macro to result in 1.

If a function returns SQL_SUCCESS_WITH_INFO, it means that the call succeeded but an informational message was produced. For example, with some ODBC drivers you might set the cursor type, prepare a statement, and then execute it. When you call SQLExecute, the statement is acted upon, but the ODBC driver might change the cursor type to something else. In this case, SQLExecute would return SQL_SUCCESS_WITH_INFO and the ODBC driver would add a diagnostic indicating the cursor type had been changed.

You should note that a few ODBC functions return a status that fails the SQL_SUCCEEDED macro, but does not indicate an error as such. For example, SQLFetch can return SQL_NO_DATA indicating that there are no further rows in the result set. This is not necessarily an error.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Obtaining diagnostics

When an ODBC function returns an error or SQL_SUCCESS_WITH_INFO, the ODBC driver associates a diagnostic with the handle used in the ODBC call. You can obtain the diagnostic to find out what failed by calling SQLGetDiagRec with the handle you used in the ODBC call that failed.

The ODBC driver may associate multiple diagnostic records with a handle.

You can call SQLGetDiagField and request the SQL_DIAG_NUMBER attribute to find out how many diagnostics exist. Alternatively, as diagnostic records start at 1, you can repeatedly call SQLGetDiagRec asking for record 1, then 2 (and so on) until SQLGetDiagRec returns SQL_NO_DATA.

As an example, the following C function takes a function name string, handle type and handle and retrieves all the diagnostics associated with that handle.

void extract_error(
      char *fn,
      SQLHANDLE handle,
      SQLSMALLINT type)
  {
    SQLINTEGER i = 0;
    SQLINTEGER native;
    SQLCHAR state[ 7 ];
    SQLCHAR text[256];
    SQLSMALLINT len;
    SQLRETURN ret;
    fprintf(stderr,
            "\n"
            "The ODBC driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do
    {
      ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
      sizeof(text), &len );
      if (SQL_SUCCEEDED(ret))
      printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret == SQL_SUCCESS );
  }

Using the example above, which attempts to allocate a database handle, you could use extract_error as follows:

SQLRETURN fsts;
  /* Assume for this example the environment has already been allocated */
  SQLHENV envh;
  SQLHDBC dbch;
  fsts = SQLAllocHandle(SQL_HANDLE_DBC, envh, &dbch);
  if (!SQL_SUCCEEDED(fsts))
  {
    extract_error("SQLAllocHandle for dbc", envh, SQL_HANDLE_ENV);
    exit(1);
  }
  else
  {
    /* Database handle allocated OK */
  }

ODBC 2.0 applications use SQLError instead of SQLGetDiagRec.

Diagnostic fields

When you call SQLGetDiagRec you can retrieve three diagnostic fields:

The state is a five character SQLSTATE code. The first two characters indicate the class and the next three indicate the subclass. SQLSTATEs provide detailed information about the cause of a warning or error. You can look states up in Appendix A.

The native error code is a code specific to the data source. This number is often extremely useful to ODBC driver developers in locating an internal error or state. If you're reporting a bug in an ODBC driver for which you obtained an error you should always quote the ODBC function called, the error text, and this native number.

The message text is the text of the diagnostic. This string takes one of two forms:

For errors and warnings that do not occur in a data source the format:

[vendor-identifier][ODBC-component-identifier]component-supplied-text

otherwise:

[vendor-identifier][ODBC-component-identifier][data-source-identifer]
      data-source-supplied-text

Example diagnostic messages

You can use the message text string to identify which piece of software reported the error. For example, here are some message texts and error conditions generated using the Easysoft ODBC-ODBC Bridge with Microsoft SQL Server:

[Easysoft ODBC (Client)]Invalid authorization specification

This error was produced when the connection attempt was refused because the LogonUser and LogonAuth attributes were invalid. The ODBC-ODBC Bridge alone was involved in this process.

[Easysoft ODBC (Server)][Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified.

This error was produced by the Microsoft ODBC Driver Manager on the ODBC-ODBC Bridge server machine, because the TargetDSN attribute specified an ODBC data source that didn't exist. The last item in square brackets was ODBC Driver Manager and therefore that's the component that generated the error text. As the text is prefixed with [Easysoft ODBC (Server)], you know that it was the ODBC Driver Manager on the server machine.

[Easysoft ODBC (Server)][Microsoft][ODBC SQL Server Driver][SQL Server]
      Login failed for user 'demo'.

This error was produced when the TargetUser and TargetAuth specified with the ODBC-ODBC Bridge client was passed through to the DBMS, which refused the connection. The last item in square brackets was SQL Server and so you know that SQL Server turned down the connection attempt.

Appendix A: ODBC status return codes

This appendix lists all ODBC status return codes.

The list includes error messages and the functions that can return the status code complete with a brief description.

Appendix B: ODBC 2.x to ODBC 3.x SQLSTATE mappings

This appendix lists ODBC 2.x to ODBC 3.x SQLSTATE mappings.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.