SQL Bible [Electronic resources] نسخه متنی

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

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