Using ODBC Tracing

Introduction to ODBC Tracing

What does tracing do?
ODBC tracing provides a way to log ODBC calls between an application, the ODBC driver
manager and the ODBC driver. The ODBC calls are written to a text file which can then be
examined for details on the behavior of the ODBC application and driver. This is helpful when
debugging error messages and other types of problems to determine their cause.

How to enable and disable tracing on Windows, UNIX and Linux

On Windows, ODBC tracing is enabled through the ODBC Administrator. To enable tracing:
Open the ODBC Administrator (in the Control Panel, look for the Data Sources (ODBC)
icon)
Click the tab where your data source is listed (either System DSN or User DSN) and then
click the Tracing tab
Enter a location and name for the output file in the Log file Path field, such as
C:\temp\my_trace.log
Click the button reading Start Tracing Now. After you click it, it will then read Stop
Tracing Now.
Click OK to close the ODBC Administrator

Tracing is now enabled. Perform the steps you would like to trace (i.e. Open your
application, repeat the steps until the error or problem occurs)
Once you have reproduced the behavior you were tracing, ODBC tracing should be
turned off
Re-open the ODBC Administrator (in the Control Panel, look for the Data Sources
(ODBC) icon)
Click the tab where your data source is listed (either System DSN or User DSN) and then
click Tracing
Click the Stop Tracing Now button, and click OK to close the ODBC Administrator
Now open the log (e.g. C:\temp\my_trace.log) with a text viewer such as Notepad

On Unix and Linux, tracing is enabled through the odbc.ini file or through the Linux ODBC
Administrator tool that was added in the Connect for ODBC and Connect64 for ODBC 5.3
release.
To enable tracing in the odbc.ini file:
Using a text editor, open the odbc.ini text file where your data source is configured
(e.g. /opt/odbc/odbc.ini)
The bottom of the odbc.ini file contains a section headed with [ODBC] such as:
[ODBC]
IANAAppCodePage=4
InstallDir=ODBCHOME
Trace=0
TraceDll=ODBCHOME/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0

 Enter a location and name for the output file where the ODBC trace file will be created
TraceFile=/home/my_dir/odbctrace.out
To turn tracing on, set
Trace=1

Save and close the file. Tracing is now enabled. Perform the steps you would like to trace
(i.e. Open your application, repeat the steps until the error or problem occurs)
Once you have reproduced the behavior you were tracing, ODBC tracing should be
turned off. To do so, re-open the odbc.ini file and set
Trace=0

Now open the log (e.g. /home/my_dir/odbctrace.out) with a text viewer

To enable tracing with the Linux ODBC Administrator tool available in the 5.3 Connect and
Connect64 release:
Start the Linux ODBC Administrator in the install_dir/tools directory with the command
odbcadmin
Click on the Trace tab.

Select the Enable Tracing checkbox.

Specify the path and the name of the trace log file in the Trace File field or click Browse
to select a file. If no location is specified, the trace log resides in the working directory of
the application you are running.
Click Apply.
Click OK when you are finished with the Linux ODBC Administrator and the tracing tab
changes are accepted.
Clear the Enable Tracing checkbox to disable tracing.

NOTE: The Linux ODBC Administrator was not available on the Linux Itanium platform at release
time; it is available on Linux x64 and Linux x86.

Why disable tracing?

Tracing is a very useful tool, so why disable it instead of leaving tracing turned on all the time? It
might seem like leaving tracing enabled would be a great idea- youd have a running log of every
ODBC call made on your machine. However, there is a powerful downside that outweighs this
benefit. Because tracing writes every ODBC call to a text file, its very resource intensive. That
means the performance of your ODBC application is seriously impacted by tracing. Leaving
tracing enabled will noticeably slow your application performance and use CPU resources on
your machine. In addition, the size of the output file will rapidly grow to the point that it requires
many megabytes of storage space.
For these reasons, tracing should only be enabled when a specific problem or set of steps is
being traced. At all other times, tracing should be disabled.

