Power to Build

Home » Databases » Troubleshooting ODBC (Kofax)

Troubleshooting ODBC (Kofax)

A friend in our desktop support team is trying to install Kofax software on a Windows 2008 machine. He is using Kofax capture 10.2. Apparently, our earlier version (6.x) is working fine in production. I really didn’t know what Kofax was before. I only went to help him with the database connection issue. I often try to learn something new, by helping others with something I know.

From what I understood, the software, called Kofax Capture (previously Ascent Capture), is used to batch scan images and by OCR or even looking up in a database, it can classify the batches automatically. Here is a snippet from their own description:

Kofax Capture™ automates and accelerates business processes by capturing all types of paper and electronic documents and forms, transforming them into accurate and actionable information, and delivering it all into core business applications, processes and workflows.

Kofax with Oracle (through ODBC)

My friend was using the database look up process to be able to identify each batch. Apparently, a document with a barcode is scanned along with the batch of documents. The Bar code contains one of the keys in our database. This is what the process uses to look up the meta data in the database. To do this, he was setting up Kofax to connect to our Oracle database to pick up some meta data from our table to be attached to the image file he picked up. The software is interesting in that, you can attach a query to a document set and that will attach the corresponding meta data automatically. And all these are scriptable.

He had first set up with Oracle 11g driver. This seemed to have failed initially.  When he talked to Kofax, they had suggested to downgrade to 10.2. and he installed Oracle client 10.2.3. This broke it completely, in the sense, even the ODBC datasource setup for Oracle kept giving unsupported method error. I looked at this setup a few days ago and thought there was some incompatibility with Oracle drivers on Windows 2008. In any case, I felt we had to make it to work with the latest version of Oracle client (our Server is Oracle 11g). So, I asked him to re-install the latest 11g client.

I came back again with some preparation and tools. We tried the new combination (Kofax Capture 10.2 with Oracle 11.2 driver). It kept failing – Interactive Query in the Kofax GUI client (called index.exe) against Oracle kept failing silently while the script seemed to succeed intermittently.

I really wanted to see the SQLs he was sending to the database. I didn’t know if there was any tool to capture the SQLs from ODBC. I resorted to Oracle v$ tables on the database side. I was able to see see his session and the SQLs. This was comforting. At least we know, it was actually connecting to the database. Using other tools (like sqlplus), I am now certain that Oracle client was working fine. Now, it was time to debug the ODBC/Oracle bridge.

Windows ODBC Setup

To connect to any database (or file) using ODBC, you need to setup Data Sources. To do this, you can go into Control Panel -> Administrative Tools -> ODBC Data Sources (32-bit*) option. It shows you several tabs, including File DSN, User/Machine level Data sources, Available drivers etc. One of the options is to enable trace. This comes in handy immensely while trying to troubleshoot the ODBC driver/connection. Note that the trace file is for all ODBC data sources, so make sure you are using onlyone data source at a time, to be able to debug properly.

 

2014-05-26 21_03_22-ODBC Data Source Administrator (32-bit)

“User DSN” refers to datasources that are available only to the current user. “Machine DSN” means it’s visible to all users logging into this machine. File DSN is a file that contains the settings saved in a file. This comes in handy to use the same setup on different machines. Drivers tab contains list of the database drivers available on this machine. There is also an option to trace entire machine. Again use with caution, if there are multiple users logged on to the machine.

Way back, ODBC settings used to be stored in a couple of ini files – ODBC.ini and ODBCinst.ini. These are now stored in registry in the following location:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

This contains a list of drivers and their settings. HKCU contains all the datasources for the currently logged in user where as, HKLM contains the settings for the entire machine. This link shows this information for a Sybase driver.

ODBCINST contains information about the drivers installed on this machine/user.

*Our operating system was Windows 2008 32-bit. This made things a bit easier. If this was a 64-bit OS, things will be slightly different. The program that’s tied to the Control Panel item is typically odbcad32.exe. This is actually the 64-bit version of the software.  If you are trying to get to the 32-bit ODBC administrator on a 64-bit machine, you will need to run the program in SysWOW64. You cannot mix the bitness. If your database client is 32-bit, then you will have to use 32-bit ODBC admin and 64-bit admin, otherwise. See here for more information. The drivers tab will only list drivers with the same bitness as the admin software. See this Microsoft note for more info.

 

