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 the PostgreSQL client/server protocol.

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 (or Fedora) 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 either the apt or 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: 0, 1, or 2. FileUsage provides a hint to the client application about how the database stores data in the OS filesystem. 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 0. 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 0 in that a type 0 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).

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).

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.

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.

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.

If you're using a recent version of the PostgreSQL ODBC driver, the data source configuration screens will look a bit different. Figure 12.5 shows the initial configuration dialog.

To fine-tune the data source, click the button labeled Datasource (in the Options group). A dialog similar to the one shown in Figure 12.6 will appear.

I'll explain the configuration options in more detail in the next section. Click Page 2 to get to the second page of data source options, shown in Figure 12.7.

If you click the button labeled Global (from the dialog shown in Figure 12.5), you'll see the dialog shown in Figure 12.8.

Check the first box (labeled CommLog) if you want a complete log of the client/server communication stream. Check the second box (labeled Mylog) to record a detailed log of the internals of the psqlodbc driver. In most cases, you'll never need to record either log.

Datasource Connection Properties

The psqlodbc driver supports a number of connection properties. If you're using psqlodbc on a Windows host, you can configure the driver using a graphical interface (see Figures 12.5, 12.6, 12.7, and 12.8). If you're using psqlodbc on a Linux/Unix host, you'll have to edit the odbc.ini file by hand.

Table 12.1 shows the connection properties supported by psqlodbc version 8.0. In the first column, you see the label that appears in the Windows configuration dialogs (Figures 12.5, 12.6, and 12.7). The second column shows the corresponding name as it must appear in the odbc.ini file. The third column lists the default value for each property.

In the list below, I describe each property:

• Database— The name of the PostgreSQL database that you want to connect to with this data source.

• Server— The name (or IP address) of the server that you want to connect to.

• Username— The dedicated username that this data source will use to connect to the PostgreSQL server—leave this field blank if you want the client application to prompt for a username.

• Description— A descriptive name for the data source (some client applications display the description when you browse through the list of data sources).

• Port— The TCP/IP port number where the postmaster is listening in the PostgreSQL server (you only need to change this if your postmaster is listening on a port other than the usual 5432).

• Password— The password used to connect to the PostgreSQL server—leave this field blank if you want the client application to prompt for a password.

• Optimizer— Disables PostgreSQL's genetic query optimizer (the genetic optimizer is responsible for selecting a reasonably good execution plan when you join many tables in a single query).

• Ksqo— Enables server-side support for keyset queries generated by the Microsoft Jet database engine. Disable this property if you are using a PostgreSQL server newer than version 7.1.

• UniqueIndex— If enabled, the ODBC's SQLStatistics() function will report unique indexes (including a “fake” index on each table's OID column). If Off, the SQLStatistics() function will report that all indexes allow duplicate values. You may want to disable UniqueIndex if you are reading PostgreSQL-hosted data from a Microsoft Access application.

• UseDeclareFetch— If enabled, the driver will automatically use server-side cursors (via DECLARE and FETCH) whenever your application executes a SELECT command. Instead of retrieving the entire result set, the driver will fetch a small number of rows as needed. The number of rows fetched is controlled by the Fetch property.

• Fetch— Determines the number of rows fetched by the driver when UseDeclareFetch is enabled.

• CommLog— If defined, the driver records all client/server traffic in a log file. The log file is named C:psqlodbc_<process-id> on Windows systems and /tmp/psqlodbc _<user-name><process-id>.log on Linux/Unix systems.

• Parse— If enabled, the driver (not the server) will parse simple SELECT statements when you call the SQLNumResultCols(), SQLDescribeCol(), or SQLColAttributes() function. The driver reads through the SELECT statement to identify individual columns and then asks the server to return type information for each column that it found.

• CancelAsFreeStmt— If enabled, the driver's SQLCancel() function will call SQLFreeStmt(SQL_CLOSE) on your behalf. Applications designed around ODBC 2.x may expect the SQLCancel() function to behave the same as SQLFreeStmt(SQL_CLOSE). If you're using such an application, you can enable this property (or better yet, upgrade the application to conform to ODBC 3.x specifications).

• Debug— If enabled, the driver records a detailed record of its work in a log file. The log file is named C:mylog_<process-id> on Windows systems and /tmp/mylog_<user-name><process-id>.log on Linux/Unix systems.

• UnknownSizes— This property determines how the SQLDescribeCol() and SQLColAttributes() functions compute the size of a column of unknown type. You can specify Maximum (0) to force the driver to report the maximum size allowed for the type, Don't Know (1) to force the driver to report an unknown length (-1), or Longest (2) to force the driver to search through the current result set to find the longest value. You should not specify Longest if you have enabled UseDeclareFetch.

  As of psqlodbc version 8.0, the driver has built in support for the following data types: CHAR, NAME, BPCHAR, VARCHAR, TEXT, BYTEA, SMALLINT (INT2), OID, XID, INTEGER (INT or INT4), BIGINT (INT8), NUMERIC (or DECIMAL), REAL (FLOAT or FLOAT4), DOUBLE PRECISION (FLOAT8), DATE, TIME, ABSTIME, TIMESTAMPTZ, TIMETZ, MONEY, BOOL, and large objects. Any other type is considered “unknown.” Arrays are viewed as “unknown” data types as well.