When will an ODBC trace be helpful?

Tracing is a way to debug issues when you cant determine the source of a problem or error. It
can be used to narrow down a problem to its origination pointthe driver, the application, the
database, even the network. For example, tracing can contribute in the debugging of error
messages, data corruption, and performance problems.
With error messages, the format of the message points to its source. Error messages usually
come from:

An ODBC driver
The database system
The ODBC driver manager

The last component listed in the error string is typically the source of the error message. An error
message originating from a database would look like this:
[vendor] [ODBC_driver] [database] message
For example,
[DataDirect] [ODBC Oracle driver] [Oracle] ORA-0919: specified length
too long for CHAR column
An error originating from the ODBC driver would look like:
[vendor] [ODBC_component] message
For example,
[DataDirect] [ODBC Oracle driver] Invalid precision specified.
An error originating from the ODBC driver manager would look like:
[vendor] [ODBC XXX] message
For example,
[Microsoft] [ODBC Driver Manager] Driver does not support this function
Knowing the origination of the error message may help you determine where to start the
debugging process, because the action you take in debugging an error is dependent upon the
error message. The DataDirect SupportLink Knowledgebase, available at
http://support.datadirect.com, is a great resource for researching error messages. Database
vendor web sites are another helpful resource.
With data corruption and performance issues, it is equally true that the problem could be in the
application, ODBC driver, or database. Performance slow-downs are also often caused by
network issues. The first step with these types of problems, as with all debugging, is to determine
the source of the problem.
See more information below on using ODBC trace to debug under How to read an ODBC trace
log.
Another source of useful information found in ODBC trace logs is SQLSTATEs. The Microsoft
ODBC API specifies that SQLSTATEs provide detailed information about the cause of a warning
or error. SQLSTATEs are guidelines and while drivers are not required to return them, many do
return them for the error messages and warnings they are capable of detecting. While
applications should not count on SQLSTATEs being returned, they are useful when available.
Most applications display a returning SQLSTATE along with the error message. A complete list of
SQLSTATEs and what functions return them is available in the ODBC Programmers Reference
on the MSDN site.
Finally, many databases return native error codes when possible. For example, the Oracle
database returns a series of error codes beginning with the letters ORA or PLS, among others.
The ODBC Programmers Reference does not document database-specific error codes. Rather,
the database vendor documentation or web site would be more likely to contain information on
these.

How to Read an ODBC Trace Log

An ODBC trace log will show the ODBC function calls an application makes to the ODBC Driver
and driver manager. Knowledge of the ODBC API Specification will help to decipher the trace log
generated. Having a copy of the ODBC Specification available will help as well.
Each ODBC function accepts a set of parameters. Here is an example of how an ODBC function
is seen in the trace log
Process_Name

XXX-YYY
ENTER ODBC_Function
DataType
value_of_argument_1
DataType
value_of_argument_2
DataType
value_of_argument_3
.
.
.

Process_Name
N (RetCode)

XXX-YYY
DataType
DataType
DataType
.
.
.

EXIT

ODBC_Function with return code

value_of_argument_1
value_of_argument_2
value_of_argument_3

The name of the process being traced

The current process id
The current thread id
Enter or exit the ODBC function
The ODBC API function being executed by the application
A return code provided by the ODBC driver which determines the
status of the function executed
DataType
The data type of the ODBC functions argument
Value_of_argument The arguments data which is passed or returned during the
ODBC functions execution
Process_Name
XXX
YYY
ENTER : EXIT
ODBC_Function
RetCode

Here is an example of executing the function SQLExecDirect using an ODBC application called
ODBC Test.
OdbcTE32

870-4e8
HSTMT
UCHAR *
SDWORD

OdbcTE32
870-4e8
(SQL_SUCCESS)
HSTMT
UCHAR *
SDWORD

ENTER SQLExecDirect
00982470
0x0015B370 [
-3] "select * from emp\ 0"
-3
EXIT

