1311-1315

Previous Table of Contents Next

Page 1311

CHAPTER 57

ODBC

IN THIS CHAPTER

• Components of ODBC 1312
• Configuring an ODBC Data Source 1313
• Connecting to an ODBC Data Source Using the ODBC API 1317
• Setting Connection Options 1320
• Applying SQL Transactions 1320
• Retrieving Result Sets 1324
• Handling Errors 1328
• Calling Stored Procedures and Functions 1330
• Disconnecting and Freeing Resources 1332
• Debugging ODBC Applications 1333
• Limitations of ODBC 1334

Page 1312

ODBC (Open Database Connectivity) is an industry standard programming interface that enables applications to access a variety of database management systems residing on many different platforms. ODBC provides a large degree of database independence through a standard SQL syntax, which can be translated by database-specific drivers to the native SQL of the DBMS.

Database independence and ease of use are the primary advantages to using ODBC. It is supported by many popular development tools, including Visual Basic, PowerBuilder, Delphi, and SQLWindows. These tools and numerous others provide their own interfaces to ODBC, making ODBC easier to use by insulating the developer from many of the complexities of the ODBC API.

Components of ODBC

ODBC software is made up of several distinct components. The Application layer contains embedded SQL and logic for data entry, preparing transactions, and displaying result sets. It calls API functions exported by the driver manager to connect to the data source, apply SQL, and retrieve results and error codes. The driver manager provides the common ODBC interface, loads database-specific drivers as requested by the application, performs call-level validations, and maps ODBC calls to functions exported by the database-specific driver. The database-specific driver processes the ODBC function calls, optionally converting SQL and data types to the native syntax of the DBMS, and formats DBMS error codes into a standard format. It also returns result sets and error codes to the driver manager. The data source consists of the DBMS, in addition to any network or operating system software required to connect to it. Figure 57.1 illustrates these layers .

Figure 57.1.

This is a visual
representation of the
components of ODBC.

For local desktop databases, the data source might simply consist of the name of the local server or database file. When the DBMS resides on a remote server, however, the data source includes any network software required to access the remote host. If, for example, you attempt to access

Page 1313

Oracle on a remote server, SQL*Net must be installed and properly configured. Although this software is not actually part of ODBC, it is considered part of the data source because it is required by ODBC to connect to the database.

Configuring an ODBC Data Source

The process of configuring an ODBC data source is simply a matter of providing some information to the driver manager and the DBMS-specific driver. The driver manager uses entries in ODBC.INI to determine what driver to load for a particular data source name. The specific driver may use ODBC.INI to determine the server name and the values of any database- specific parameters required to connect.

The ODBC administration program ships with nearly all Windows development tools that support ODBC and is typically installed as part of the Windows Control Panel applet. This program, ODBCADM.EXE, and a DLL, ODBCINST.DLL, are used to install specific drivers and configure data sources. The following instructions on how to use the driver manager are based on version 1.02 of ODBCADM.EXE. This application may vary slightly from version to version, but the functions provided are essentially the same.

CAUTION

Always use the ODBC administration program to install drivers and configure data sources. ODBC.INI and ODBCINST.INI should not be edited manually, unless it becomes absolutely necessary because of corruption or other extreme problems. If this situation arises, the files should be backed up prior to editing.

When the administration application is started, a list of defined data sources is presented, as shown in Figure 57.2.

Figure 57.2.

The Data Sources
dialog displays a list of
defined data sources.

The Close and Help buttons should be self-explanatory, and the Options button is discussed later in the section on debugging. Add is used to define a new data source for one of the installed drivers. Delete is used to delete an existing data source, but it does not delete the driver. The configuration of the selected data source can be edited by clicking on the Setup button. The Drivers button is used to install additional DBMS-specific ODBC drivers.

Page 1314

The following examples illustrate the installation of the Oracle ODBC driver and the configuration of an Oracle data source. This process begins with the installation of the Oracle ODBC driver. First, select Drivers from the Data Sources dialog box. When this button is clicked, all installed drivers are displayed, as shown in Figure 57.3.

Figure 57.3.

The Drivers dialog box
displays a list of
installed drivers.

To install the Oracle ODBC driver, click the Add button. This displays a dialog requesting the location of the drivers. Select the drive and directory containing the ODBC.INF file and click OK. The dialog box shown in Figure 57.4 indicates that the Oracle ODBC driver was located.

Figure 57.4.

The Install Drivers
dialog box displays a list
of drivers available for
installation.

The Advanced button displays a dialog that allows the user to specify installation of the driver manager and code page translators. The Versions button in the lower-right corner brings up a second dialog that can be used to view extended version information about each component available to install. These two dialog boxes are shown in Figure 57.5.

Figure 57.5.

The Advanced
Installation Options
and Versions dialog
boxes can be used to
control the installation
of the Oracle ODBC
driver.

Page 1315

The Install selected driver(s) with version checking should be selected. This causes the installation program to prompt before overwriting an existing driver if it is the same or a newer version of the driver being installed.

The version checking options should also be used for the installation of the Driver Manager and translators. Newer versions of these DLLs should not be overwritten if they exist.

Code page translators are used to translate between different character sets and languages. In some cases, they are used for encryption or data type conversion. Although a translator is not needed in most cases, you can install them for possible future use. After making your selections from these options, click the OK button to return to the Install Drivers dialog.

To complete the installation of the Oracle7 driver, make sure that Oracle7 is highlighted in the list box and click the OK button. The Drivers dialog box should now appear as shown in Figure 57.6.

Figure 57.6.

The Drivers dialog box
shows that the Oracle
ODBC driver was
successfully installed.

Click the Close button to complete driver installation.

Configuring the data source is simply a matter of specifying a driver, naming the data source, and providing some additional information for the driver to use when connecting to the database. Refer to the Data Sources dialog box in Figure 57.2. From this dialog box, click Add to configure a new Oracle data source. The next dialog box, Add Data Sources, requires the selection of an installed driver. Select Oracle7 from the list and click the OK button. Next, the Oracle7 ODBC Setup dialog box prompts the user for a data source name, description, and SQL*Net connect string, as illustrated in Figure 57.7.

Figure 57.7.

This is the Oracle7
ODBC Setup
dialog box.

The connect string is specific to the network transport (if any), the host name or address of the server, and the system ID of the database to be accessed (if more than one Oracle database exists on the host). The syntax of the connect string is

 transport_code:host_name:database 

Previous Table of Contents Next