Troubleshooting ODBC driver

First, my goal was to find out how compatible was the Oracle driver with the new ODBC drivers on Windows 2008. This way, I can find out if the driver would even work for the current setup. One of the tools I found earlier, called ConfTool from Microsoft (?), which shows the conformance/compatibility of a certain database driver to ODBC levels. Unfortunately, this only tests ODBC Levels 1 and 2.

The tool showed, while Oracle driver seems to support most of the functions, there were some features and attributes that were failing.
Next, I really wanted to see if the data source used by Kofax was working. To do this, I used was SQLRun from SQLEdit.This is a command line utility that lets you test any SQL against a specific ODBC Data Source. This is a real cool tool. I set it up, so it will use the ODBC data source we setup earlier for use with Kofax. I also used the query coming from Kofax that I captured in the database above. The goal was to see the result of issuing this query worked OK through the data source outside of Kofax. I had already made sure this query worked in Oracle SQL*Plus. Now, I am trying the same query through ODBC. This will guarantee that the ODBC to Oracle connection is OK.

 


You can setup all the required parameters for a test run in a file called SQLRun.ini. These parameters can also passed on commandline. SQLRun has a lot commandline options.

Here is what SQLRun.ini looks like for my test:
SQLRun.ini

[Connection]
DSN=testDB1
User=<user>
Password=<pwd>
[Settings]
Output=out.txt
Errors=error.log
Delimiter=t
Header=1
OverwriteOutput=0
OverwriteLog=0
NullIsEmptyString=0
[Script]
;File=source.sql
Statement=Select * from latax.sysparm where rownum < 2;

When it completes, if all goes well, the record(s) fetched will be placed in Out.txt file. Errors.txt file contains any errors.

I turned on ODBC Trace just before this step. I turned it off right after, so the trace file now contains all the ODBC calls generated for transaction in SQLRun. Since this worked fine, I will keep this trace as my baseline to compare against.

Troubleshooting Kofax ODBC/Oracle connection

After confirming that the ODBC setup worked fine outside of Kofax, I returned to verifying the connection from Kofax. I turned on a fresh ODBC Trace with a new trace file. Then I asked my friend to run Kofax query in the Index tool.

At the end of  this test, I have 2 trace files for the same SQL run through ODBC connection to Oracle – one from command line and another through Kofax. I diffed the 2 files using Winmerge. There was the difference I was looking for.

The Kofax software was sending several additional ODBC calls. One of them was to SQLSetStmtAttr function to set SQL_ATTR_QUERY_TIMEOUT to 0 (see here for more on this). This seemed to cause the query to fail immediately. Now, why they would set the timeout to 0, I don’t know.

I wanted to increase the timeout value. I couldn’t figure out if Kofax has a setting to change this. I tried the next best thing – can this feature be disabled? I looked around and found Enable Timeout Checkbox on the data source details in ODBC Admin screen. I unchecked this and ran Kofax again with new ODBC Trace restarted. Bingo!! That seemed to have done the trick!!!! The screen got populated this time!!

When I diffed the new trace file with the one from the previous run, I noticed this time, it did try to set the Query Timeout to which the Oracle driver returned a -1, saying it’s an unsupported optional feature. From then on, Kofax seemed to have stopped setting the timeout setting before issuing a  query!!!! After we did this, the query passed each time, we tried. (Oracle driver didn’t seem to support the function anyway. But, according to this page, it can be used to avoid infinite wait time to resolve locks).

When we did the same exercise with the scripted (automated) run he had in Kofax, it seemed to go through fine. The Trace output more or less matched my trace file from running the same query in SQLRun.

I really don’t know how the Kofax script was different from running the query interactively in the GUI tool. Whatever it was, the tool was creating a problem and we found a solution. But, I know, this is just a workaround. What if a query really needed timeout? I’ve asked my friend to check with Kofax as to why the discrepancy. That’s for another day 🙂


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: