PART V
Oracle8 and the Web

In This Part

•  ODBC/JDBC

•  Oracle Web Application Server

Chapter 26
ODBC/JDBC

by Scott Wheeler

In This Chapter

•  Overview of ODBC

•  ODBC Architecture

•  ODBC Setup, Usage, and Limitations

•  Oracle ODBC Driver Capabilities and Limitations (V8.0.3)

•  Oracle8 ODBC Optimization and Troubleshooting

•  Last Words on ODBC

•  Overview of JDBC

•  Oracle’s JDBC Evolution

•  JDBC Setup, Usage, and Limitations

•  Oracle-Specific Functions

•  Last Words on JDBC

Overview of ODBC

JDBC and ODBC are similar in their purpose and structure in that they exist to allow easy access of data from programs via a common programming interface. In the following chapter you explore the pros, cons, capabilities, and limitations of both ODBC and JDBC when accessing Oracle8.

To effectively use any software product, it is important to understand the general structure and purpose of its creation. The following section will introduce the purpose of ODBC and offer a brief synopsis of its evolution.

ODBC’s Purpose

Open Database Connectivity (ODBC) is the industry-accepted standard Application Programming Interface (API) that enables programs to access data in many different formats and locations, which includes most database management systems as well as some of the most popular file formats such as Microsoft Excel. ODBC uses Structured Query Language (SQL) to access the data, regardless of what format the data is in or where the data is located. The ability to use one standard, SQL, to access many different sources of data gives a developer a great degree of database independence and code independence.

ODBC’s Evolution

The first ODBC version 1.0 desktop database drivers were released in August 1993 due to a need for a common data access method to use with Microsoft Windows–based applications, which were gaining tremendous popularity at the time. The drivers enabled Microsoft Windows developers to provide database access to their programs, through a relatively simple, standard interface. ODBC was a significant factor in the revolutionary effect client/server computing had on corporate culture in the ’90s in addition to helping establish Microsoft Windows as the operating system of choice for client/server computing.

Due to the popularity of ODBC versions 1.0, version 2.0, 3.0, and 3.5 were released in December 1994, October 1995, and October 1996, respectively. In each release, enhancements were focused around speed, new operating system compatibility (Windows 95 and NT), and providing access to functionality present in some of the more advanced database systems ODBC accessed (for example, cursor management, double-byte character sets, and so on).

RDO, DAO, OLE DB, ADO, and Other Acronyms

RDO, OLE DB, and ADO are an evolution by Microsoft to address the current issues and anticipated future needs of developers. The following excerpts describe each of the components and their relevance to data access, ODBC, and Microsoft’s strategy going forward.

RDO (Remote Data Objects) was important in that it allowed Microsoft’s Visual Basic 4 and 5 to access ODBC-enabled data sources without going through the “jet engine” that existed in most Microsoft development tools at that time. The result was much better performance for Visual Basic application that used RDO rather than the traditional ODBC approach.

In contrast, DAO (Data Access Objects) utilizes OLE DB to access the data via ODBC and the Microsoft jet engine. Additionally, OLE DB is a C++ API that represents the fundamental component for data access in Microsoft’s current Component Object Model (COM) strategy. It is meant to be more robust and handle “future” data sources, including, not replacing, ODBC. Currently, OLE DB does not fundamentally change the use of ODBC driver in access Oracle (or other) data sources.

ADO (ActiveX Data Objects) is Microsoft’s current target object model. It is different than DAO and RDO in that you no longer have to navigate through the complexities of Microsoft’s object hierarchy, but it does utilize OLE DB as its data access method. Microsoft has also stated that all its products will standardize on ADO as the common object model for development.

Oracle’s ODBC Evolution

Although ODBC drivers for Oracle were available from various companies as early as 1994, Oracle made ODBC drivers widely available to its customers in early 1995. Additionally, Oracle enabled its development tools to use ODBC to access data sources other than Oracle during 1995. Starting in 1996, Oracle made all of its ODBC drivers available for download from the Web at no cost and continues to do so.

To get the most recent version of Oracle’s ODBC drivers, Oracle’s Web site continues to be the best source of free ODBC drivers, although there are a few vendors (Simba, Intersolve, and so on) who still make high quality ODBC drivers for Oracle.

ODBC Architecture

Although there a several components involved in connecting to a data source via ODBC, ODBC itself is made up of two major components: the ODBC application (Driver Manager) and the database driver. Both of these components are written to a common application and data driver ODBC API specification. The ODBC API specification identifies the functions that should be supported in the specific ODBC version. The current ODBC version is version 3, and within this version the functions are broken into the following categories: Core, Level1, and Level2. The Oracle ODBC drivers currently support all but a few functions defined in the version 3.0 specification. (The exceptions are identified later in this document in the Oracle8 optimization section.)

A visual representation of the components involved in the submission of a SQL request through ODBC can be found in Figure 26.1.

Figure 26.1.  SQL request through ODBC.

---

NOTE:  
ODBC and Net8 (formerly SQL*Net) are both required on every client that uses ODBC to access Oracle data. Additionally, the Net8 drivers (Oracle DBMS Driver) for each version of the Oracle database you plan to connect to must exist on each client machine that accesses data through ODBC, although the backward compatibility of the database drivers may allow you to access most of Oracle’s functionality. The Net8 drivers are referred to as Required Support Files in the Oracle Installer program. The installation and configuration of the ODBC drivers will be explained in the following section.

---