Getting Started: ODBC Connections to InterSystems Databases
InterSystems provides ODBC drivers that enable you to access InterSystems databases via an ODBC connection. The InterSystems standard installation includes ODBC driver components by default. InterSystems also supports driver managers and other options as described in the following sections:
Installation Options
To use an InterSystems database as an ODBC data source, you should first ensure that the InterSystems ODBC client driver has been installed. The following options are available:
-
The InterSystems standard installation installs ODBC driver components by default (as described in the Installation Guide).
-
If you perform a custom installation, you can select the SQL client only option to install only the ODBC client driver.
-
If InterSystems IRIS is not installed on your system, you can download drivers for Windows, Linux, and macOS from the InterSystems IRIS Drivers pageOpens in a new tab.
-
Other options are available for some UNIX® driver managers (see “ODBC Installation and Validation on UNIX® Systems”).
You must also define DSNs (Data Source Names) to provide your ODBC-aware applications with information needed to connect to InterSystems databases. Each InterSystems database can be represented by multiple DSNs, each of which can support multiple connections. See “Defining an ODBC Data Source on Windows” or “Defining an ODBC Data Source on UNIX®” for OS-specific instructions on how to perform these tasks.
On Windows, InterSystems IRIS IDs use the Large Number (BigInt) datatype, so ODBC client applications must have Large Number support. For example, instances of Access 2016 previous to build 16.0.7812 will display row data as #Deleted. This may also happen if Large Number support is not turned on in the Access Settings for the current database.
Supported ODBC Driver Managers
The InterSystems ODBC drivers are compliant with ODBC 3.5.
InterSystems ODBC supports the following ODBC driver managers:
-
On Windows: the Microsoft Windows driver manager provided with the operating system.
-
On UNIX®: the iODBC driver manager (for use with the Unicode and 8–bit ODBC APIs) and the unixODBC driver manager (for use with the 8–bit ODBC API). See “ODBC Installation and Validation on UNIX® Systems” for more information.
For questions about other driver managers, contact the InterSystems WorldWide Response Center (WRC)Opens in a new tab.
InterSystems also supports Python and Node.js open source modules that work with these driver managers to provide language-specific ODBC interfaces. See the following sections fo details:
For more complete information, including specific supported databases, see the InterSystems Supported Platforms.
An Overview of ODBC
An ODBC system has the following components:
-
The client application — An application makes calls according to the Microsoft ODBC API. ODBC calls establish a connection from the client to a data source (see the section on “ODBC Connection Details”).
-
The ODBC driver manager — The driver manager accepts calls from applications using the ODBC API and hands them off to a registered ODBC client driver. The driver manager also performs any necessary tasks so that the client application can communicate with the client driver and, ultimately, the database server.
-
The ODBC client driver — A database-specific application that accepts calls from a client application through the ODBC driver manager and provides communication to the database server. It also performs any ODBC-related data conversions that the application requests.
-
The database server — The actual database ultimately receiving the calls from the client application. It can be on the same or a different machine than the client driver from which it is receiving calls.
-
An initialization file — A set of configuration information for the driver manager; depending on the operating system, it may also contain client driver information. On UNIX®, this is an actual file, frequently called odbc.ini. On Windows, it is a registry entry.
For a particular vendor database, that vendor may offer its own version of the ODBC client driver for that platform. Oracle, for example, supplies its own ODBC driver for use with Oracle databases on Windows. This may be preferred in some cases because the vendor driver may take advantage of its knowledge of how the database works internally to optimize performance or enhance reliability.
ODBC Connection Details
When connecting to a database via ODBC, an application must generally provide the following connection details:
-
Information about the ODBC client driver to use.
-
Information on locating and accessing the database. For example, this may include the server on which the database resides and the port to use when connecting to it. The details needed depend upon the database technology.
-
Login credentials to access the database, if the database is protected by a password.
In most cases, this information is stored within a DSN, which has a logical name for use within the client application. The DSN may or may not include login credentials, which can also be stored in the database initialization file, or not stored at all.
The DSNs must be registered with the ODBC driver manager.
In practice, a connection is established as follows:
-
A client application includes ODBC calls that attempt to connect to a particular DSN. A client application is linked to an ODBC driver manager, which accepts the calls.
-
The ODBC driver manager reads the initialization file to obtain the location of the ODBC client driver and load the client driver into memory.
-
Once loaded into memory, the ODBC client driver uses the ODBC initialization file to locate connection information for the DSN, as well as other information. Using this information, the client driver connects to the specified database.
-
Having established the connection, the client driver maintains communications with the database server.
If DSN passwords are provided, they are stored in plaintext. Hence, if a password is stored on the host machine when creating a DSN, it may be visible to other users. InterSystems does not recommend storing passwords; this should only occur if there has been a risk assessment.