SQLExecDirect

00982470
0x0015B370 [
-3

with return code 0

-3] "select * from emp\ 0"

According to the ODBC Specification, the SQLExecDirect function takes 3 arguments:

RETCODE

SQLExecDirect(hstmt, szSqlStr, cbSqlStr)

Type
HSTMT
UCHAR FAR *
SDWORD

Argument
hstmt
szSqlStr
cbSqlStr

Use
Input
Input
Input

Description
Statement handle
SQL statement to be executed
Length of szSqlStr

Compare the arguments above to the trace log to see what information is passed to the
SQLExecDirect function.
hstmt
szSqlStr
cbSqlStr

00982470
select * from emp\ 0 where \ 0 is the null terminator
-3 or SQL_NTS means the value is a null-terminated string

Error messages
Whenever an error message is returned by the ODBC driver, it will be displayed with the format
[Vendor][ODBC Component]Error message
or
[Vendor][ODBC Component][Data source]Error message
The [] immediately to the left of the error message informs the user where the error message was
generated. The error message will either be returned by one of the ODBC components, i.e. the
ODBC driver or driver manager, or by the data store, i.e. Oracle, Sybase, DB2, etc.
The [Vendor] is the distributor of the ODBC component referenced in the error message.
When trying to determine which ODBC function failed, search the trace log for the return code
SQL_ERROR or the error message returned by the application. If the error message or
SQL_ERROR cannot be found, search for the SQL statement or ODBC function being executed
at the time the problem exists in the application. Use that marker as a beginning point to start
traversing backward through the trace log. Its possible that, if the error isnt found in the trace
log, the results from executing a function caused the application to error. Application error
messages will not appear in the ODBC trace log.
Once the error message is found, verify the arguments passed for each ODBC function are
correct between where the statement was executed and where the error appeared. You may
need to look in the ODBC specification to confirm the correctness of the argument values.
Example 1
OdbcTE32

4c8-d3c
HSTMT
UCHAR *
SDWORD

OdbcTE32
4c8-d3c
(SQL_ERROR)
HSTMT
UCHAR *
SDWORD

ENTER SQLExecDirect
00981BE8
0x001513B0 [-3] "select * from em\ 0"
-3
EXIT

SQLExecDirect

with return code -1

00981BE8
0x001513B0 [-3] "select * from em\ 0"
-3

DIAG [42S02] [DataDirect][ODBC Oracle Wire Protocol driver]

[Oracle]ORA-00942: table or view does not exist (942)

Based on the error message returned, the table em referenced in the SQL statement executed
doesnt exist in the database. Here, you would want to verify the table does in fact exist. The
table name may be incorrect, the table names maybe case sensitive, or it may exist under a
different catalog or schema than the default. If that is the case, you would need to qualify it in the
query.
Example 2
OdbcTE32

570-6c0
HDBC
HSTMT *

ENTER SQLAllocStmt
01391690
01495184

OdbcTE32
570-6c0
(SQL_SUCCESS)
HDBC
HSTMT *

EXIT

OdbcTE32

ENTER SQLNumResultCols
01394A28
0x0012F958

570-6c0
HSTMT
SWORD *

OdbcTE32
570-6c0
(SQL_ERROR)
HSTMT
SWORD *

SQLAllocStmt

with return code 0

01391690
0x01495184 ( 0x01394a28)

EXIT

SQLNumResultCols

with return code -1

01394A28
0x0012F958

DIAG [S1010] [Microsoft][ODBC Driver Manager] Function

sequence error (0)
In this example the user is getting the error message Function sequence error. According to the
ODBC API Specification entry for SQLNumResultCols, Function sequence error is returned from
SQLNumResultCols when it is called before calling SQLPrepare or SQLExecDirect. The ODBC
trace log shows a statement handle was allocated followed by SQLNumResultCols. Since a
statement wasnt prepared or executed, this error message is valid. To correct the problem, the
application needs to be modified such that SQLNumResultCols is executed in the proper order.
At times, the error message Optional feature not implemented is displayed in the log file. The
error message means the driver doesnt support the functionality being requested. Unless you
require the functionality described by the ODBC function that is failing or this error message is
being returned back to your application, in most instances your problem is not related to this error
and the error can be ignored.

