ODBC Architecture Overview

In a typical ODBC application, there are five components : the client application, the ODBC driver manager, a database-specific driver, an ODBC-compliant database server, and a data source.

The ODBC Client Application

The client application is the component that you have to write. Typically, an ODBC client is written in C or C++. The client interacts with a database by opening a data source (which I will describe in a moment), sending requests to the data source, and processing results.

The ODBC Driver Manager

The ODBC driver manager gets involved when the client application opens a data source. The driver manager is responsible for converting a data source name into a data source handle. After the client has provided the name of a data source, the driver manager searches a configuration file for the definition of that data source. One of the properties contained in a data source is the name of an ODBC driver.

The ODBC Driver

An ODBC driver is a shared library (or DLL on the MS Windows platform). A driver provides access to a specific type of database (for example, PostgreSQL or Oracle). The driver is responsible for translating ODBC requests into whatever form is expected by the backend database. The driver also translates database-specific results back into ODBC form for the client application.

The ODBC-Compliant Database

The backend database processes requests and provides results. By the time the database receives a request from the client application, the driver has already translated the request from ODBC form into a form understood by the server. In the case of PostgreSQL, the PostgreSQL ODBC driver translates requests into libpq function calls.

The Data Source

A data source is a named set of connection properties.

Each data source has a unique name (in the following examples, I use a data source named MoviesDSN ). This name is used by a client application to represent the connection properties needed to connect to a particular database.

