Database access middleware provides a stylized connection between an application computer program and a database. By stylized connection, we mean a link having a formal, published definition. This definition identifies the interface that application programs must use to issue SQL and receive database content through the link. In the case of ODBC and JDBC, the definitions are common to all vendors because ODBC and JDBC are industry-standard database access middleware. ODBC and JDBC are important because they are available on many disparate platforms and they provide common interfaces to several different database products.

Primarily, database access middleware shuttles SQL and database content to and from application programs. In addition, it helps ensure security, and it insulates the application from having to deal directly with the database server. Generalized query tools can interface with database access middleware (rather than the database server itself) to provide query services for multiple types of database products. Computer programs written in Visual Basic, C, C++, Pascal, COBOL and many other languages can perform database operations via ODBC. Similarly, programs written in Java can use JDBC to perform database operations. Programs using ODBC or JDBC can run directly on the database server computer, but, more typically, they run on client computers networked to a database server.

Vendor support for ODBC is pervasive. JDBC support isn't quite at the level of ODBC support, but JDBC is growing and flourishing. Database product vendors and several third-party software houses offer ODBC and JDBC drivers for a variety of databases and operating environments. The Appendix contains a list of vendors for ODBC and JDBC middleware.

The following discussion material covers the basics of database access middleware and provides specific information about ODBC and JDBC. You'll learn how the redirection of SQL to and from a database server works; explore different types of data access; gain an understanding of ODBC's goals; learn the considerations for using ODBC in your organization; and find out how to use ODBC effectively. In addition, you'll read about JDBC's goals; explore the considerations for choosing JDBC; delve into the four different JDBC driver types; and learn how to use JDBC effectively.

The SQL standard identifies the statements that application programs can use to store or retrieve database content, but it doesn't specify the steps involved in connecting to the database, how to deliver the statements to the database server, or how to transfer database content to and from the database. Before the advent of ODBC, computer programs used the delivery mechanism supplied by the database product vendor-a mechanism proprietary to the vendor and always quite different from the delivery mechanisms of other vendors. With ODBC and JDBC, applications have a database-neutral means of delivering SQL to the database server. Whether via a proprietary or standard interface, database access middleware's basic approach to providing a pipeline between a client and the database server is the same. And it works in a fairly simple manner.

Delivery mechanisms for both SQL requests and database content replies, when used in a networked environment, are part of the network protocol stack. Application programs connect to the database, issue SQL, and retrieve results by interacting with the database driver software within the network protocol stack running on each client machine.

The delivery mechanism-the database access middleware-accepts the SQL from the application program. As it accepts the SQL, the delivery mechanism presents a database server interface to the application, just as if the application were running on the database server computer and dealing directly with the database software. However, the delivery mechanism acts only as an agent, transmitting the SQL to the actual database server and receiving the replies. For each reply, the delivery mechanism again presents a database server interface to the application software as it delivers the results to the application. When a software agent intercepts information by pretending to be the recipient and sends the information over the network to be processed by a server, the agent is using redirection.

On the database server, programs listening for SQL request messages from client machines turn each received request message back into a SQL database call, invoke the database server software to process the SQL, and return the database software's result through the network to the client. Thus supplied with database content, the application continues its processing.

Turn for a moment from the inner workings of database access middleware to the administrator's perspective. The database access middleware running at each client computer must be configured into the protocol stack by an administrator, and typically all clients should run the same version of the middleware. Once the administrator has installed the database vendor's software (Oracle Corp.'s, for instance) on a server computer, he or she then visits each client computer to install SQL delivery drivers on those clients. Thereafter, each time the network gains or loses a client machine, the administrator performs maintenance tasks to install or remove driver software. New releases of driver software from the database vendor call for distributing the driver software to each client in a planned, scheduled update effort. Vendor claims of quality and bug fixes notwithstanding, an administrator always makes sure the organization tests the new release of driver software before replacing the old drivers with the new ones.

Different Types of Database Access

Application designers have used many different approaches to connect client computers to database servers. These approaches include a custom-written (home-grown) design, database vendor (proprietary) database access drivers, ODBC, JDBC and object-oriented technologies that let application programs forego the issuing of SQL to treat database content as persistent objects that do not require explicit retrieval or storage.

The most rudimentary and labor-intensive approach is a home-grown one that completely avoids the use of ODBC, JDBC or other commercially-available database access middleware. Using this approach means client machines need a protocol stack containing a transport layer and nothing else. The in-house programming staff develops both client and server software for transferring requests and responses over the network. Application programs send and receive network messages via a transport layer (TCP/IP, IPX, SNA or NetBEUI) protocol, using the programming interface (perhaps WinSock or NetBIOS) appropriate for that protocol. An application system designer specifies the layout and meaning of each different network message, and all the message specifications together form a dialog. Within the framework of the dialog, a client application sends network messages to the server-side application. The server component performs work on behalf of the client and, as spelled out by the specification, returns a response to the client. Applications that follow this approach usually run a bit faster and consume less memory because they work directly with the network-and avoid using general-purpose middleware. On the other hand, though simply sending and receiving messages via a transport layer such as TCP/IP isn't particularly difficult, the design of the dialog and the extra work to incorporate the send/receive operations into the application are tedious and error-prone. Network administrators generally favor the home-grown approach because it entails less work distributing driver files and setting client configurations.

In a more typical approach, application programs use the database vendor's call-level interface to issue SQL and retrieve database content, and the vendor-supplied database access middleware shunts the SQL and database content to and from the database server. As an example, Oracle's call-level interface is its Oracle Call Interface (OCI), while its best-known database access middleware delivery mechanism is SQL*Net. Other popular vendor interfaces and associated database access middleware products are Open Client for Sybase, I-Net for Informix Software, Ingres Net for CA-OpenIngres, Progress Client Networking for PROGRESS, DBLibrary for Microsoft Corp.'s SQL Server, and DDCS for IBM Corp.'s DB2.

Database access APIs that did not (or have not yet) become a standard include IBM's Distributed Relational Database Architecture (DRDA), Microsoft's Data Access Objects (DAO) and Remote Data Access (RDA), and Oracle's Objects for OLE (formerly Oracle Glue). DAO and RDA are high-level, object-oriented APIs that transform requests and responses into ODBC calls. Oracle Objects for OLE is a middleware product that allows native access to Oracle7 and Oracle8 databases from client applications via the Microsoft OLE standard. It consists of three components: an OLE Automation (InProcess) Server, which supplies an OLE Automation interface to applications that support OLE automation scripting, such as Visual Basic; an Oracle Data Control, implemented as a VBX (Visual Basic custom control); and two C++ Class Libraries, one for Microsoft Foundation Classes (MFC) and another for Borland International's OWL. Oracle Objects for OLE is a layer of middleware the application interfaces with and, in turn, uses SQL*Net to communicate with the database server.

Sometimes the best way to concurrently connect a variety of clients to Oracle, Sybase, SQL Server, and DB2 databases running on different servers, without loading multiple drivers, is a generic database access middleware driver (termed by some a data access tool). A subset of middleware designed to overcome ODBC's limitations in multiple database environments, data access tools distribute queries to different databases and provide additional APIs besides just ODBC. Two such tools are OpenLink Software's Data Access Driver Suite and ISG's (International Software Group's) Navigator.

With object-oriented databases, such as Computer Associates International's Jasmine, each database entity is simply a program object whose lifetime persists beyond that of running of the application. In other words, Jasmine-based computer programs can operate directly on the database content without issuing SQL to retrieve or store that content. Jasmine achieves this by closely following the current ODMG (Object Database Management Group) object model, ODMG-93, which proposes a direct relationship between objects within computer programs and objects in a database. The result is a reduction in data-type mismatch as programs retrieve, manipulate, and store database content. Mismatch is the extra work a program does to convert database entries to and from its own format (dates, for example). This extra work can sometimes add 30 percent to 40 percent to the bulk of a program. The reduction in application size and programming effort comes at the expense of loading a slightly larger than usual database access middleware driver that Computer Associates supplies with Jasmine.