Easysoft ODBC-ODBC Bridge User's Guide - Technical Reference

Additional information for the Easysoft ODBC-ODBC Bridge

This section contains extra information relating to the deployment of the Easysoft ODBC-ODBC Bridge (OOB).

It documents where the Easysoft ODBC-ODBC Bridge API differs from other ODBC APIs, diagnostic functionality and tracing issues.

Appendix Guide

ODBC versions supported

The Easysoft ODBC-ODBC Bridge supports most of ODBC 2.0 and ODBC 2.5, and all of ODBC 3.0 and 3.5 with the exception of SQL_IS_POINTER (see Unsupported ODBC 3.5 functionality).

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

Unsupported ODBC 3.5 functionality

SQL_IS_POINTER

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

Modifications to the API

SQLBrowseConnect()

The semantics of SQLBrowseConnect() are slightly difficult in the context of the OOB. 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 which would make it impossible to return a final connection string which allows a later connection without browsing.

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 is displayed when a user connects via the Easysoft ODBC-ODBC Bridge, the error does not necessarily lie in the Easysoft ODBC-ODBC Bridge component of your configuration and 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 when the LogonUser/LogonAuth attributes were invalid and the connection attempt has been 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 OOB Server machine when the TargetDSN attribute specified a DSN which does not exist on the server. You can see that the last item in square brackets was the "ODBC Driver Manager" and hence it is that component which 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/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 SQLServer 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.

The next section explains how to implement ODBC diagnostic messages.

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

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:

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 which 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 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 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.0 applications will 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. SQLSTATE codes provide detailed information about the cause of a warning or error.


REF

For the definitive SQL CLI document consult the Open Group CAE Specification C451, ISBN 1-85912-081-4.

The Microsoft ODBC 3.0 Programmer's Reference, ISBN 1-57231-516-4explains ODBC usage in some detail.


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 Easysoft ODBC-ODBC Bridge 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:

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

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

See Understanding ODBC diagnostic messages to review some example messages.

Tracing

There are three ways to trace the ODBC calls an application makes through the driver manager and the OOB Client ODBC driver:

1.  tracing in the driver manager may be turned on.

2.  tracing in the OOB Client ODBC driver may be turned on.

3.  an application can turn tracing on via the ODBC API SQLSetConnectAttr (...,SQL_ATTR_TRACE,...). The trace filename may also be specified with the SQLSetConnectAttr attribute SQL_ATTR_TRACEFILE.

Starting tracing in the driver manager is platform-specific:

[1a] Windows:

Start the ODBC driver manager administration interface via Start Menu > Control Panel > ODBC Data Sources.

Click on Tracing, ensure the specified filename is valid and click Start Tracing Now.

[1b] Unix:

If you are using the unixODBC driver manager then tracing is enabled in the odbcinst.ini file (usually /etc/odbcinst.ini).

To enable tracing you must add two attributes to the [ODBC] section (if you do not have an [ODBC] section, create one):

Trace = Yes

TraceFile = /path/filename

e.g.

[ODBC]

Trace = Yes

TraceFile = /tmp/sql.log

Make sure that the user who is running the application to be traced has write permission to TraceFile (and to the directory containing it), or you will not get any tracing at all.

Driver manager trace files show all the ODBC calls applications make, their arguments and return values, but OOB Client ODBC driver tracing is specific to the Easysoft ODBC-ODBC Bridge and is of most use when making a support call.

You can enable OOB Client ODBC tracing by method [3] or by a platform-specific method:

[2a] Windows

Update the Registry by running regedit and edit the key:

HKEY_LOCAL_MACHINE\SOFTWARE\EASYSOFT ODBC-ODBC BRIDGE\CONFIGURATION\SYSTEM\SETTINGS

Edit the Logging string value and set it to 0xffffff, which turns on all tracing (see Easysoft ODBC-ODBC Bridge FAQ for tracing bitmask values).

A trace file named esoobclient.log is written to the LogDir directory (which is defined in the same registry key).

[2b] Unix:

OOB Client ODBC driver tracing can be enabled by adding a {Settings} section to the end of your odbc.ini file.

e.g.

{Settings}

Logging = 0xffffff

The Logging value is a bitmask and the values are listed in the Easysoft ODBC-ODBC Bridge FAQ.

A trace file named esoobclient_PID.log is created in LogDir, where PID is the process ID.


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.