Enterprise OLE Includes OLE DB (Code-named
Nile), Network OLE, OLE Transactions, OLE Team Development, and
OLE Directory Services.
In the client/server database world, Windows NT is steadily gaining favor as a server operating system and Windows
operating systems are the dominant client platforms. Microsoft has added data
access to the feature set of its Windows applications by using technologies that
are available to programmers and end users.
When discussing database client applications, it's easy to use the local
desktop as a frame of reference, but distributed-object technologies will create
situations where objects are everywhere and computers can act as servers in one
context and clients in another. Many pundits see future versions of today's
Windows NT as becoming the desktop operating system of choice for enterprise
applications, so it's a good idea for NT users to understand data-access
solutions, particularly Open Database Connectivity (ODBC), Object Linking and
Embedding (OLE), and Data Access Objects (DAO).
What are ODBC, OLE, and DAO? The short answer is: ODBC is a programming
interface for SQL data access; OLE is a binary standard for object sharing; and
DAO is an object layer that encapsulates data-access services. ODBC is based on
a SQL industry standard; OLE is an open specification from Microsoft; and DAO
has been, until recently, a technology available only with Microsoft developer
products. For the long answer, read on!
Open Database Connectivity
ODBC is a call-level interface (CLI). It lets application programs call
functions exported from dynamic link libraries (DLLs). The first implementation
of ODBC was for Windows, but it's now available for the Macintosh, Power
Macintosh, OS/2, and a variety of UNIX and other operating systems. ODBC uses
SQL for all data access: relational or non-relational, text or spreadsheet.
Microsoft developed ODBC by extending a CLI originally specified by the SQL
Access Group (SAG), a consortium of SQL vendor companies. A revised version of
the SAG specification has been accepted by X/Open, ANSI, and the International
Standards Organization (ISO), prompting Microsoft to update ODBC to align with
the new standard. Microsoft has no interest in seeing ODBC take a different
course. An updated version of the SAG and X/Open CLI, which includes
enhancements for SQL-92, recently became the international standard programming
interface for SQL-92.
Prior to its adoption, the SQL standard included only embedded SQL, which
uses source-code preprocessing and compile-time data binding. A CLI permits
execution-time binding, so it's easier for writing interoperable applications.
ODBC uses an environment handle, connection handles, and statement handles
to track information and resources. An application has a single environment
handle, but it may have multiple active connections and statements. ODBC has
functions for allocating handles, connecting to databases, binding program
variables to columns in tables, executing SQL statements, fetching bound or
unbound data, and returning error information.
ODBC supports transaction processing (TP) with isolation levels,
pessimistic and optimistic locking, and the ability to commit or rollback
database changes. It uses an abstraction known as a data source to
encapsulate the server names, database names, network libraries, directory
paths, or other information necessary to provide a unique path to the data.
One major difference between ODBC and a proprietary application programming
interface (API) (e.g., Oracle Call Interface) is that ODBC works with many
different database management system (DBMS) products by reporting what features,
SQL, data types, and API functions a DBMS and its driver support. (Reporting,
not mandating, is the operative word here.)
Many developers believe incorrectly that because ODBC represents a
standard, they can write applications using a standard set of features and data
types that all the DBMSs support. However, ODBC tells an application what a DBMS
can do, not what it must do.
ODBC uses loadable drivers for each type of database it connects to.
Because the capability of a text-data driver differs from that of a massively
parallel server (e.g., the White Cross 9000), ODBC defines levels of conformance
with SQL grammar and the ODBC API. An application can make execution-time calls
to determine a driver's features (e.g., whether it supports outer joins).
ODBC includes an SQL grammar that uses escape clauses to express
extensions, such as outer joins, scalar functions (providing aggregation, string
processing, phonetic searches, and so on), calling procedures, and dates. Escape
clauses enable you to express a query in a DBMS-neutral syntax. The driver
translates the query to native syntax before it sends the statement to the DBMS.
ODBC 2.0 includes more than 50 functions for SQL data access alone. It also has
functions for installing drivers and configuring data sources. (For information
on the next version of ODBC, see the sidebar "ODBC 3.0" on page 29.)
Direct Access Objects
Microsoft developers in Redmond developed DAO to be an object layer to
simplify programming for applications using Microsoft Access's database engine,
commonly known as Jet. Jet can attach desktop databases (e.g., Paradox)
and SQL servers (e.g., Oracle). When attaching a local desktop database, Jet
uses a keyset-driven cursor model and Indexed Sequential Access Method
(ISAM)-based techniques. When accessing SQL servers, it uses a keyset-driven
cursor model and ODBC.
Figure 1 illustrates the DAO hierarchy of collections and objects. DAO
raises the level of abstraction in database programs by insulating you from
having to manage low-level details. It represents a single means of providing
access to multiple, heterogeneous data sources. You can write one routine using
common collections and objects and use it whether the data is in an Access,
ISAM, or remote SQL database. Microsoft includes DAO in Access, Visual Basic
(VB), and Visual C++, so there have been interim releases to synchronize the
versions across product lines.
Microsoft didn't build Jet or DAO with an OLE infrastructure, so
Visual C++ programmers had to use ODBC and SQL to work on Access databases.
Access and VB users, however, could work directly on ISAM and Access databases.
For Visual C++ 4.0, the company rewrote DAO as an OLE Automation server, opening
the door for use by multiple applications. Microsoft has been using Visual Basic
for Applications (VBA) as a common language across applications so DAO code is
highly portable. This lets Visual C++ developers prototype with VB and Excel
users to reuse code from Access applications.