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.
Object Linking and Embedding
OLE is a binary standard for object sharing. If you've used PowerPoint to
create a presentation with a linked or embedded Word table, then you've seen
OLE. PowerPoint is a container that uses OLE services to link or embed objects.
OLE is an architecture that layers services over the Component Object Model. COM defines certain functionality and exposes interfaces for use by applications and OLE's service layers. COM objects encapsulate data with methods that operate on the object's data and expose interfaces to enable applications to use objects. (Personally, I'd prefer to say an object publishes an interface, but I'll stick with Microsoft's term.)
Microsoft is augmenting COM by adding a capability known as transparent remoting. It enables a COM object to reside on a remote computer but appear to an application as if it were local. OLE uses Remote Procedure Calls (RPCs) to support marshaling, a process that provides the "under the covers" processing necessary to make objects appear to be of a similar word size and address space.
OLE Automation enables software modules known as automation servers to let clients set properties or call methods with optional arguments. Microsoft sees this capability as being vital to client/server database applications using a three-tier architecture.
The three-tier architecture separates the logic of an application into presentation services, such as GUIs, business-rules processing, and data access. Microsoft suggests using OLE to create the tier that encapsulates business logic and rules, partly because business objects based on OLE's COM are interoperable across languages and applications. VB 4.0 Enterprise Edition provides OLE-based Remote Automation Objects for that purpose.
In May of 1995, Microsoft unveiled a component-based strategy for
enterprise-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 OLE
component-based architecture.
Enterprise OLE includes complementary technologies that incorporate data-integration software called OLE DB--code-named Nile. The other enterprise technologies 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 secure distributed processing. Network OLE uses either Microsoft's implementation of RPCs or Distributed Computing Environment (DCE)-compliant RPCs to access remote objects. In addition, updates to OLE to provide distributed COM have a distinctly DCE flavor, using Kerberos security and DCE naming services.
As part of its enterprise strategy, Microsoft has also entered into agreements with Digital Equipment and Software AG to port OLE's non-GUI service layers (OLE Integration Services) to other operating systems. Enterprise marketeers at Microsoft forecast that OLE will eventually be available for all major operating systems. Multiplatform OLE and distributed COM mean that applications will be able to use objects on local and remote machines without having to worry about such things as operating system and word size.
For interoperable object technologies, it's clear that OLE is the de facto standard for Windows environments while the Object Management Group's (OMG's) Common Object Request Broker Architecture (CORBA) is dominant in the UNIX world. The fundamental purpose of CORBA and OLE is the same, but there are differences in areas such as interface definition languages (IDLs) and inheritance.
At one time, Microsoft was working with Digital to develop a Common Object Model that would be interoperable between CORBA and OLE. More recently, however, Microsoft has chosen to focus on OLE, leaving CORBA interoperability to third-party vendors. If you need UNIX connectivity today, you can look at products 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-based
data-access approach, but OLE DB expands that approach. It also moves you to a
programming model that is closer to the one you will use with Cairo. OLE DB will
expose the metadata in Cairo's Object File System (OFS) as objects.
Microsoft also sees OLE as a solution for multi-tier client/server applications that use remoteable OLE objects to encapsulate business processes. It will let you define cross-component business rules and create objects that export an event model so operations (e.g., updating a spreadsheet) will be able to fire events and make other updates. This capability, which is conceptually similar to triggers in SQL database, is useful in maintaining data integrity.
OLE DB provides data access while operating at the object level. It raises the level of abstraction for those who traditionally use library calls to access ISAM, flat-file, or relational databases. ODBC provides that type of CLI to a DBMS, but OLE offers the opportunity to operate at a lower level, using component interfaces and an event model. Microsoft anticipates that the interfaces between DBMS products (e.g., Microsoft SQL Server) will be open and in components.
In other words, you will be able to access the constituent components of a SQL DBMS (e.g., a parser). Relational vendors will be able to implement a component-based OLE interface into their DBMSs by implementing Nile. OLE DB will be able to use ODBC to access relational databases, and Microsoft will eventually supplement the ODBC Driver Manager with an OLE DB Manager. The new manager, code-named Kagera, will be an OLE DB data provider for ODBC. It will be able to use existing ODBC drivers (see Figure 2) as well as new data providers written to the Nile API.
If you use ODBC, DAO, and OLE, then you will find familiar terms in Nile's lexicon. A data source, bookmark, schema view, index, table, view, and rowset object have meanings consistent with those in ODBC and DAO, although terminology and implementation details may differ. OLE DB includes interfaces that are familiar to OLE developers. IPersistStream and IPersistFile save a persistent object, and IUnknown, the base interface of all COM objects, provides runtime dispatching.
OLE supports transactions, ODBC supports transactions, and Nile provides coordinated transaction interfaces that operate with OLE Transactions. Programming with Nile rowsets will involve operations that are conceptually similar to programming with ODBC cursors and DAO recordsets. Rowset operations will still support bound retrieval, scrollable next and previous operations, transaction isolation, and locking modes. Their behavior will depend upon runtime properties that you can set.
SQL commands drive ODBC, so it's no great surprise that Nile provides a command interface to let you extract data from data sources. ODBC doesn't support a standard mechanism for reporting an access plan (the feedback from a DBMS's query optimizer).
Nile provides a mechanism for getting command cost information. It can pass cost information in a manner similar to Jet's SQL passthrough feature (queries are passed to the data source without being processed by the Access Engine). A DBMS query analyzer parses a query statement and creates a structure known as a parse tree. Nile enables you to pass query subtrees to data providers.
Command-interface features include operators and data structures to support data 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 also provides data definition and data manipulation of SQL objects (including those in SQL-92 and SQL3).
| Object Acronyms | |
| CLI | Call-Level Interface |
| COM | Component Object Model |
| DAO | Data Access Objects |
| DS | Directory Services |
| OLE | Object Linking and Embedding |
| ODBC | Open Database Connectivity |
| OFS | Object File System |
| SAG | SQL Access Group |
| SQLDA | SQL Descriptor Area |
What's the Point?
What is the promise of Enterprise OLE, OLE DB, and component-level
interfaces? Consider a SQL query that uses heterogeneous data sources.
Heterogeneous joins are difficult because they often involve bringing rows from
the server to the client to do a local join. A component-level architecture
would allow bit-mapped optimization of queries as long as the servers provide
low-level access to those representations.
Clone objects enable you to introduce redundancy for high-reliability, high-availability systems. The Service Control Manager, proxies, and stubs provide the infrastructure to route to alternative objects.
For OLE DB to provide an interface to a virtual database, Microsoft must meet several challenges. Eventually, the component interfaces must expose as much functionality as APIs do, and the connections among components must perform as well as the code generated by an API.
Using current relational database technology and TP, it's possible to block application behaviors that compromise data integrity. Because OLE DB and OLE Transactions will have some data sources that lack comparable features, ensuring that data integrity is maintained will be a design challenge.
Network software supports adaptive and least-cost routing, and SQL databases can calculate a query's optimal access plan. Enterprise OLE will also address load balancing. However, Microsoft may find that developers want a cost model that includes both data-access and remote-access costs. Exposing this cost information to an object's user won't be a trivial task.
In the final analysis, only you can decide whether it makes sense for your company to adopt an ODBC-only strategy and ignore OLE DB. But in your company's deliberations, you should consider that Microsoft currently provides ODBC drivers 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.
| Contact Info |
| Microsoft * 206-882-8080 |