• TextAsLongVarChar— If enabled, the driver treats PostgreSQL TEXT columns as if they are of type SQL_LONGVARCHAR (or SQL_WLONGVARCHAR if you are using the Unicode-enabled driver). If disabled, the driver treats TEXT column as SQL_VARCHAR (or SQL_WVARCHAR) values.

• UnknownAsLongVarChar— If enabled, the driver treats columns of unknown type as SQL_LONGVARCHAR values. If disabled, the driver treats columns of unknown type as SQL_VARCHAR values.

• BoolsAsChar— If enabled, the driver treats PostgreSQL BOOL columns as SQL_CHAR values. If disabled, BOOL columns are treated as SQL_BIT values.

• MaxVarcharSize— The driver treats VARCHAR and BPCHAR values longer than MaxVarCharSize as SQL_LONGVARCHAR (or SQL_WLONGVARCHAR) values.

• MaxLongVarcharSize— If TextAsLongVarChar is enabled, the driver reports that TEXT values are MaxLongVarcharSize bytes long; otherwise TEXT values are reported to be MaxVarcharSize bytes long. Also, if UnknownAsLongVarChar is enabled, columns of unknown type are reported to be MaxLongVarcharSize bytes long, otherwise they are reported to be MaxVarcharSize bytes in length.

• ReadOnly— If defined, the driver prevents you from executing any of the following commands: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, or LOCK. The driver also prohibits procedure calls that use ODBC's procedure call escape syntax {call=procedure-name?}.

• ShowSystemTables— If enabled, the driver will report PostgreSQL system tables when you call the SQLTables() function. The SQLTables() function will also report system tables if you specifically ask for them by specifying a table type of SYSTEM TABLE. The driver considers any table whose name starts with pg_ to be a system table. The driver will also consider any table whose prefix matches one of the strings specified in ExtraSysTablePrefixes (see the next property) to be a system table.

• ExtraSysTablePrefixes— If defined, this property specifies a semicolon-separated list of prefixes. A table whose name starts with one of these prefixes is considered to be a system table (see the previous property).

• LFConversion— If enabled, the driver converts line-feed characters to carriage-return/line-feed pairs when retrieving character values from the server and converts carriage-return/line-feed pairs to line-feed characters when sending character values to the server.

• UpdatableCursors— If enabled, the driver permits positioned UPDATE and DELETE operations using the SQLSetPos() or SQLBulkOperations() function.

• ByteaAsLongVarBinary— If enabled, the driver treats PostgreSQL BYTEA columns as if they are of type SQL_LONGVARBINARY. If disabled, BYTEA columns are treated as SQL_VARBINARY values.

• RowVersioning— If enabled, the driver includes the xmin column when reporting the columns defined for a given table. Well-designed applications can use the xmin column to avoid updating rows that have been modified by another transaction. The SQLSpecialColumns() function treats xmin as an SQL_ROWVER column.

• DisallowPremature— This property determines how the driver behaves if you try to retrieve information about a query (such as the number of columns in the result set or the type of each column) without actually executing the command. If DisallowPremature is enabled, the cursor declares a cursor for the query and fetches the meta-data from the cursor (without actually fetching a row). If DisallowPremature is disabled, the driver executes the command as soon as you request any meta-data.

• UseServerSidePrepare— If enabled, the driver will use the PREPARE and EXECUTE commands to implement the Prepare/Execute model (explained later in this chapter). UseServerSidePrepare is ignored if you connect to a server running PostgreSQL version 7.2 or older.

• BI— This property determines how the driver treats BIGINT (INT8) values. BI can be set to any of the following values: -5 (SQL_BIGINT), 2 (SQL_NUMERIC), 8 (SQL_DOUBLE), 4 (SQL_INTEGER), 12 (SQL_VARCHAR), or 0. If BI is set to 0, the driver treats BIGINT values as SQL_NUMERIC when connected to an MS Jet client and SQL_BIGINT when connected to any other client.

• Protocol— If defined, forces the driver to interact with the server using a specific protocol version. If not defined, the driver negotiates with the server to find a suitable protocol.

• ShowOidColumn— If defined, the SQLColumns() function reports the OID column (if present). If not defined, the OID column is hidden from SQLColumns().

• FakeOidIndex— If defined, the SQLStatistics() function reports that a unique index exists on each OID column.

• ConnSettings— A semicolon-separated list of SQL commands that are executed when the driver first connects to the server. Typically, this property contains a list of SET statements that initialize the server's runtime parameters (CLIENT_ENCODING, DATESTYLE, and so on) to fit your needs.

• Socket— Determines the size of the buffer that the driver uses when communicating with the client. You can increase the buffer size to improve performance if you are shuffling large fields between the client and the server.

• Lie— If defined, the driver (when asked) claims to support a number of ODBC features that it does not in fact support. For example, if Lie is defined, the driver claims to support positioned UPDATE's and DELETE's, dynamic and mixed scrolling within cursors, the SQLBrowseConnect() function, SQLColumnPrivileges(), and more. Presumably, this property exists to satisfy client applications that check for advanced features but don't actually use them.

• LowerCaseIdentifier— If enabled, the driver will translate identifiers (column names, table names, schema names, etc.) to lowercase. If disabled, the driver reports identifiers in the form delivered by the server.