Microsoft Open Database Connectivity (ODBC)

ODBC stands for Open Database Connectivity. It started as a Microsoft-only standard and was adopted as a vendor-independent SQL/CLI standard. There were several iterations of the ODBC interfaces, with the latest being in version 3.0, and it remains a Microsoft product.

ODBC 3.0 aligns itself with the existing X/Open CAE specification, Data Management: SQL Call-Level Interface (CLI), and ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI).

The functions in the ODBC API are implemented in a variety of the ODBC drivers, which can be called by applications through the ODBC Driver Manager, which handles communications between the functions and the drivers.

Both the drivers and the Driver Manager were ported on platforms different from Windows (e.g., Unix, Macintosh). When the driver manager receives a call from an application, it loads the corresponding ODBC driver that in turn forwards the call to the RDBMS interface. When a response is received from the database, the driver forwards it to the Driver Manager, which returns the results of the call to the application. Only the Driver Manager is loaded and unloaded by an application that requests ODBC connection to the database; the rest is the task of the Manager itself. The Driver Manager also performs ODBC functions basic checking (correct parameters, data types, and the like).

The drivers represent the libraries implementing the ODBC functions. Each RDBMS requires its own ODBC driver. You may think of the driver as an interpreter: understanding a common language (ODBC API calls) of the application and translating it into the RDBM-specific terms. Drivers are not supposed to understand, execute, or even translate SQL; those tasks are solely the responsibility of the RDBMS.

The tasks performed by ODBC drivers are the following:

• Establishing and closing connections to data source

• Checking ODBC function calls for errors

• Initiating transactions in a manner apparent to the application

• Passing SQL to DBMS for execution

• Sending data to and retrieving data from the DBMS

• Mapping DBMS vendor-specific errors to ODBC SQLSTATES collection of predefined errors

Configuring an ODBC Data Source Name on Windows

On Microsoft Windows OS, the Driver Manager resides in the SettingsàControl PanelàAdministrative ToolsàData Sources (ODBC) (assuming that you are using Windows 2000; the path might be different if the data source is configured on Windows 9x or Windows XP). It maintains information about all ODBC drivers installed on the system and assists in creating and configuring ODBC Data Source Names (DSN).

Tip 

The only exception to the "speak no SQL" rule are drivers for the DBMS that do not have a stand-alone database engine (Xbase, for instance). In this case, the driver is supposed to process the SQL as well.

The DSN can be used by an application to connect to the RDBMS for which it was configured. The use of ODBC DSN is illustrated later in this chapter, in the paragraph discussing ADO interface.

We will guide you through the process of creating an ODBC Data Source Name (DSN) on a Windows 2000 Professional machine. The DSN will be used later in this chapter in the example in which the Microsoft Visual Basic application connects to IBM DB2 UDB database.

From the Control Panel (StartàSettingsàControl Panel; again, we assume Windows 2000 configuration, the path might different for other flavors of Windows) menu, select the Administrative Tools option and double-click it. From the Administrative Tools, select the Data Sources (ODBC) option and double-click it. This brings up the ODBC Data Source Administrator. Figure 16-1 shows several tabs for the three different types of ODBC DSN that can be created on a Windows machine; they differ primarily in scope — the System DSN has global scope, the User DSN is visible for the user account only, and the File DSN is represented by a standard OS file. Select the User DSN tab.

Figure 16-1: Microsoft ODBC Driver Manager console

The screen will list all the DSN entries created on the machine; from here you can add new DSN or remove and configure existing ones. Click the Add button to add a new DSN entry. The next screen (Figure 16-2), Create New Data Source, presents you with the list of all ODBC drivers installed on the machine. Select the IBM DB2 ODBC driver from the list and click the Finish button.

Figure 16-2: ODBC drivers selection

From this point on, you will be guided by the IBM-specific driver interface, shown in Figure 16-3. This interface is not uniform, as it reflects RDBMS specifics. The Oracle driver will display a different screen and set of options; and the SQL Server driver, yet another.

Figure 16-3: IBM DB2 UDB ODBC driver configuration

A DSN needs a unique name, so choose one and type it in to the Data Source Name field, as shown in Figure 16-3. Since we are creating a Data Source on the same machine where IBM DB2 UDB is installed, the Database Alias field defaults to ACME (you should have the same, if you've followed the instructions for the sample database installation given in Appendix F). Click the OK button.

The final screen (Figure 16-4) shows the User DSN for the IBM DB2 UDB. This DSN can be used by any ODBC-compliant application to establish connection to the DB2 UDB database ACME.

Figure 16-4: New user DSN entry IBM DB2 UDB