Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

ODBCTracer

4.90/5 (27 votes)
4 Aug 20056 min read 1   2K  
Using Microsoft's tracing infrastructure for ODBC.

Image 1

Introduction

Microsoft's ODBC architectures enable application developers to write robust and flexible database software by splitting the client code from database specific implementation details. Instead of directly connecting to a unique database via the database vendor's own call interface, the application developer uses the ODBC-API to define to which database he wants a connection to and through which driver this connection can be established.

Image 2

The ODBC drivers are generally provided by the database vendors and encapsulate all of the details needed for setting up the connection and to communicate with the database. Although, applications could use these drivers directly, there are many disadvantages why they should not.

  • ODBC driver creation

    If applications use drivers directly, they need to know where these drivers are located and how they are instantiated. Any changes on this creation process (e.g. through newer ODBC versions) involve complete adjustment of older applications.

  • ODBC conformance levels

    Currently, there are three major ODBC conformance levels. Drivers and applications of different conformance levels can work together, if the different dialect is translated somehow.

  • CHAR or WCHAR

    Like the conformance levels, applications and drivers can differ in the way they define strings, so there is also some kind of translation needed.

  • Trace information

    Finally, there are some cases where it is useful to get detailed information about application to driver communication. It is very important to be able to trace down any ODBC API invocation.

Therefore, the ODBC architecture is based on a delegate model where invocations from ODBC applications are delegated to the specific drivers. This delegation is accomplished through the ODBC driver manager which is also handling translation work and tracing. The driver manager does not trace the invocations itself, but delegates them to a tracing module called odbctrac.dll (the name of this module is subject to change and may differ from system to system). By changing this module, you are able to hook in the ODBC's tracing mechanism.

Backgrounds

The ODBC-API is a functional interface. Its functions can be divided into four different categories.

  • ODBC core functions.
  • ODBC functions for setting up the driver and configuring data sources. These functions are implemented by the ODBC driver manager.
  • ODBC installer functions containing the driver specific part of the installation of drivers or data sources.
  • ODBC translation functions for translating data flowing from the data source to the driver and vice versa. Please don't confound these functions with those I've mentioned above for translation between the different types of ODBC conformance levels.

ODBC driver developers have to implement a subset of all those ODBC functions defined in the ODBC-API. Which functions are implemented depend on the data source and the conformance level the driver developer wants to achieve with his driver. A complete introduction to this subject is out of the scope of this article. But it is important to know, that the ODBC drivers export these functions so that the driver manager is able to bind and invoke them in times they are needed. Trace modules have to export one function for every ODBC function they want to trace invocations of. It must have the same name as the ODBC function with "Trace" as a prefix.

Image 3

Tracing is done between each ODBC API call. For example, if the application wants to connect to a data source, it first has to allocate an environment. The application calls the appropriate function SQLAllocEnv() on the ODBC driver manager which in turn calls TraceSQLAllocEnv() on the trace module if tracing is activated. The trace function TraceSQLAllocEnv() has the same signature as the ODBC function SQLAllocEnv() implemented by the ODBC driver and takes the same parameters. In TraceSQLAllocEnv(), the trace module can dump information about the function call. After tracing is finished, the ODBC driver manager delegates the call to the driver. In the case of environment allocation, the driver has to allocate internal data structures needed for further processes. Finally, once the main invocation has returned, the driver manager calls an after-call trace function called TraceReturn. In TraceReturn(), the trace module can dump the parameter values after the function call. The problem is that TraceReturn does not have the same function signature as the original trace function TraceSQLAllocEnv() and does not get the parameters for dump. At first glance, this is a little bit confusing, but very useful at the end.

Imagine, each ODBC function would have its own TraceReturn function to trace dump call information after ODBC API function invocation (for instance, TraceSQLAllocEnvReturn() for TraceSQLAllocEnv()). This would mean that you would double the amount of functions the trace module would have to export for tracing. To avoid this code blow, the tracing mechanism of ODBC works with handles. Each TraceXYZ function returns a handle to a call data structure. You are free to use whatever structure you want. Let's say, you want to dump exactly the same information after the ODBC function call, then you must save all arguments of the given ODBC function for later use. I have defined my own trace call data structure where I can save information about arguments, like their names and types.

RETCODE SQL_API TraceSQLAllocEnv(SQLHENV FAR * phenv)
{
    ODBCTraceCall *call = new ODBCTraceCall();

    call->insertArgument("phenv", TYP_SQLHENV_PTR, phenv);

    call->function_name = "SQLAllocEnv";
    call->function_id = SQL_API_SQLALLOCENV;

    ODBCTrace(call, true);
    return (RETCODE)stack.push(call);
}
VOID SQL_API TraceReturn(RETCODE rethandle,RETCODE retcode)
// Processes trace after FN is called
{
    ODBCTraceCall *call = stack.pop(rethandle);
    if (call != NULL)
    {
        call->retcode = retcode;
        ODBCTrace(call, false);
        delete call;
    }
}

Using the ODBCTracer

This tool materialized some weeks ago when I worked on a project where we had to write our own ODBC driver for some kind of data source. We had problems with some applications and didn't know why our driver did not work properly in some cases. We had to trace the ODBC function calls between the application and different ODBC drivers to understand what was wrong, but the normal ODBC tracer was not able to turn on or off tracing for different ODBC functions and it was a messy thing to scrutinize the log files generated by the odbctrac.dll. This tool has two advantages if you compare it with the stock ODBC tracer.

  • It first provides tracing turned on and off for every ODBC function it supports.

    Image 4

  • It also has its own system tray icon which appears once the tracer has been loaded and one of the TraceXYZ functions have been called. Besides logging the trace information into a file, the ODBC tracer also dumps them into the tracer's dialog, so you don't have to reopen the file to see the updated ODBC function calls.

    Image 5

Finally, you can turn file logging completely off if you are not interested.

Installing the ODBC-Tracer

If you want to use the ODBC-Tracer, you have to copy the module into your system32 directory due to Windows security policies. Then you have to open the ODBC administrator to select the ODBCTracer.dll as your custom trace module. Once you activate tracing, any ODBC function call is traced (sorry about the German screenshot of the ODBC administrator sheet :). Enjoy!

Image 6

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here