The Future of Data Access: ODBC, DAO, and OLE DB

Enterprise OLE Includes OLE DB (Code-namedNile), Network OLE, OLE Transactions, OLE Team Development, andOLE Directory Services.

In the client/server database world, Windows NT is steadily gaining favor as a server operating system and Windowsoperating systems are the dominant client platforms. Microsoft has added dataaccess to the feature set of its Windows applications by using technologies thatare available to programmers and end users.

When discussing database client applications, it's easy to use the localdesktop as a frame of reference, but distributed-object technologies will createsituations where objects are everywhere and computers can act as servers in onecontext and clients in another. Many pundits see future versions of today'sWindows NT as becoming the desktop operating system of choice for enterpriseapplications, so it's a good idea for NT users to understand data-accesssolutions, particularly Open Database Connectivity (ODBC), Object Linking andEmbedding (OLE), and Data Access Objects (DAO).

What are ODBC, OLE, and DAO? The short answer is: ODBC is a programminginterface for SQL data access; OLE is a binary standard for object sharing; andDAO is an object layer that encapsulates data-access services. ODBC is based ona SQL industry standard; OLE is an open specification from Microsoft; and DAOhas been, until recently, a technology available only with Microsoft developerproducts. For the long answer, read on!

Open Database Connectivity
ODBC is a call-level interface (CLI). It lets application programs callfunctions exported from dynamic link libraries (DLLs). The first implementationof ODBC was for Windows, but it's now available for the Macintosh, PowerMacintosh, OS/2, and a variety of UNIX and other operating systems. ODBC usesSQL for all data access: relational or non-relational, text or spreadsheet.

Microsoft developed ODBC by extending a CLI originally specified by the SQLAccess Group (SAG), a consortium of SQL vendor companies. A revised version ofthe SAG specification has been accepted by X/Open, ANSI, and the InternationalStandards Organization (ISO), prompting Microsoft to update ODBC to align withthe new standard. Microsoft has no interest in seeing ODBC take a differentcourse. An updated version of the SAG and X/Open CLI, which includesenhancements for SQL-92, recently became the international standard programminginterface for SQL-92.

Prior to its adoption, the SQL standard included only embedded SQL, whichuses source-code preprocessing and compile-time data binding. A CLI permitsexecution-time binding, so it's easier for writing interoperable applications.

ODBC uses an environment handle, connection handles, and statement handlesto track information and resources. An application has a single environmenthandle, but it may have multiple active connections and statements. ODBC hasfunctions for allocating handles, connecting to databases, binding programvariables to columns in tables, executing SQL statements, fetching bound orunbound data, and returning error information.

ODBC supports transaction processing (TP) with isolation levels,pessimistic and optimistic locking, and the ability to commit or rollbackdatabase changes. It uses an abstraction known as a data source toencapsulate the server names, database names, network libraries, directorypaths, or other information necessary to provide a unique path to the data.

One major difference between ODBC and a proprietary application programminginterface (API) (e.g., Oracle Call Interface) is that ODBC works with manydifferent 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 astandard, they can write applications using a standard set of features and datatypes that all the DBMSs support. However, ODBC tells an application what a DBMScan 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 massivelyparallel server (e.g., the White Cross 9000), ODBC defines levels of conformancewith SQL grammar and the ODBC API. An application can make execution-time callsto determine a driver's features (e.g., whether it supports outer joins).

ODBC includes an SQL grammar that uses escape clauses to expressextensions, such as outer joins, scalar functions (providing aggregation, stringprocessing, phonetic searches, and so on), calling procedures, and dates. Escapeclauses enable you to express a query in a DBMS-neutral syntax. The drivertranslates 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 hasfunctions for installing drivers and configuring data sources. (For informationon 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 tosimplify 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, Jetuses a keyset-driven cursor model and Indexed Sequential Access Method(ISAM)-based techniques. When accessing SQL servers, it uses a keyset-drivencursor model and ODBC.

Figure 1 illustrates the DAO hierarchy of collections and objects. DAOraises the level of abstraction in database programs by insulating you fromhaving to manage low-level details. It represents a single means of providingaccess to multiple, heterogeneous data sources. You can write one routine usingcommon 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 theversions across product lines.

Microsoft didn't build Jet or DAO with an OLE infrastructure, soVisual 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, openingthe door for use by multiple applications. Microsoft has been using Visual Basicfor Applications (VBA) as a common language across applications so DAO code ishighly portable. This lets Visual C++ developers prototype with VB and Excelusers to reuse code from Access applications.

Object Linking and Embedding
OLE is a binary standard for object sharing. If you've used PowerPoint tocreate a presentation with a linked or embedded Word table, then you've seenOLE. PowerPoint is a container that uses OLE services to link or embed objects.

OLE is an architecture that layers services over the Component ObjectModel. COM defines certain functionality and exposes interfaces for use byapplications and OLE's service layers. COM objects encapsulate data with methodsthat operate on the object's data and expose interfaces to enable applicationsto use objects. (Personally, I'd prefer to say an object publishes aninterface, but I'll stick with Microsoft's term.)

Microsoft is augmenting COM by adding a capability known as transparentremoting. It enables a COM object to reside on a remote computer but appearto an application as if it were local. OLE uses Remote Procedure Calls (RPCs) tosupport marshaling, a process that provides the "under the covers"processing necessary to make objects appear to be of a similar word size andaddress space.

OLE Automation enables software modules known as automation serversto let clients set properties or call methods with optional arguments. Microsoftsees this capability as being vital to client/server database applicationsusing a three-tier architecture.

The three-tier architecture separates the logic of an application intopresentation services, such as GUIs, business-rules processing, and data access.Microsoft suggests using OLE to create the tier that encapsulates business logicand rules, partly because business objects based on OLE's COM are interoperableacross languages and applications. VB 4.0 Enterprise Edition provides OLE-basedRemote Automation Objects for that purpose.

In May of 1995, Microsoft unveiled a component-based strategy forenterprise-application development that consists of a variety of OLE-based tech-
nologies.Future versions of software (e.g., Microsoft SQL Server), applications (e.g.,Excel), and developer tools (e.g., Visual FoxPro) will be built using an OLEcomponent-based architecture.

Enterprise OLE includes complementary technologies that incorporatedata-integration software called OLE DB--code-named Nile. The other enterprisetechnologies include Network OLE (remote object access), OLE Transactions (TP),OLE Team Development (repository), and OLE Directory Services (DS).

Microsoft is aware of industry expectations for robust and securedistributed processing. Network OLE uses either Microsoft's implementation ofRPCs or Distributed Computing Environment (DCE)-compliant RPCs to access remoteobjects. In addition, updates to OLE to provide distributed COM have adistinctly DCE flavor, using Kerberos security and DCE naming services.

As part of its enterprise strategy, Microsoft has also entered intoagreements with Digital Equipment and Software AG to port OLE's non-GUI servicelayers (OLE Integration Services) to other operating systems. Enterprisemarketeers at Microsoft forecast that OLE will eventually be available for allmajor operating systems. Multiplatform OLE and distributed COM mean thatapplications will be able to use objects on local and remote machines withouthaving to worry about such things as operating system and word size.

For interoperable object technologies, it's clear that OLE is the defacto standard for Windows environments while the Object ManagementGroup's (OMG's) Common Object Request Broker Architecture (CORBA) is dominant inthe UNIX world. The fundamental purpose of CORBA and OLE is the same, but thereare differences in areas such as interface definition languages (IDLs) andinheritance.

At one time, Microsoft was working with Digital to develop a Common ObjectModel that would be interoperable between CORBA and OLE. More recently, however,Microsoft has chosen to focus on OLE, leaving CORBA interoperability tothird-party vendors. If you need UNIX connectivity today, you can look atproducts that provide some measure of interoperability (e.g., Iona's Orbix,Digital's Object Broker, and Visual Edge's Object Bridge).

A Glimpse of the Nile
ODBC drivers provide access to diverse data sources using an SQL-baseddata-access approach, but OLE DB expands that approach. It also moves you to aprogramming model that is closer to the one you will use with Cairo. OLE DB willexpose the metadata in Cairo's Object File System (OFS) as objects.

Microsoft also sees OLE as a solution for multi-tier client/serverapplications that use remoteable OLE objects to encapsulate business processes.It will let you define cross-component business rules and create objects thatexport an event model so operations (e.g., updating a spreadsheet) will be ableto fire events and make other updates. This capability, which is conceptuallysimilar to triggers in SQL database, is useful in maintaining data integrity.

OLE DB provides data access while operating at the object level. It raisesthe level of abstraction for those who traditionally use library calls to accessISAM, flat-file, or relational databases. ODBC provides that type of CLI to aDBMS, but OLE offers the opportunity to operate at a lower level, usingcomponent interfaces and an event model. Microsoft anticipates that theinterfaces between DBMS products (e.g., Microsoft SQL Server) will be open andin components.

In other words, you will be able to access the constituent components of aSQL DBMS (e.g., a parser). Relational vendors will be able to implement acomponent-based OLE interface into their DBMSs by implementing Nile. OLE DB willbe able to use ODBC to access relational databases, and Microsoft willeventually supplement the ODBC Driver Manager with an OLE DB Manager. The newmanager, code-named Kagera, will be an OLE DB data provider for ODBC. It will beable to use existing ODBC drivers (see Figure 2) as well as new data providerswritten to the Nile API.

If you use ODBC, DAO, and OLE, then you will find familiar terms in Nile'slexicon. A data source, bookmark, schema view, index, table, view, and rowsetobject have meanings consistent with those in ODBC and DAO, although terminologyand implementation details may differ. OLE DB includes interfaces that arefamiliar to OLE developers. IPersistStream and IPersistFile save apersistent object, and IUnknown, the base interface of all COM objects, providesruntime dispatching.

OLE supports transactions, ODBC supports transactions, and Nile providescoordinated transaction interfaces that operate with OLE Transactions.Programming with Nile rowsets will involve operations that are conceptuallysimilar to programming with ODBC cursors and DAO recordsets. Rowset operationswill still support bound retrieval, scrollable next and previous operations,transaction isolation, and locking modes. Their behavior will depend uponruntime properties that you can set.

SQL commands drive ODBC, so it's no great surprise that Nile provides acommand interface to let you extract data from data sources. ODBC doesn'tsupport a standard mechanism for reporting an access plan (the feedback from aDBMS's query optimizer).

Nile provides a mechanism for getting command cost information. It can passcost information in a manner similar to Jet's SQL passthrough feature (queriesare passed to the data source without being processed by the Access Engine). ADBMS query analyzer parses a query statement and creates a structure known as aparse tree. Nile enables you to pass query subtrees to data providers.

Command-interface features include operators and data structures to supportdata definition, data manipulation, comparison operators, and scalar functions.Nile will probably support all the scalar functions in SQL-92 and ODBC 2.0(which collectively represent the functions likely to be in ODBC 3.0). Nile alsoprovides data definition and data manipulation of SQL objects (including thosein SQL-92 and SQL3).

What's the Point?
What is the promise of Enterprise OLE, OLE DB, and component-levelinterfaces? Consider a SQL query that uses heterogeneous data sources.Heterogeneous joins are difficult because they often involve bringing rows fromthe server to the client to do a local join. A component-level architecturewould allow bit-mapped optimization of queries as long as the servers providelow-level access to those representations.

Clone objects enable you to introduce redundancy for high-reliability,high-availability systems. The Service Control Manager, proxies, and stubsprovide the infrastructure to route to alternative objects.

For OLE DB to provide an interface to a virtual database, Microsoft mustmeet several challenges. Eventually, the component interfaces must expose asmuch functionality as APIs do, and the connections among components must performas well as the code generated by an API.

Using current relational database technology and TP, it's possible to blockapplication behaviors that compromise data integrity. Because OLE DB and OLETransactions will have some data sources that lack comparable features, ensuringthat data integrity is maintained will be a design challenge.

Network software supports adaptive and least-cost routing, and SQLdatabases can calculate a query's optimal access plan. Enterprise OLE will alsoaddress load balancing. However, Microsoft may find that developers want a costmodel that includes both data-access and remote-access costs. Exposing this costinformation to an object's user won't be a trivial task.

In the final analysis, only you can decide whether it makes sense for yourcompany to adopt an ODBC-only strategy and ignore OLE DB. But in your company'sdeliberations, you should consider that Microsoft currently provides ODBCdrivers for SQL Server, Access, FoxPro, and Excel. However, in the future,Microsoft will also provide OLE interfaces to integrate data from Project,Exchange Server, Word, and other sources.