Here is a simple data source definition (later, I'll tell you how to actually build a data source definition):

 [MoviesDSN] Driver              = PostgreSQLDriver Description         = Movie Database 

(Don't worry ”you rarely have to build a data source definition by hand. In most cases, you construct a data source using a nice graphical user interface.)

The first line specifies the name of the data source (in this case, the data source is named MoviesDSN ). The data source name is followed by a set of "keyword=value" pairs ”each pair defines a connection property. The Driver property tells the ODBC driver manager which driver should be used to connect to this particular data source. The Description property is a human-friendly description of the data source (this property is displayed in ODBC configuration utilities).

Each ODBC driver supports a different set of connection properties (the Driver and Description properties are used by the driver manager, not by the driver). The PostgreSQL driver enables you to specify the database name, host address, port number, and a number of other properties.

Why does ODBC use a data source instead of letting you specify the connection properties each time you connect? It is much easier for an application (and a human) to work with a data source name than with a huge set of connection properties (I've shown you two properties here ”most drivers support 10 or more properties). Separating the connection properties from the application also makes it much easier for a client to achieve database portability. Rather than embedding the properties in each client, you can use an external configuration tool to define a data source for each database that you might want to use.

Setting Up a Data Source on Unix Systems

Many people think that ODBC exists only in the world of Microsoft Windows ”that's not the case at all. If you are working in a Linux or Unix environment, there are two open -source ODBC implementations: unixODBC (www.unixODBC.org) and iODBC (www.iodbc.org). You can also find commercially supported ODBC implementations for Unix, Linux, and other environments.

Installing unixODBC and the PostgreSQL ODBC Driver

Before you can use unixODBC, you must ensure that it is installed on your system. You'll also need the PostgreSQL ODBC driver. As in previous chapters, I'll assume that you are running a Red Hat Linux host. You'll need two RPM (Red Hat Package Manager) files: unixODBC and unixODBC-kde . Assuming that your host is connected to the Internet, you can use the rpmfind program to download the latest versions:

 # rpmfind --latest --auto unixODBC unixODBC-kde Installing unixODBC will require 2345 KBytes Installing unixODBC-kde will require 244 KBytes ### To Transfer: ftp://ftp.redhat.com/pub/.../RPMS/unixODBC-kde-2.2.0-5.i386.rpm ftp://ftp.redhat.com/pub/.../RPMS/readline-4.2a-4.i386.rpm ftp://ftp.redhat.com/pub/.../RPMS/unixODBC-2.2.0-5.i386.rpm transferring ... 

The rpmfind utility has located and downloaded all the packages that you need and saved them in the /tmp directory. Notice that you asked for two packages, but rpmfind downloaded three. The rpmfind utility checks for dependencies: It found that unixODBC requires the readline package and downloaded that for you as well.

Now that you have the packages downloaded, let's install them:

 # cd /tmp # rpm -ihv *.rpm Preparing...    ########################### [100%] 1: readline     ########################### [ 33%] 2: unixODBC     ########################### [ 66%] 3: unixODBC-kde ########################### [100%] 

If you want to view the list of files installed for a given package, you can use the rpm command in query mode. For example:

 $ rpm -q -l unixODBC-kde /etc/X11/applnk/System/DataManager.desktop /etc/X11/applnk/System/ODBCConfig.desktop /usr/bin/DataManager /usr/bin/ODBCConfig 

The unixODBC package includes the PostgreSQL ODBC driver.

If you install unixODBC from the Red Hat package files, unixODBC will store configuration information in the /etc directory. If you decide to build and install unixODBC from source, the default configuration will store information in the /usr/local/etc directory, but you can override the location at compile time. The remainder of this discussion assumes that you installed from the Red Hat package files and will expect configuration files to be located in /etc .

The unixODBC implementation stores data source information in a set of configuration files (in Windows, ODBC configuration information is stored in the Registry). For any given user, there are three configuration files: a systemwide list of data sources, a systemwide list of drivers, and a user-specific list of data sources.

Each configuration file is organized as a flat text file, divided into sections, starting with a name surrounded by square brackets ( [] ). Each section contains a list of property = value pairs.

The /etc/odbcinst.ini file contains a list of ODBC drivers that are available on your system. Here is a sample odbcinst.ini entry:

 [PostgreSQLDriver] Description     = PostgreSQL driver Driver          = /usr/local/lib/libodbcpsql.so Setup           = /usr/local/lib/libodbcpsqlS.so FileUsage       = 1 

The first line defines a driver named PostgreSQLDriver . When you define a data source, you use this name to connect a data source to a driver. An ODBC driver is usually composed of two shared libraries: a setup library and the driver itself. The ODBC administrator ( ODBCConfig ) uses the setup library to prompt the user for driver-specific configuration information. The driver library contains a set of functions that provide a client application with access to the database. The Driver property contains the name of the driver-shared library. The Setup property contains the name of the setup-shared library. The final property ( FileUsage ) is an enumerated value that describes how a driver maps files into relational tables. ^[1] See the ODBC reference documentation (msdn.microsoft.com/library) for more information.

^[1] The FileUsage property can be set to one of three predefined values: , 1 , or 2 . FileUsage provides a hint to the client application about how the database stores data in the OS file system. Some databases, such as Oracle, can store an entire installation in a single file or in a collection of files ”the actual organization of the data is not important (and is not discernable) to the client application. An Oracle data source has a FileUsage value of . Other databases, such as Paradox, store each table in a separate file. A Paradox data source has a FileUsage value of 1 . Finally, a data source whose FileUsage is set to 2 stores an entire database in a single file. This is different from type in that a type data source can store multiple databases in a single file.

The /etc/odbc.ini file contains a list of ODBC data sources. Remember that a data source is a named set of properties. Here is a sample entry:

 [PostgreSQL] Description         = PostgreSQL Accounting Database Driver              = PostgreSQLDriver 

The first line defines a data source named PostgreSQL . The Description property provides a human-friendly description of the data source (you will see both the description and the data source name in the ODBCConfig program). The Driver property contains the name of an ODBC driver, as defined in the /etc/odbcinst.ini file. Most of the entries in /etc/odbc.ini are more complex than this example. The unixODBC driver manager understands a few more properties, and each driver supports its own set of properties.

Fortunately, you don't have to edit any of the configuration files by hand. The unixODBC package includes a GUI configuration tool named ODBCConfig. When you first run ODBCConfig, you will see a list of all the data sources defined on your system (see Figure 12.1).

Figure 12.1. unixODBC Data Source Administrator.

If you installed unixODBC from the unixODBC and unixODBC-kde packages as previously described, you should find the ODBCConfig application on the KDE Start menu in the System folder. Click the ODBCConfig entry to invoke the program, or run ODBCConfig from a command line. The first time you run this program, you may get a warning that you don't have an .ODBCConfig subdirectory in your home directory ”you can just click the OK button and ignore this warning: ODBCConfig creates the required configuration files automatically.

To add a new data source, press the Add button and you will see a list of installed drivers (see Figure 12.2).

Figure 12.2. Adding a new data source.

Select one of the drivers and press OK (Note: If you're like me, you'll press the Add button by mistake. If you do that, ODBCConfig will assume that you want to add a new driver.)

After you have selected a driver, you will be asked to define the rest of the connection properties (see Figure 12.3). Remember that each driver understands a different set of connection properties, so the Data Source Properties dialog will look different if you are using a different driver.

Figure 12.3. PostgreSQL Data Source Properties.

You can leave most of these properties set to their default values ”you really need to provide only the Name , Description , and Database properties. (This dialog is a little confusing. Where's the OK button? To accept the changes that you have made, click the check mark in the upper-left corner of the window. To cancel, click the X.)

You can see that using the ODBCConfig utility is much easier than configuring a data source by hand. When you create a new data source using ODBCConfig , the data source properties are stored in the odbc.ini file.

Setting Up a Data Source in Windows

MS Windows also provides a graphical configuration tool, almost identical to ODBCConfig . On most Windows systems, you will find the ODBC administrator in the Control Panel or in the Administrative Tools applet within the Control Panel. Double-click whichever ODBC icon is present on your system, and you should see something similar to what is shown in Figure 12.4.

Figure 12.4. Windows ODBC Data Source Administrator.

The procedure for creating a data source using the Windows ODBC Data Source Administrator is identical to the procedure you would following using the unixODBC Data Source Administrator.