February 2011 Edition
Working with ODBC DataSources on Microsoft® Windows® (32-bit versus 64-bit)
With an increased demand for performance, many database administrators are taking advantage of a Microsoft® Windows® 64-bit (x64) operating system that can provide additional memory to applications. However, when moving their applications from their legacy 32-bit (x86) Windows systems, administrators are frequently confused - how should they set up the new ODBC database connections for their legacy application?
This article is intended to clarify this difference, and should help illustrate what needs to be done to adopt an existing 32-bit database application that uses ODBC on a 64-bit system.
A 64-bit Windows system can host both 32 and 64-bit applications – this is known as ‘Windows-on-Windows 64’ (WOW64). (Note: In ‘Task Manager’ 32-bit applications are indicated with an asterisk ('*') next to the executable name).
When defining ODBC DataSources (DSNs), the system administrator needs to remember that there are four "types" of DSNs that can be now created on a 64-bit operating system:
(32-bit) User DSNs
(32-bit) System DSNs
(64-bit) User DSNs
(64-bit) System DSNs
32-bit applications must reference the 32-bit ODBC driver and 32-bit DSNs. 64-bit applications must reference the 64-bit ODBC driver and 64-bit DSNs. System DSNs must be used for any clients running as Windows Services (e.g. Applications running on web servers, any "daemon" clients, etc.). For .NET applications, the ODBC data source bitness must match the type of architecture of the .NET Common Language Runtime (CLR) that is running. If the .NET project is compiled for ‘Any CPU’, the CLR will pick its own bitness based on the type of operating system that the CLR is currently running on (e.g. x86 on x86, x64 on x64). Compiling the .NET project for ‘x86’ or ‘x64’ will force the CLR to use a particular bitness across all operating systems.
There are also two ODBC Administrators - one that manages 64-bit DSNs, and one that manages 32-bit DSNs. They both have the same executable name but they reside in different locations on the file system:
When working with the SQL Anywhere ‘dbdsn’ utility, a system administrator needs to be careful to reference the appropriate ‘dbdsn’ executable, depending on the bitness of the ODBC DSN that is being created.
For a 32-bit application, use:
For a 64-bit application, use:
The ‘dbdsn.exe’ utility is a very useful command-line tool to create DSNs when deploying a new application.
To add a new data source:
dbdsn -w "My DSN" -c "UID=DBA;PWD=sql;DBF=C:\mydb.db"
To list existing data sources:
To delete a data source:
dbdsn -d "My DSN"
For a full description of the ‘dbdsn’ utility, please refer to the help:
C:\ >dbdsn -?
SQL Anywhere Data Source Utility Version 22.214.171.12401
dbdsn [options] -l[s|u] list data sources
dbdsn [options] -d[s|u] <dsn> delete a data source
dbdsn [options] -g[s|u] <dsn> get details of a data source
dbdsn [options] -w[s|u] <dsn> [details] write data source definition
dbdsn -cl list connection keywords
The [s|u] specifier refers to System or User data sources. The default
@<data> expands <data> from environment variable <data> or file <data>
Options (use specified upper- or lower-case letter, as shown):
-b brief: print connect string
-cm display DSN creation command (with -g or -l)
-dr include DRIVER parameter (with -g or -l)
-o <file> log output messages to file
-or DSN is an iAnywhere Oracle driver DSN (with -c or -cl)
-pe encrypt password
-q do not display messages
-v verbose: print connection values in tabular form
-y delete or overwrite data source without confirmation
supply database connection parameters (see -cl option)
-cw ensure DBF is stored as an absolute pathname (with -c)
It is important to remember that the 32-bit and 64-bit ODBC entries and associated driver information are stored in different areas within the Windows registry on a 64-bit system:
HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI - 64-Bit ODBC Drivers
HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI - 64-Bit System DSNs
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI - 64-Bit User DSNs
HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC\ODBCINST.INI - 32-Bit ODBC Drivers
HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC\ODBC.INI - 32-Bit System DSNs
HKEY_CURRENT_USER\Software\Wow6432Node\ODBC\ODBC.INI - 32-Bit User DSNs
When working with Sybase Technical Support to diagnose an issue with accessing a DSN, it may be required to verify the current ODBC registry settings for the computer.
To do this, you can run the following commands at a ‘Command Prompt’:
regedit /e system_x64.reg HKEY_LOCAL_MACHINE\Software\ODBC
regedit /e system_x86.reg HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC
regedit /e user_x64.reg HKEY_CURRENT_USER\Software\ODBC
regedit /e user_x86.reg HKEY_CURRENT_USER\Software\Wow6432Node\ODBC
This will export all of the system ODBC settings to “.reg” files which can sent to technical support and verified for correctness. (Note: Be careful of any stored ODBC DSN passwords that were extracted in this step before submitting these files to Sybase).