About the Easysoft ODBC-ODBC Bridge

The Easysoft ODBC-ODBC Bridge lets applications that support ODBC connect to a datastore that’s on a different platform or has a different architecture. For example, an application on Linux needs to connect to a database for which there’s only a Windows ODBC driver; a 64-bit application needs to connect to a database for which there’s only a 32-bit ODBC driver.

ODBC versions supported

The Easysoft ODBC-ODBC Bridge supports most of ODBC 2.0 and ODBC 2.5, and all of ODBC 3.x apart from SQL_IS_POINTER.

All the ODBC 2.0 and 2.5 API functions required to run Perl DBD:ODBC, PHP, and Python mxODBC are present, but the full API of these deprecated versions is not supported, as new applications will be written using the ODBC 3.x API.

Unsupported ODBC 3.5 functionality

The use of driver-specific pointer types (SQL_IS_POINTER) is not supported in calls to:

Modifications to the ODBC API

SQLBrowseConnect

The semantics of SQLBrowseConnect are slightly difficult in the context of the Easysoft ODBC-ODBC Bridge. Normally, SQLBrowseConnect provides an iterative method of discovering and enumerating the attributes and attribute values required to connect to a data source. Each call to SQLBrowseConnect returns successive levels of attributes and attribute values. When all levels have been enumerated a connection to the data source is completed and a complete connection string is returned by SQLBrowseConnect.

This process works fine when SQLBrowseConnect is called for a driver on the local machine, but when you introduce a bridge there are in effect two levels of browsing. The first level will browse the local data sources (defined in your odbc.ini file or in the registry), but these point to another real data source on a remote machine.

SQLBrowseConnect only supports the browsing of Easysoft ODBC-ODBC Bridge data sources on the local machine. If the user tries to connect to one of these then they will be prompted for Easysoft ODBC-ODBC Bridge attributes such as SERVERPORT, LOGINUSER, LOGINAUTH, TARGETUSER, and TARGETAUTH.

Once sufficient attributes have been defined to allow a bridge connection to the server, the server side of the Easysoft ODBC-ODBC Bridge will return a list of DSNs retrieved by calling SQLDataSources. The browse stops here and so the remote data source must already be set up with sufficient information to allow a connection.

This implementation avoids complications with possible clashes of attributes between the Easysoft ODBC-ODBC Bridge and the remote ODBC driver that would make it impossible to return a final connection string.

Understanding ODBC diagnostic messages

The Easysoft ODBC-ODBC Bridge works in conjunction with a number of other types of software, such as ODBC applications, driver managers, drivers, and DBMSs.

If a diagnostic (or error) message displays when a user connects by using the Easysoft ODBC-ODBC Bridge, the error does not necessarily lie in the Easysoft ODBC-ODBC Bridge. The message should usually indicate where the problem lies.

For example, examine the following diagnostic messages:

[Easysoft ODBC (Client)]Invalid authorization specification

This error was produced because the LogonUser and LogonAuth attributes were invalid and the connection attempt was refused. Only the Easysoft ODBC-ODBC Bridge 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 Easysoft ODBC-ODBC Bridge server machine because the TargetDSN attribute specified a DSN that did not exist. The last item in square brackets was the ODBC Driver Manager and therefore is the component that generated the error text.

The text is also prefixed with [Easysoft ODBC (Server)], which means that the error occured with 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 and TargetAuth specified by the Easysoft ODBC-ODBC Bridge client was passed through the DBMS, which refused the connection.

The last item in square brackets is SQL Server and so you know that SQL Server turned down the connection attempt.

Therefore, if you encounter any error messages when using the Easysoft ODBC-ODBC Bridge, please note the last item specified in square brackets, because that is the component that generated the error text and the error may not lie in the Easysoft ODBC-ODBC Bridge.

If you are integrating the Easysoft ODBC-ODBC Bridge with your own ODBC application, it is worthwhile implementing diagnostics in your application because then your error messages will indicate in which component the problem lies.

Implementing ODBC diagnostics

This section explains how to implement ODBC diagnostics in your own ODBC application, so that the application can display diagnostic messages if an error occurs at any point in the connection process.

Status returns

All ODBC APIs return a status value that 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:

SQLRETURN fsts;

/* Assume 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:

  • SQL_SUCCESS

  • 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.

For example, 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 that fails the SQL_SUCCEEDED macro, but do not necessarily indicate an error. For example, 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, the ODBC 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 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 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 this example, which attempts to allocate a database handle, you could use extract_error as follows:

SQLRETURN fsts;

/* Assume 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.x applications use SQLError instead of SQLGetDiagRec.

Diagnostic fields

When you call SQLGetDiagRec, you can retrieve three diagnostic fields:

  • State

  • Native error code

  • Message text

The state is a five character SQLSTATE code. The first two characters indicate the class and the next three indicate the subclass. SQLSTATE codes provide detailed information about the cause of a warning or error.

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.

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]component-supplied-text

otherwise it’s:

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