Windows 7 and ODBC

 

ODBC - Open Database Connectivity - is a standard for remote application clients to communicate with databases. It has quite a long history - almost 20 years - and it is still very much used today, and is supported on many different operating systems.

Various versions of MS Windows have supported it, mostly in a 32 bit format. There is also a 64 bit format, which Microsoft supported in 64 bit Windows XP, then 64 bit Vista, and now in 64 bit Windows 7.

64 bit XP and 64 bit Vista were not that commonly used. However now that even bottom-end laptops have 64 bit hardware, 64 bit Windows 7 is becoming quite commonly used.

On 32 bit Windows 7, ODBC is pretty well the same as it always has been. However on 64 bit Windows 7, there is a bit of a confusion factor, which may catch people out.

 

32 bit Windows

On 32 bit Windows, Microsoft provide a GUI tool which is C:\Windows\System32\odbcad32.exe.

It can be run by going to the file in Windows Explorer, using the RUN facility, or by going to Control Panel / Administrative Tools / Data Sources (ODBC).

This tool allows users and administrators to set up connections for ODBC to various types of database, by providing several different drivers - for example, for Access, Excel, dBase, FoxPro, Oracle, Paradox, and SQL.

The drivers are .dll files, also located in the System32 folder.

 

64 bit Windows 7

With 64 bit Windows 7, things get more confusing.

In 64 bit Windows 7, the system folder C:\Windows\System32\ also contains a file called odbcad32.exe.

BUT - this is a 64 bit version of the ODBC management tool, not the 32 bit version - despite the name !

To add to your woes, Microsoft only provide one driver for 64 bit ODBC, and this is for SQL server. It is also a .dll file - at this point you probably will not be surprised to find that it has the same name as the 32 bit driver for SQL.

So in 64 bit ODBC in Windows 7, not only have they used the same names as 32 bit ODBC, but they have removed all but one of the drivers.

 

32 bit ODBC in Windows 7

All this doesn`t mean the end of 32 bit ODBC in Windows 7 - tucked away in the Windows on Windows 64 system folder there are the 32 bit ODBC files - with the same names.

The Windows on Windows 64 system folder is C:\Windows\SysWOW64\.

So by running that version of odbcad32.exe, you can set up 32 bit ODBC. All the same drivers that are in 32 bit XP are provided.

 

Beware of the PATH

In 64 bit Windows 7 Home Premium, the PATH environmental variable does not contain a reference to the system folder C:\Windows\SysWOW64\, but it does contain one for \System32\ - so if you run odbcad32.exe from either a DOS window or the RUN box, it is the 64 bit version of the ODBC administration tool that is run.

If you want to run the 32 bit version from either of these places, you will have to explicitly state the full path.

I don`t know if the Windows on Windows 64 system folder C:\Windows\SysWOW64\ is included in the PATH on other versions of 64 bit Windows 7 - however even if it is, there is still the problem of which folder is listed first - \System32\, or \SysWOW64\. Both the DOS window and the RUN box will go to the first one listed.

 

32 bit or 64 bit ODBC ?

It does not matter if the operating system is 64 bit, the choice of whether to use 32 bit ODBC or 64 bit ODBC is dictated solely by the application using it.

If it is a 32 bit application, use 32 bit ODBC.

If it is a 64 bit application, use 64 bit ODBC.

 

ODBC and the registry

DSN`s - Data Source Names - are packages of information that define a data source - ie, a database server, and they are stored in the registry.

System DSN`s set up using the 64 bit administration tool are stored in 


     HKLM\Software\ODBC\ODBC.INI

System DSN`s set up using the 32 bit administration tool are stored in 


     HKLM\Software\Wow6432Node\ODBC\ODBC.INI

User DSN`s set up by both the 32 bit and the 64 bit administration tools are stored in 


     HKCU\Software\ODBC\ODBC.INI

All this therefore means that system DSN`s can only be seen in the version of odbcad32.exe that set them up. But user DSN`s can be seen in both versions of odbcad32.exe, it doesn`t matter which version set them up.

 

ODBC and NetBios

When setting up DSN`s, I don`t think it is a good idea to use a server name - this is resolved by NetBIOS.

NetBIOS is a rather old and famously insecure protocol that really should be disabled throughout the whole organisation.

It would appear that it is better to use IP addresses and port numbers to define the server. I think you can also use fully qualified domain names if the system is within Active Directory, as these would use the AD DNS for resolution.

Using IP addresses and port numbers has an advantage that non-standard port numbers can be used in the data source.

 

The ODBC stack

The ODBC stack itself has four layers -

⇒   application layer
this calls ODBC functions to produce SQL statements that are sent to the data source, and processes the results that are sent back
responsible for opening and closing a session
the user applications can be written in several different languages, and are often written in C or C++ - required header files are Sql.h, Sqlext.h, and Sqltypes.h - other available ones are Odbcinst.h, Sqlucode.h, and Odbcss.h
⇒   driver manager
sometimes refered to as the ODBC manager - interprets and manages the communication between the application and the ODBC drivers
the driver manager is \System32\odbc32.dll for 64 bit ODBC, and \SysWOW64\odbc32.dll for 32 bit ODBC
⇒   driver
designed for a specific type of data source, the driver processes the function calls from the application, and interacts with the data source
⇒   data source
the server with the database

 

On a real network, the stack would have to be extended to include transmission over TCP/IP.

 

 

 

 

 

website design by ron-t

 

website hosting by freevirtualservers.com

 

© 2024   Ron Turner

 

+                                   +  

 

Link to the W3C website.   Link to the W3C website.