ODBC Diagnostics
================

Status Returns
==============

All ODBC APIs return a status value which may be used to check whether
the function succeeded or not.

In C you can test the return value from an ODBC function using the
macro SQL_SUCCEEDED e.g.

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 macro SQL_SUCCEEDED is defined as:

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

Virtually all ODBC functions can return two values which indicate success

o SQL_SUCCESS
o SQL_SUCCESS_WITH_INFO

Both of these returns 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.

e.g. with some drivers you might set the cursor type, prepare a
statement and then execute it. When SQLExecute is called the statement
is acted upon but the driver might change the cursor type to something
else.  In this case, SQLExecute would return SQL_SUCCESS_WITH_INFO and
the driver would add a diagnostic indicating the cursor type had been
changed.

You should note that a few ODBC functions return a status which fails
the SQL_SUCCEEDED macro but do not indicate an error as such.  e.g.
SQLFetch can return SQL_NO_DATA indicating there is no further rows in
the result set, this is not necessarily an error.

Obtaining Diagnostics
=====================

When an ODBC function returns an error or SQL_SUCCESS_WITH_INFO then
the driver will associate 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 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 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 will use SQLError instead of SQLGetDiagRec.

Diagnostic Fields
=================

When you call SQLGetDiagRec you can retrieve 3 diagnostic fields:

o State
o Native error code
o Message text

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 the ODBC specification.

The native error code is a code specific to the data source. This
number is often extremely useful to the driver developers in locating
an internal error or state. If you are reporting a bug in the OOB 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
is:
[vendor-identifier][ODBC-component-identifier]component-supplied-text

otherwise it is:

[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:

[Easysoft ODBC (Client)]Invalid authorization specification

This error was produced when the LogonUser/LogonAuth attributes were
invalid and the connection attempt has been refused. The OOB 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
OOB Server machine when the TargetDSN attribute specified a DSN which
does not exist on the server. You can see that the last item is square
brackets was the "ODBC Driver manager" and hence that is the component
which generated the error text. As the text is prefixed with
"[Easysoft ODBC (Server)]" you know that it was the driver manager
at the server end.

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

This error was produced when the TargetUser/TargetAuth specified at the
OOB client was passed through 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.
