Tracing is a way to debug issues when the source of a problem or error cannot be determined. Below three parts are meant to help to understand the ODBC trace.
- Knowing the origination of the error message
- How to Read an ODBC Trace Log
- SQLSTATEs in ODBC trace log
PART 1: Knowing the origination of the error message:
ODBC trace can be used to narrow down a problem to its origination point—the 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
PART 2: How to Read an ODBC Trace Log
ODBC function
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 XXX-YYY EXIT ODBC_Function with return code N (RetCode)
DataType value_of_argument_1
DataType value_of_argument_2
DataType value_of_argument_3
Process_Name
-
The name of the process being traced
XXX
-
The current process id
YYY
-
The current thread id
ENTER : EXIT
-
Enter or exit the ODBC function
ODBC_Function
-
The ODBC API function being executed by the application
RetCode
-
A return code provided by the ODBC driver which determines the status of the function executed
DataType
-
The data type of the ODBC function’s argument
Value_of_argument -
The argument’s data which is passed or returned during the ODBC function’s execution
ODBC function example Here is an example of executing the function SQLExecDirect using an ODBC application called ODBC Test.
OdbcTE32 870-4e8 ENTER SQLExecDirect
HSTMT 00982470
UCHAR * 0x0015B370 [ -3] "select * from emp\ 0"
SDWORD -3
OdbcTE32 870-4e8 EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
HSTMT 00982470
UCHAR * 0x0015B370 [ -3] "select * from emp\ 0"
SDWORD -3
According to the ODBC Specification, the SQLExecDirect function takes 3 arguments:
RETCODE SQLExecDirect(hstmt, szSqlStr, cbSqlStr)
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr |
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr |
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr |
SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr | SET QUERY_BAND = ‘MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;MSTRPriority=!i;’ FOR SESSION;
Pre/Post Statements Cleanup Post Statement SET QUERY_BAND = NONE FOR SESSION; Pre/Post Statements Element Browsing Pre Statement SET QUERY_BAND = ‘MSTRProject=!p;;MSTRReport=ELEMENTBROWSE;;MSTRUser=!u;;MSTRJob=!j;;MSTRPriority=!i;;’ FOR SESSION; Pre/Post Statements Element Browsing Post Statement SET QUERY_BAND = NONE FOR SESSION; Query Optimizations Sub Query Type WHERE (col1, col2...) IN (SELECT s1.col1, s1.col2...)TypeArgumentUseDescriptionHSTMThstmtInputStatement handleUCHAR FAR *szSqlStrInputSQL statement to be executedSDWORDcbSqlStrInputLength of szSqlStr |
Compare the arguments above to the trace log to see what information is passed to the SQLExecDirect function.
hstmt 00982470
szSqlStr “select * from emp\ 0”
where “\ 0” is the null terminator
cbSqlStr -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. It’s possible that, if the error isn’t 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 ENTER SQLExecDirect
HSTMT 00981BE8
UCHAR * 0x001513B0 [-3] "select * from em\ 0"
SDWORD -3
OdbcTE32 4c8-d3c EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 00981BE8
UCHAR * 0x001513B0 [-3] "select * from em\ 0"
SDWORD -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 doesn’t 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 ENTER SQLAllocStmt
HDBC 01391690
HSTMT * 01495184
OdbcTE32
570-6c0 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 01391690
HSTMT * 0x01495184 ( 0x01394a28)
OdbcTE32
570-6c0 ENTER SQLNumResultCols
HSTMT 01394A28
SWORD * 0x0012F958
OdbcTE32
570-6c0 EXIT SQLNumResultCols with return code -1 (SQL_ERROR)
HSTMT 01394A28
SWORD * 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 wasn’t 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.
PART 3: SQLSTATEs in 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 Programmer’s Reference on the MSDN site.
Example: When SQLFetchScroll returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a handle of StatementHandle.
Refer to Technical Document below on how to obtain the SQLSTATE value to troubleshoot SQLFetchScroll error:
KB38324: Troubleshooting the root cause when SQLFetchScroll statement returns SQL_ERROR during retrieval of results from the warehouse in Strategy 9.x
Further readings: Refer to Technical Document below on how to get process id and timestamps logged in ODBC tracing in Strategy 10.x:
How to get process id and timestamps logged in ODBC tracing in Strategy 10.x
Refer to Technical Document below on how to enable ODBC trace in Unix:
KB11979: How to turn on/turn off ODBC tracing in Strategy Intelligence Server Universal 9.x - 10.x
Refer to Technical Document below on how to enable ODBC trace on Windows:
KB8699: How to enable 32-bit Open Database Connectivity (ODBC) tracing in Strategy Intelligence Server 9.x?
Article Reference Number: KB441650