Data Corruption
An ODBC trace log can be helpful when diagnosing data corruption problems. The ODBC trace
file will display your data in clear text or its associated memory address, and you can see the
describe information for any of the columns or data types.
When debugging data corruption problems, its best if you know which statement is causing the
problem. Once the ODBC trace log is created, you can search the trace log for that SQL
statement. Either before or after the SQL statement, verify the values in the column describe
functions (SQLDescribeCol, SQLBindCol, etc) are correct based on how the data is stored and
how your application needs it returned. If executing a parameterized statement, verify
SQLBindParameter is defined correctly for each parameter marker.
Its recommended that you get a Snoop trace as well as an ODBC trace when diagnosing data

corruption problems. The Snoop trace will show the hex values for the data and confirm the data
is being passed correctly between the ODBC driver and the data store. Contact DataDirect
SupportLink to obtain Snoop, or download it from the SupportLink Online Downloads/Tools page
available from http://support.datadirect.com.
Also, try executing the same statement through a databases native tool. The native tools will
display how the data is stored in the database and whether the problem could or could not be the
ODBC driver. If corrupt data also occurs with the native tool, the ODBC driver is not related to the
problem.

Performance
When debugging performance problems, an ODBC trace log can prove helpful. However, to
determine if you have a performance problem, you need a good example demonstrating desired
and undesired performance. The example needs to be in similar environments. Comparing the
performance of 2 Oracle drivers running on the same operating system connecting to the same
database is a good example. Comparing the performance between Oracle and SQLServer is not
a good example. Oracle and SQLServer ODBC drivers and databases are coded completely
differently and they support different functionality. When comparing two different Oracle ODBC
drivers, as in the good example above, you have one variable: the ODBC drivers.
Generating an ODBC trace log of two different scenarios will allow you to compare the
applications steps with the two drivers. Many times an application will execute one set of ODBC
functions with one driver and execute a different set of ODBC functions with another driver based
on the supported functionality of the driver. If that is the case, you need to determine why the
application is executing a different set of ODBC functions.
To determine the supported differences between the drivers, you can to look at the results of the
executed SQLGet functions, i.e. SQLGetInfo, SQLGetFunctions, SQLGetConnectAttr,
SQLGetStmtAttr, etc. These functions will return the supported functionality of the driver and/or
data store. You may need to look up the function attributes in the ODBC specification to
understand what functionality is being requested and their return values.
There is a document available on the DataDirect web site which explains how to write
performance-optimized applications.

Following handles
Handles are memory addresses used to identify a particular item, environment, connection,
statement or descriptor. The handles are used by ODBC functions for each particular item when
the function is called. The driver manager uses the handles to locate each item. You can use the
handles to follow the path an application takes when calling ODBC functions and executing
statements. An application can use multiple environments, connections, statements and
descriptor handles to process various functions.
How do you use handles to debug a problem? Lets say your application is returning an error
message. You can search the ODBC trace log for the error message. You then want to find out
what steps occurred prior to the error.
In the trace log, youll see the data type of the handle being SQLHANDLE, HENV / SQLHENV,
HDBC / SQLHDBC, HSTMT / SQLHSTMT. This value is unique to the particular series of items
being called. You can search in the trace log for this handle number and each call made on the
handle will be shown. This allows you to pick out only the calls in the trace log that are relevant
to your error message. Keep in mind, some applications may create multiple handles. For

example, your application may execute three statements consecutively with each residing on a
different statement handle.

Reproducing problems based on a trace log

