ODBCTracer

• Download demo project - 44 Kb

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.

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.

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.

  

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

  

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!

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