Using an InterSystems Database as an ODBC Data Source on Windows
This chapter describes how to create a DSN for an InterSystems database on Windows, which you can do either via the Control Panel or by creating a file DSN.
Creating a DSN with the ODBC Data Source Administrator
To create a DSN, you can use the Windows ODBC Data Source Administrator to access the InterSystems ODBC Data Source Setup dialog box:
In the Windows Control Panel, select Administrative Tools and click the ODBC Data Sources icon (the actual icon name may vary depending on your version of Windows; see “Selecting the Correct ODBC Data Source Administrator Version” below).
In the Windows ODBC Data Source Administrator dialog, select the User DSN tab and click the Add... button.
Select Intersystems ODBC for the ODBC 2.5 driver or Intersystems ODBC35 for the ODBC 3.5 driver, and click the Finish button.
The following illustration shows an instance of the InterSystems ODBC Data Source Setup dialog box with all required fields filled in:
The fields are listed below and are required unless otherwise specified:
Name — Specifies the user-defined name of the DSN.
Description — Optional. Provides user-defined information about the DSN.
Host IP Address — Specifies the IP address to be used by the ODBC connection in dotted decimal or dotted quad form, such as “127.0.0.1”.
Host Port Number — Specifies the port to be used by the ODBC connection. The default for InterSystems is 1972.
Namespace — Specifies the namespace to use as the ODBC data source.
Authentication Method — Select one of the following options, depending on the security used for this database. For detailed information on these options, see “Authentication” in the Security Administration Guide.
Password — authenticate with standard username and password.
Password with SSL/TLS — authenticate using an SSL/TLS-protected connection (see “Using SSL/TLS” in the Security Administration Guide).
Kerberos — authenticate with Kerberos (see “Configuring for Kerberos Authentication” in the Security Administration Guide). For this option, also specify the following settings:
Connection Security Level — Select Kerberos, Kerberos with Packet Integrity, or Kerberos with Encryption, as appropriate (see the “Client/Server” section of “About Kerberos and the Access Modes” in the Security Administration Guide).
Service Principal Name — Specify the name of the server to be used as a Kerberos principal.
User Name — Optional. Specifies the username to be used by the ODBC connection. By default, this is _SYSTEM (not case-sensitive).
Password — Optional. Specifies the password to be used by the ODBC connection. For the default username, the password is SYS (must be all upper case).
ODBC Log — Optional. If selected, specifies the creation of a log file of ODBC client driver activities for all InterSystems DSNs. This log is for troubleshooting; you should not turn logging on during normal operation as it will dramatically slow down ODBC performance. See “Enabling logging for ODBC on Windows” for more information.
Static Cursors — Optional. If selected, enables the InterSystems ODBC client driver’s static cursor support. If this flag is off, then the cursor support provided by the ODBC Cursor Library will be used. In general, this flag should be off unless you have a specific reason for not using the ODBC Cursor Library.
Disable Query Timeout — Optional. If selected, causes the ODBC client driver to ignore the value of the ODBC query timeout setting.
The ODBC query timeout setting specifies how long a client should wait for a specific operation to finish. If an operation does not finish within the specified time, it is automatically cancelled. The ODBC API provides functions to set this timeout value programmatically. Some ODBC applications, however, hard-code this value. If you are using an ODBC application that does not allow you to set the timeout value and the timeout value is too small, you can use the Disable Query Timeout option to disable timeouts.
Use Locale Decimal Symbol — Optional. When selected, specifies the use of the current locale's decimal separator; not checking this sets the decimal separator in the process to a period (".") regardless of the locale. This value can have an affect when the ODBC connection is interoperating with an application that uses the decimal separator as defined for the current locale.
Unicode SQL Types — Optional. This functionality is only relevant if you are working with a multibyte character set, such as in Chinese, Hebrew, Japanese, or Korean locales. If you are only using single-byte character set data, do not select this check box. If selected, this option turns on reporting of a Unicode SQL type (SQL_WVARCHAR (-9) SQLType) for string data. This allows some Microsoft applications to allocate the properly sized buffers to hold multibyte data.
If an application encounters a “SQL data type out of range” error from the Microsoft Driver Manager using SQLBindParameter, it can be caused by having selected this check box.
After you have created the DSN, you can use the Test Connection button to see if your data source is working correctly.
The Ping button attempts to ping the DSN host machine for the number of times specified in the #Times field. A popup window will display information on ping success or failure.
Selecting the Correct ODBC Data Source Administrator Version
In 64-bit releases of Windows, there are both 32-bit and 64-bit versions of the Data Source Administrator. Select the appropriate version as follows:
In Windows 8 and higher, the Administrative Tools section of the Control Panel offers two clearly labelled options, ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit). Select the appropriate one to create DSNs for 32-bit or 64-bit drivers.
In 64-bit releases before Windows 8, the Control Panel offers only one option (labeled either ODBC Data Sources or Data Sources (ODBC)), which will start the 64-bit version. To create a DSN for a 32-bit driver, run the 32-bit Data Source Administrator located at:
Both versions of the ODBC Data Source Administrator executable are named odbcad32.exe, but they are in different locations. In Windows releases prior to Windows 8, the 64-bit executable is in %windir%\System32, and the 32-bit executable is in %windir%\SysWOW64 (which may be the opposite of what you expect). Use the one in SysWOW64 to create DSNs based on the 32-bit drivers.
Using File DSNs and DSN-less Connections
DSN information is typically stored in the Windows Registry (under [HKLM\SOFTWARE\ODBC]), but you can also specify connection information in a file DSN (a text file with extension .dsn).
A file DSN can be created with either the ODBC Data Source Administrator (from the File DSN tab) or a standard text editor. For detailed information, see the Microsoft support site (search on "file DSN").
The file DSN can specify the name of an existing DSN to use, for example:
[ODBC] DSN=InterSystems ODBC Sample Code
or it can specify a set of key-value pairs that specify the same connection information as a standard registry entry.
A file DSN is invoked by a call to SQLDriverConnect.
File DSNs are typically stored in \Program Files\Common Files\ODBC\Data Sources, but you can use the File DSN tab in the ODBC Data Source Administrator to define a different default location.
ODBC Connection Strings
SQLDriverConnect takes a connection string argument that can specify connection information in three different ways:
Specifies the name of a regular DSN in the registry. For example:
Specifies a file DSN rather than a registry entry. For example:
Defines all connection information directly in the connection string. For example:
"Driver=Cache ODBC Driver;Host=127.0.0.1;Port=56772;Database=USER;UID=myUsername;PWD="