Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

Before the ODBC interface to databases was developed in the late 80:s and early 90:s, it was difficult to connect to a database and import data. But thanks to Microsoft and some other DB vendors, we got an open interface with which we still today can load data from almost any database.

But, some aspects of the Windows ODBC implementation are confusing…

When ODBC was developed, computers were running DOS or Windows 3.1, i.e. 16-bit programs, and as a consequence, ODBC was also 16-bit. Then came 32-bit programs and it got messy: You could not use the 16-bit ODBC with your 32-bit programs – you had to use the 32-bit ODBC. But at least there were two icons for the two different ODBC:s in the control panel, so it was clear what you needed to do to configure the right driver.

ODBC Basic.png

Today, with 64-bit operating systems, we have a similar situation: There are both 32-bit and 64-bit programs. But it has become even more confusing, because there is only one ODBC icon in the control panel. And many users do not know that this is just for the 64-bit ODBC.

Facts:

  • A program always needs the correct ODBC driver: A 64-bit driver for 64-bit programs, and a 32-bit driver for 32-bit programs.
  • The 64-bit drivers are configured using C:\Windows\System32\odbcad32.exe
    (Can be started from the ControlPanel ˃ Adminstrative Tools > Data Sources (ODBC).)
  • The 32-bit drivers are configured using C:\Windows\SysWoW64\odbcad32.exe
    (Cannot be started from the control panel.)

And, no, there are no typos in the paths and the file names. They are really named like that. Trust me. The 32-bit administrator really is found in SysWoW64, and the 64-bit is found in System32. Microsoft cannot have had any usability tests here...

To simplify things, QlikView has menu items for both ODBC administrators in the script editor:

QlikVIew Toolbar.png

With these, you can open the correct ODBC Administrator easily. But note – the two administrators cannot run at the same time. You need to close the open one before you can open the other.

Further, when you create your data source, you can choose between creating a User DSN or a System DSN. The latter can be accessed by any user, while the former can be accessed only by you.

ODBC Admin.png

But unfortunately, that is not the only difference between the two. There is a second, confusing difference: The list of User Data Sources is a mixture between 32-bit and 64-bit User DSN:s, so when you create the connect string, you will also see unusable data sources (see below). If you choose an unusable data source, you will get an error message that talks about an “architecture mismatch”. To avoid mixed lists, you should never create any User DSN:s. Always create System DSN:s.

Choosing data source.png

Finally, a couple of words on how it is that QlikView can use both 32- and 64-bit ODBC drivers: The QV.exe itself never connects to ODBC. Instead it launches a separate process. Depending on whether CONNECT32 or CONNECT64 is used, QVConnect32.exe or QVConnect64.exe is launched, which connects to ODBC and streams the data in QVX format to the QV.exe.

ODBC 2 QlikView.png

With this solution it is possible to use 32-bit ODBC drivers together with your 64-bit QlikView.

HIC

36 Comments
narband2778
Creator II
Creator II

Thanks Henric,

very useful information.

0 Likes
4,548 Views
thornofcrowns
Specialist II
Specialist II

Very useful as always. The "Force 32 Bit" tick box is a shortcut to happiness, I think!

0 Likes
4,548 Views
eruditio
Partner
Partner

Bless you

Antoine

0 Likes
4,548 Views
Not applicable

Nice as always!

Thanks

Renjith Kumaran

0 Likes
4,548 Views
stevedark
MVP & Luminary
MVP & Luminary

Thanks Henric - I had never spotted those shortcuts to the ODBC Administrators before.  No more having to remember that the 32 bit administrator lives in the SysWOW64 folder!

0 Likes
4,548 Views
Not applicable

Great Post as always!

Regards,

Divya

0 Likes
4,548 Views