An ODBC trace log can be used as a guide for reproducing a problem in a simpler environment.
It helps isolate the problem to a few numbers of steps. One way of reproducing the problem
outside of your application is to use the program ODBC Test. ODBC Test is a tool developed by
Microsoft. Contact DataDirect SupportLink for ODBC Test, or download it from the SupportLink
Online Downloads/Tools page available from http://support.datadirect.com.
ODBC Test is a GUI application that allows a user to call any of the ODBC functions. Therefore,
any function found in an ODBC trace can be executed within this program.
In this example, I used MS Query to fetch data out of a Sybase database. I created an ODBC
Trace file so you can see what ODBC functions MS Query calls.
msqry32

348-948
ENTER SQLPrepare
HSTMT
008C1A90
UCHAR *
0x0018A8E0 [
69] "SELECT EMP.FIRST_NAME,
EMP.LAST_NAME, EMP.DEPT\ d\ aFROM test.dbo.EMP EMP"
SDWORD
69
msqry32
348-948
EXIT SQLPrepare with return code 0
(SQL_SUCCESS)
HSTMT
008C1A90
UCHAR *
0x0018A8E0 [
69] "SELECT EMP.FIRST_NAME,
EMP.LAST_NAME, EMP.DEPT\ d\ aFROM test.dbo.EMP EMP"
SDWORD
69
msqry32

348-948
HSTMT

ENTER SQLExecute
008C1A90

msqry32
348-948
(SQL_SUCCESS)
HSTMT

EXIT

msqry32

ENTER SQLNumResultCols
008C1A90
0x0012E57E

348-948
HSTMT
SWORD *

SQLExecute

008C1A90

msqry32
348-948
(SQL_SUCCESS)
HSTMT
SWORD *

EXIT

msqry32

ENTER SQLBindCol
008C1A90
1
1 <SQL_C_CHAR>
0x0018B530
9
0x0012E37C

348-948
HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

msqry32
348-948
(SQL_SUCCESS)

with return code 0

SQLNumResultCols

with return code 0

008C1A90
0x0012E57E (3)

EXIT

SQLBindCol

with return code 0

msqry32

HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

008C1A90
1
1 <SQL_C_CHAR>
0x0018B530
9
0x0012E37C (0)

348-948
HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

ENTER SQLBindCol
008C1A90
2
1 <SQL_C_CHAR>
0x0018B539
11
0x0012E37C

msqry32
348-948
(SQL_SUCCESS)
HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

EXIT

msqry32

ENTER SQLBindCol
008C1A90
3
1 <SQL_C_CHAR>
0x0018B544
5
0x0012E37C

348-948
HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

SQLBindCol

with return code 0

008C1A90
2
1 <SQL_C_CHAR>
0x0018B539
11
0x0012E37C (0)

msqry32
348-948
(SQL_SUCCESS)
HSTMT
UWORD
SWORD
PTR
SQLLEN
SQLLEN *

EXIT

SQLBindCol

msqry32

348-948
HSTMT

ENTER SQLFetch
008C1A90

msqry32

348-948
HSTMT

EXIT SQLFetch
008C1A90

with return code 0

008C1A90
3
1 <SQL_C_CHAR>
0x0018B544
5
0x0012E37C (0)

with return code 0 (SQL_SUCCESS)

Ill follow the same steps in ODBC Test. See the document ODBCTest.doc included in the ODBC
Test download for a tutorial on using ODBC Test. Here are results from the output box:
Full Connect(Default)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN '500_Sybase12'.

SQLPrepare:
In:
StatementHandle = 0x00981930,
StatementText = "SELECT EMP.FIRST_NAME, EMP.LAST_NAME,
EMP.DEPT FR...", TextLength = 68
Return:
SQL_SUCCESS=0
SQLExecute:
In:
StatementHandle = 0x00981930
Return:
SQL_SUCCESS=0
SQLNumResultCols:
In:
StatementHandle = 0x00981930, ColumnCountPtr = 0x0015AD98
Return:
SQL_SUCCESS=0
Out: *ColumnCountPtr = 3
SQLBindCol:
In:
StatementHandle = 0x00981930, ColumnNumber = 1,
TargetType = SQL_C_CHAR=1, TargetValuePtr = VALID,
BufferLength = 9, StrLen_or_IndPtr = VALID
Return:
SQL_SUCCESS=0
SQLBindCol:
In:
StatementHandle = 0x00981930, ColumnNumber = 2,
TargetType = SQL_C_CHAR=1, TargetValuePtr = VALID,
BufferLength = 11, StrLen_or_IndPtr = VALID
Return:
SQL_SUCCESS=0
SQLBindCol:
In:
StatementHandle = 0x00981930, ColumnNumber = 3,
TargetType = SQL_C_CHAR=1, TargetValuePtr = VALID,
BufferLength = 5, StrLen_or_IndPtr = VALID
Return:
SQL_SUCCESS=0
SQLFetch:
In:
StatementHandle = 0x00981930
Return:
SQL_SUCCESS=0

Using the example above, I can program a C application to follow the same steps. You can use
which ever programming language you like as long as it will all you to import the ODBC header
files. In this example, Ill be using C.
/*
** Allocate a HSTMT to communicate with ODBC DB Driver.
*/
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("Unable to Allocate a HSTMT:\n");
ODBC_error (SQL_HANDLE_DBC, hdbc);
EnvClose (henv, hdbc);
exit (255);
}
/*
** Build the SQL statement
*/
strcpy ((char*)table, "EMP");
strcpy ((char*)sql, "SELECT FIRST_NAME, LAST_NAME, EMP_ID ");
strcat ((char*)sql, "FROM EMP");
/*
** Prepare our SQL Statement for Executions.
*/
rc = SQLPrepare (hstmt, sql, (SDWORD)strlen((char*)sql));
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("SQLPrepare has Failed. RC=%d\n", rc);

ODBC_error (SQL_HANDLE_STMT, hstmt);

EnvClose (henv, hdbc);
exit (255);

}
/*
** Execute Prepared SQL
*/
rc = SQLExecute (hstmt);
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("......SQLExcute has Failed. RC=%d\n", rc);
ODBC_error (SQL_HANDLE_STMT, hstmt);
EnvClose (henv, hdbc);
exit (255);
}
/*
** Bind variables to SQL Columns
*/
rc = SQLBindCol (hstmt, 1, SQL_C_CHAR,
&first_name,
(SDWORD)sizeof(first_name),
&val_first_name);
rc = SQLBindCol (hstmt, 2, SQL_C_CHAR,
&last_name,
(SDWORD)sizeof(last_name),
&val_last_name);
rc = SQLBindCol (hstmt, 3, SQL_C_CHAR,
&emp_id,
(SDWORD)sizeof(emp_id),
&val_emp_id);
/*
** Fetch Data
*/
printf("\n");
printf("%-15s %-15s %-15s\n",
"First Name", "Last Name", "Emp ID");
printf("%-15s %-15s %-15s\n",
"----------", "---------", "---------");
for (;;) {
rc = SQLFetch (hstmt);
if (rc == SQL_NO_DATA_FOUND) {
printf ("SQLFetch returns: SQL_NO_DATA_FOUND\n");
break;
}
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
printf ("SQLFetch has Failed. RC=%d\n", rc);
ODBC_error (SQL_HANDLE_STMT, hstmt);
break;
}
printf("%-15s %-15s %-15s\n",
first_name, last_name, emp_id);
}
printf("\n");

Here is the output of this application:

Allocating ODBC Environment...

Connecting to DSN: 500_Sybase12, UID: test01, PWD: test01
First Name
---------Tyler
.
.
.

Last Name
--------Bennett

Emp ID
--------E10297

The above examples show you 2 ways to use an ODBC trace to replicate an applications steps
in a small, stand-alone environment. This is useful for debugging purposes and also useful when
reporting issues to DataDirect SupportLink as youll have a way to reproduce an issue outside a
full-scale application.