ODBC is a standard which allows applications to import data from any compliant database, without having to understand the database file format or server protocol. In fact, the ODBC architecture is quite straightforward and is built on two tiers between an application and a database from which it wants to import data.
ODBC manager
The heart of the ODBC architecture is a piece of Microsoft software called the ‘ODBC manager’, which is the central point of contact for an application. An application can interrogate the ODBC manager to find out what drivers and data sources are installed on a machine, and use this information to make connections to the required database. The ODBC manager handles the connection request, and locates the appropriate ODBC driver in order to establish the connection.
ODBC drivers
A database system is ODBC-compliant if it provides a piece of software called an ‘ODBC driver’. This driver will generally be installed as part of the database installation process, together with the ODBC manager, if it was not previously present.
Note: STEM does not install the ODBC manager because STEM does not provide an ODBC driver for exporting data from STEM.
The driver software understands the file-format or server protocols relevant to the corresponding database system, and is responsible for extracting the actual data.
ODBC architecture
Once an application has established an ODBC connection to a database, it will extract data through an SQL query. The benefits of ODBC are two-fold:
- the application doesn’t need to know the database file format or server protocol
- a common SQL grammar can be used with any ODBC connection, regardless of the underlying database format.
Connection string
An application requests an ODBC connection by passing a series of key/value pairs as formatted text, called the ‘connection string’, to the ODBC manager. In general this string first identifies the desired driver, and then any additional parameters which are required or understood by the driver.
For example, the connection string for a Microsoft SQL Server database called ‘MyDatabase’ located on a machine called ‘MyServer’ would look like:
DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;
UID=sa;PWD=;
Note: The user-id sa is the default system-administrator id for many databases.
The ODBC manager and/or driver will typically prompt for any missing or malformed parameters in the connection string.
Data Source Names (DSN)
In order to simplify the information needed by an application to make an ODBC connection, the ODBC manager provides the facility to associate a simple name, e.g., ‘MyDataSource’, with a specific driver and additional parameters, which can then be identified by a simple connection string:
DSN=MyDataSource;
Control panel
If the ODBC manager is installed on your machine, there will be an icon in the Windows Control Panel called something like ‘32bit ODBC’, which allows you to access the ODBC Data Source Administrator dialog. This dialog has tabs for:
- User, System and File DSN, where you can create data source names
- ODBC Drivers, where you can see which drivers are installed
- Tracing, which you can use to debug any problems with ODBC
- About, which gives version information about the various software components.
Note: STEM does not support 16-bit ODBC.