Inheritance

This code is used to include related rows down a class hierarchy:

SELECT * from ALL vehicle;
/*  all vehicles  */

SELECT * from ALL car;
/*  everything from car downward by class */

Aggregation

These commands will retrieve components of aggregate objects:

SELECT reportCard.grade
WHERE
    report_card.class = 'C++';

At this time, these extensions are only implemented in the object/relational databases such as Sybase/Illustra and UniSQL. However, they will become a reality in 1997 with the widespread acceptance of the SQL3 standard, as well as the commitment of every major relational database vendor to add object extensions to their engines.

Using ODBC As A Server Interface

The Open Database Connectivity (ODBC) product was initially developed by Microsoft as a generic database driver. Its architecture has now been generalized and many different vendors are offering open database connectivity products that are based on ODBC. ODBC is the predominant common-interface approach to database connectivity and is a part of Microsoft’s Windows Open Service Architecture (WOSA). ODBC and WOSA define a standard set of data access services that can be used by a variety of other products when interfacing with MS-Windows applications.

ODBC consists of more than 50 functions that are invoked from an application using a call-level API. The ODBC API does not communicate with a database directly. Instead, it serves as a link between the application and a generic interface routine. The interface routine, in turn, communicates with the database drivers via a Service Provider Interface (SPI), as illustrated in Figure 4.4.

Figure 4.4  A look at the ODBC architecture.

Each custom application within Windows will have call-level API calls to the ODBC database driver, which then directs the request to the appropriate database driver for execution. The database driver manages the communication between the databases and handles all returning data and messages, passing them back to the ODBC driver—which passes them back to the invoking application.

As ODBC becomes more popular, database vendors are creating new ODBC drivers that will allow ODBC to be used as a gateway into their database products. It needs to be noted that most programmers are successful with ODBC in a simple application, but effective use of ODBC in multidatabase environments is a very difficult task. Programmers are not only faced with all the different dialects of SQL, they must also be aware of the native API to the database engines. However, despite the steep learning curve, a few tips can ease the effort with ODBC.

Essentially, ODBC serves as the “traffic cop” for all data within the client/server system. When a client requests a service from a database, ODBC receives the request and manages the connection to the target database. ODBC manages all of the database drivers, checking all of the status information as it arrives from the database drivers.

It is noteworthy that the database drivers should be able to handle more than just SQL. Many databases have a native API that requires ODBC to map the request into a library of functions—for example, an SQL-Server driver that maps ODBC functions to database library function calls. Databases without a native API (i.e., non-SQL databases) can also be used with ODBC, but they go through a much greater transformation than the native API calls.

When accessing multiple databases with ODBC, the API programmer has to manage the multiple database connections and the multiple SQL requests that are being directed to the connections. In ODBC, “handles” are used to point to each database connection. Handles are usually pointers into the database, and the value of the handle is a record key, a row ID, or an object ID.

Most people associate ODBC with SQL. While SQL is now the single most common access method for databases, many important non-SQL databases are widely used. Popular non-SQL databases include IMS, CA-IDMS, Basis Plus—and almost all of the new object-oriented databases. It is a misconception that a database that does not support SQL cannot use ODBC.

Summary

Now that we understand how individual SQL queries can be tuned to make efficient use of the database resources, we can move on to look at the other considerations for effective client/server implementation. Among the topics covered will be lock management, database connectivity issues, and tuning a distributed server environment.