Click here to Skip to main content
13,006,530 members (62,488 online)
Click here to Skip to main content
Add your own
alternative version


51 bookmarked
Posted 1 Aug 2005


, 4 Aug 2005
Rate this:
Please Sign up or sign in to vote.
Using Microsoft's tracing infrastructure for ODBC.


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.

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.


    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.


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.

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.

    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


About the Author

Doga Arinir
Chief Technology Officer W3L
Germany Germany
-Since 1th August 2007: Chief Technology Officer of W3L
-2002/08/01-2007/07/31: PhD student
-1997/10/15-2002/07/31: Studied Electrical Engineering and Computer Science

You may also be interested in...

Comments and Discussions

QuestionOdbc tracing or is it something more? Pin
Member 433764112-Apr-13 3:07
memberMember 433764112-Apr-13 3:07 
QuestionPatch for Visual Studio 2008 that supports 32 and 64 bit builds Pin
zippy19814-Sep-12 17:07
memberzippy19814-Sep-12 17:07 
AnswerRe: Patch for Visual Studio 2008 that supports 32 and 64 bit builds Pin
zippy19816-Sep-12 13:15
memberzippy19816-Sep-12 13:15 
Generalusername and password Pin
fiddyschmitt4-May-09 15:16
memberfiddyschmitt4-May-09 15:16 
QuestionProblems with Vista AND XP sp3 Pin
tfryar26-Dec-08 5:48
membertfryar26-Dec-08 5:48 
AnswerRe: Problems with Vista AND XP sp3 Pin
igvk15-Feb-10 10:20
memberigvk15-Feb-10 10:20 
GeneralszSqlStr SQLWCHAR* is very small in SQLExecDirectW [modified] Pin
bmp-ra13-Apr-08 7:07
memberbmp-ra13-Apr-08 7:07 
GeneralRe: szSqlStr SQLWCHAR* is very small in SQLExecDirectW Pin
bmp-ra18-Apr-08 6:44
memberbmp-ra18-Apr-08 6:44 
Questionhow can i debug it on microsoft odbc? Pin
toxyboy22-Nov-07 0:16
membertoxyboy22-Nov-07 0:16 
QuestionBinary version of ODBCTracer.dll? Pin
Daniel Stern27-Sep-07 0:33
memberDaniel Stern27-Sep-07 0:33 
Generalnot an ODBC tracing dll or it is obsolete Pin
plancake23-May-07 19:54
memberplancake23-May-07 19:54 
GeneralRe: not an ODBC tracing dll or it is obsolete Pin
normanl9-Oct-07 5:31
membernormanl9-Oct-07 5:31 
GeneralRe: not an ODBC tracing dll or it is obsolete Pin
zippy19814-Sep-12 17:04
memberzippy19814-Sep-12 17:04 
GeneralODBC Tracer Pin
seanroger5-Apr-07 3:20
memberseanroger5-Apr-07 3:20 
Questioncustomizing Pin
uday22-Feb-07 9:45
memberuday22-Feb-07 9:45 
QuestionWriting own ODBC win driver Pin
rajendra.badam8-Jan-07 21:50
memberrajendra.badam8-Jan-07 21:50 
AnswerRe: Writing own ODBC win driver Pin
Arinir10-Jan-07 23:51
memberArinir10-Jan-07 23:51 
GeneralRe: Writing own ODBC win driver Pin
rajendra.badam11-Jan-07 18:47
memberrajendra.badam11-Jan-07 18:47 
Generalproblem with SQLSetEnvAttr Pin
fbachan14-Jul-06 5:27
memberfbachan14-Jul-06 5:27 
GeneralSQL Queries Pin
ChrisC10011-Jan-06 6:41
memberChrisC10011-Jan-06 6:41 
GeneralRe: SQL Queries Pin
Arinir11-Jan-06 21:25
memberArinir11-Jan-06 21:25 
GeneralRe: SQL Queries Pin
ChrisRotherham12-Jan-06 7:26
memberChrisRotherham12-Jan-06 7:26 
GeneralRe: SQL Queries Pin
ChrisRotherham13-Jan-06 7:22
memberChrisRotherham13-Jan-06 7:22 
GeneralCompleting Implementation and ESP register not being restored. Pin
Mario Pintaric13-Aug-05 12:08
memberMario Pintaric13-Aug-05 12:08 
GeneralRe: Completing Implementation and ESP register not being restored. Pin
Arinir14-Aug-05 22:38
memberArinir14-Aug-05 22:38 
GeneralRe: Completing Implementation and ESP register not being restored. Pin
Mario Pintaric15-Aug-05 7:17
memberMario Pintaric15-Aug-05 7:17 
QuestionWhat have I missed? Pin
macronencer11-Aug-05 1:11
sussmacronencer11-Aug-05 1:11 
AnswerRe: What have I missed? Pin
Arinir12-Aug-05 9:04
memberArinir12-Aug-05 9:04 
GeneralNT4 Pin
Thierry Parent5-Aug-05 1:09
memberThierry Parent5-Aug-05 1:09 
GeneralRe: NT4 Pin
Arinir5-Aug-05 2:14
memberArinir5-Aug-05 2:14 
GeneralA simple sample of using this great tool would be perfect. Pin
li00004-Aug-05 10:35
memberli00004-Aug-05 10:35 
GeneralRe: A simple sample of using this great tool would be perfect. Pin
Arinir4-Aug-05 12:15
memberArinir4-Aug-05 12:15 
QuestionCan it be used with Microsoft ODBC? Pin
li00004-Aug-05 10:31
memberli00004-Aug-05 10:31 
AnswerRe: Can it be used with Microsoft ODBC? Pin
Arinir4-Aug-05 12:12
memberArinir4-Aug-05 12:12 
QuestionWhat did you update? Pin
WREY4-Aug-05 6:20
memberWREY4-Aug-05 6:20 
AnswerRe: What did you update? Pin
Arinir4-Aug-05 11:54
memberArinir4-Aug-05 11:54 
GeneralI am looking forward to your second article on the codeproject. Pin
li00004-Aug-05 13:53
memberli00004-Aug-05 13:53 
BobbyQSoft3-Aug-05 5:07
memberBobbyQSoft3-Aug-05 5:07 
Arinir3-Aug-05 5:57
memberArinir3-Aug-05 5:57 
GeneralGreat work Pin
Mikael Segerstein3-Aug-05 2:56
memberMikael Segerstein3-Aug-05 2:56 
GeneralRe: Great work Pin
Arinir3-Aug-05 3:21
memberArinir3-Aug-05 3:21 
GeneralRe: Great work Pin
Mikael Segerstein3-Aug-05 4:28
memberMikael Segerstein3-Aug-05 4:28 
GeneralRe: Great work Pin
Arinir3-Aug-05 4:38
memberArinir3-Aug-05 4:38 
GeneralRe: Great work Pin
Mikael Segerstein3-Aug-05 4:43
memberMikael Segerstein3-Aug-05 4:43 
QuestionI am really interested in your other ODBC software, could you publish them? Pin
Anonymous2-Aug-05 19:00
sussAnonymous2-Aug-05 19:00 
AnswerRe: I am really interested in your other ODBC software, could you publish them? Pin
Arinir2-Aug-05 21:26
memberArinir2-Aug-05 21:26 
GeneralCompiling error Pin
Anonymous2-Aug-05 17:54
sussAnonymous2-Aug-05 17:54 
GeneralRe: Compiling error Pin
Arinir2-Aug-05 22:16
memberArinir2-Aug-05 22:16 
GeneralVery Cool... Pin
Matt Davison2-Aug-05 17:24
memberMatt Davison2-Aug-05 17:24 
GeneralRe: Very Cool... Pin
Arinir2-Aug-05 21:27
memberArinir2-Aug-05 21:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170628.1 | Last Updated 4 Aug 2005
Article Copyright 2005 by Doga Arinir
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid