Database Programming with OLE and ActiveX

The SQL DBMS market has long been a buyer's market. To remain competitive, SQL vendors must improve performance, add features, and reduce prices. Vendors can no longer gain and hold market share based solely on the merits of a database engine or server a
rchitecture. Ease of programming has become as much a part of a product evaluation as the results of performance benchmarks. Because programming features affect sales, most DBMS vendors recognize the importance of programming interfaces. The recently ado
pted SQL Call Level Interface (CLI) provides little opportunity for product differentiation. In an era when embedded SQL and SQL CLI are standard, how is a vendor to provide unique programming features? The answer is objects.

What Is OLE and Where Did It Come From?

OLE is an enabling technology for building shared, distributed objects. A review of certain Object Linking and Embedding (OLE) and ActiveX concepts will help you understand this article. (See "Understanding OLE," DBMS, June 1995, page 50.) The binary-lev
el incompatibility of classes and objects created with C++, Smalltalk, and other OOP languages created a demand for a shared-object architecture. Such an architecture would enable us to share local or remote objects among disparate programming languages
and applications. The Object Management Group (OMG) responded with the Common Object Request Broker Architecture (CORBA), and Microsoft responded with OLE 2.0. OLE is based on a Component Object Model (COM), which provides interoperability at a binary le
vel.

OLE components encapsulate data and the methods that operate on that data. OLE includes service layers that build on COM to provide embedded objects, access to structured storage, persistent objects, drag-and-drop editing, and other capabilities. Microso
ft plans to wrap most operating system services in COM objects so programs can use OLE instead of calling the DLLs that support the Windows API. Automation interfaces permit OLE components to expose the commands and methods by which it is possible for sc
ripts and programs to drive an application. To supplant its popular Visual Basic components (VBXs), Microsoft specified a new component architecture called OCXs that added interfaces to OLE in order to support events.

OLE supports transparent remoting, a process that makes objects on remote computers appear to be local. The OLE architecture includes proxies and stubs so that remote or out-of-process objects appear to be in the same address space (in-process) as the ob
ject user. Figure 1 illustrates a COM object using a proxy and stub to provide access to a remote object. The version of the COM that supports distributed component objects is distributed COM, or DCOM. COM and DCOM use remote proce
dure calls to marshal data between processes and computers so that components appear to have the same word size, bit orientation, and address space. Microsoft is shipping DCOM as part of Windows NT 4.0, thus providing the foundation on which a company ca
n build a component-based DBMS. "DBMS as components" is a theme behind much of Microsoft's product planning and development for SQL Server, OLE DB, OLE Transactions, and other software.

ActiveX Emerges

In 1995, NCompass Labs demonstrated that it was possible to mix OLE with Web browser plug-in components, Microsoft partnered with NCompass, and a paradigm shift was born. In March 1996, Microsoft introduced the term "ActiveX" to refer to software that is
Internet-enabled. ActiveX software provides connectivity using Internet and Web technology, even though the user may connect only to internal networks (Intranets). As part of an effort to Internet-enable its products, Microsoft recently introduced suppo
rt for distributed components operating over the Hypertext Transfer Protocol (HTTP). HTTP is the protocol used to provide data to Web browsers. Microsoft's purpose in introducing ActiveX is to permit an application to use objects that reside on a local h
ard disk, on a server in the next room, or on a Web server anywhere in the world.

Because ActiveX enhances OLE with Web access, ActiveX controls are now trendy, and OLE custom controls (OCXs) have become pass&eacute. ActiveX controls are like low-fat OCXs: They involve fewer interfaces and load faster. ActiveX also applies to other OL
E technologies. For example, OLE automation interfaces that are Internet-enabled become ActiveX automation interfaces. To recap, OLE provides object sharing for local or remote objects, and ActiveX supplements that capability with Internet access.

The classic SQL client/server model uses remote procedure calls with proprietary network libraries. Applications use client/server protocols that operate over standard network transports such as TCP/IP, NetBIOS, and IPX/SPX. This model resulted in Oracle
users becoming familiar with Transparent Network Substrate and SQL*Net, while Sybase users worked with Net Library and the Tabular Data Stream (TDS) protocol. Microsoft's inclusion of DCOM in its operating systems may change DBMS vendors' reliance on pr
oprietary network transports. To exploit the Microsoft foundation that provides distributed objects for free, a DBMS must expose interfaces at the component level. To do so, Microsoft has been using its SQL Server product, which is consistent with its pl
an to expose component interfaces everywhere. Oracle and Sybase also provide OLE interfaces to their SQL servers but have not made clear whether they will expose interfaces to low-level DBMS components.

SQL, Components, and OLE

Prior to OLE 2.0, it was not unusual for Windows client applications to use Dynamic Data Exchange (DDE) to communicate with software that provided access to SQL databases. OLE 2.0 introduced automation interfaces that were easier to use and more reliable
than DDE. Many independent software vendors developed Visual Basic controls (VBXs), but then rewrote their component products as OLE custom controls (OCXs) and eventually ActiveX controls. Sylvain Faust Inc. (SFI) is one such Windows software vendor tha
t developed component-based products for SQL databases. SFI's SQL Sombrero includes a VBX, automation interfaces, and OCXs for accessing Sybase, Microsoft SQL Server, and Oracle databases.

SQL DBMS vendors noticed the demand for components and adopted OLE in client-side tools or as an object interface to their servers. Oracle Objects for OLE encapsulates the Oracle Call Interface (OCI). Oracle Objects for OLE includes a data control that r
eplaces the Visual Basic data control for developers who prefer to connect to Oracle databases without using the Access Engine or RDO. Oracle Objects for OLE includes a 16-bit OLE automation server; Oracle is now beta-testing a 32-bit version. The produc
t also includes C++ class libraries for Borland C++ and Visual C++. Sybase ObjectConnect, currently in development, will use a repository that contains object mappings to relational databases. Developers using C++, OLE, and eventually Java will be able t
o use components created from the same relational schema. ObjectConnect for OLE and ObjectConnect for C++ use a single object model to create bindings for programs that use OLE automation or C++ classes. When it becomes available, Distributed ObjectConne
ct will add bindings for Java. Sybase reworked the TDS protocol to support the exchange of objects between the client and Sybase servers. Sybase also updated PowerBuilder so that version 5.0 can create automation servers. Oracle and Sybase expose automat
ion interfaces so that programs and scripting languages can use those interfaces to access databases. Informix has taken a different path by adding OLE automation to its New Era application development suite.

OLE DB and ADO

OLE DB is the data access cornerstone of Microsoft's Enterprise OLE strategy that uses OLE-based solutions for crafting enterprise applications. In March 1996, Microsoft revised that strategy to include ActiveX technology and positioned ActiveX Data Obje
cts as language-independent objects built on top of OLE DB. Microsoft is unifying RDO with DAO and positioning ActiveX Data Objects (ADO) as their successor. At press time, OLE DB was in beta test; no dates are forecast for ADO. Microsoft released the OL
E DB SDK version 1.0 in early September. It can be downloaded at no charge from the Microsoft Web site at http://www.microsoft.com/oledb.

OLE DB 1.0 includes software components that provide COM interfaces with C/C++ bindings. In a future release, Microsoft will provide automation interfaces to permit Visual Basic and scripting languages to use OLE DB. Programmers manipulate OLE DB objects
by setting properties and executing methods. OLE DB builds on existing OLE services. Data access programs are likely to contain a mix of OLE DB calls and calls to common interfaces such as IUnknown and IPersistFile. IUnknown is a dispatching interface e
xposed by all COM objects. IPersistFile provides persistent storage, so it is useful to cache information.

In the vocabulary of OLE DB, a provider is software that provides data or services to consumer applications. A data provider provides access to specific data such as text or a Sybase database, and a service provider performs a
service such as processing queries. Data providers must implement a fundamental set of interfaces by which applications can use their component services. They may also implement other interfaces for transactions, ISAM access, data definition, and comman
d processing. The base-level interfaces provide the ability to use Data Source, Enumerator, DBSession, and Rowset objects. For some data providers, the DBSession object includes interfaces for transactions (ITransactionLocal) and command processing (IDBC
reateCommand). Applications that access SQL data sources will use IDBCreateCommand to create a Command object, which in turn exposes command text (ICommandText), parameters (ICommandWithParameters), column information (IColumnsInfo), and other interfaces
. A Data Source object includes an interface (IDBInfo) that provides information about names, features, and capabilities. Enumerator objects enumerate data sources, providers, and other enumerator objects. Enumerators access the data source and provider
information stored in the registry. OLE DB includes a command interface (ICommand) for use with language-driven providers such as a provider for SQL data. Figure 2 illustrates the relationship of the DataSource, DBSession, and Rows
et objects.

Most OLE DB programming will center on the Rowset object (IRowset), a counterpart to SQL result sets and DAO recordsets. Like its counterparts, a Rowset can include data or metadata. For SQL data sources, a Rowset is typically the product of a query. Bec
ause OLE DB supports non-relational data, a Rowset can also be the product of indexed searches or sequential traversal of data. Programs doing indexed retrieval for ISAM operations will use the Index object. The typical query processing cycle will be fam
iliar to programmers using today's SQL APIs. An application uses the DBBINDING and DBCOLUMNINFO structures in the course of specifying bindings and executing fetches.

OLE DB's Accessor objects simplify the retrieval process. An Accessor (IAccessor) handles type coercion, unpacking rows, and other retrieval mechanics. Accessors can read Rowsets by copying data or reading by reference (in-place addressing with pointers)
. Once a consumer application has created bindings and accessors, it uses Rowset methods to retrieve the next row, previous row, bookmark rows, and so on. OLE DB supports immediate updates with pessimistic locking and deferred updates that support optimi
stic locking levels. It also supplies an IRowsetLockRows interface to support other locking techniques. OLE DB supports the four isolation levels defined by SQL-92, and OLE DB 1.0 will support SQL data access, but future releases will provide interfaces
that permit access to language-independent command trees.

OLE DB defines handles to reference entities such as rows (HROW) and result codes (HRESULT). OLE DB also defines an ERRORINFO structure and error interfaces that support multiple error objects. It supports retrieval of metadata and schema information usi
ng techniques similar to those used for data. OLE DB providers support schemas and catalogs consisting of one or more schemas. Schemas are of two types. Logical information schemas provide logical metadata and a superset of SQL-92's INFORMATION_SCHEMA; l
ogical schema information includes rowsets such as CHECK_CONSTRAINTS, ASSERTIONS, FOREIGN_KEYS, and COLUMN_PRIVILEGES. The other type, physical information schemas, include Rowsets that identify CATALOGS, INDEXES, PROVIDER_TYPES, and STATISTICS.

To make OLE suitable for building custom controls, Microsoft had to add event processing to OLE 2.0. It added interfaces and connection points to support notifications among cooperating components. OLE DB uses a similar event model that permits data cons
umers to register to receive notification of events, such as changes to Rowsets. For example, a consumer application displaying rows in a spreadsheet control could use IRowsetNotify to show updates to the displayed data automatically.

OLE DB supports the retrieval and storage of binary large objects and OLE objects. In a future release it will support user-defined abstract data types. In this context the term OLE objects refers to objects that support the IPersist storage and stream i
nterfaces; it also means COM objects that support another persistence mechanism. Microsoft intends for its operating systems to support OLE interfaces to system services and an object file system. Microsoft will build on OLE-persistent objects to supply
the data provider for the object file system. For OLE DB 1.0, Microsoft is shipping only a provider for relational data, although you might eventually see providers for Microsoft Exchange, Word, and other data associated with its products.

Transactions and the ODBC Data Provider

To understand transactions and OLE DB, look at a larger picture that includes OLE Transactions and Microsoft's Distributed Transaction Coordinator (DTC). OLE DB programmers can do commit and abort processing by using ITransaction interfaces. To coordinat
e these activities across computers, a programmer can use the Windows NT Distributed Transaction Coordinator. The DTC works with X/Open XA-compliant transaction managers and is therefore compatible with transaction monitors such as Transarc Encina. OLE D
B supports coordinated transactions by exposing an ITransactionJoin interface on DBSession objects. Multiple providers can participate in coordinated transactions. OLE Transactions supports event notification through the use of OLE's connection point mec
hanism.

OLE DB programs can connect to ODBC data sources by using an ODBC data provider, code-named Kagera, capable of working with version 2.x and 3.0 ODBC drivers. Visigenic Software, OpenLink, Intersolv, and Simba Technologies successfully tested their driver
s with Kagera in July 1996. OpenLink is also working on a data provider, and Intersolv is working on a data provider, service providers, and ActiveX controls. Simba Technologies is partnering with NCompass to develop ActiveX support for Simba Express, it
s ODBC server.

The Security Model and the Cost Model

Security is a concern when developing software that operates on enterprise databases. Those concerns increase if the word "Internet" is part of a project description - so security is a requirement for OLE data access. DCOM implements a security model for
components. OLE DB complements DCOM with authentication and authorization services. OLE DB permits developers to get and set permissions based on users, objects, and groups.

Developers writing database or network programs often require the ability to manage resources by applying costs. When it comes to a cost model, OLE DB brings both good news and bad news. The good news is that OLE DB will implement a cost model for comman
ds (although it won't be included in version 1.0). The bad news is that Microsoft didn't implement a component cost model at the COM level.

Database Objects: JDBC and ActiveX

Much of the computer industry is coalescing around Java as the solution for portable software and multiplatform component development. Although Java is clearly on the road to becoming as widely deployed as C, it isn't yet obvious which database object in
terface will become as ubiquitous as ODBC. SQL vendors including IBM, Informix, Sybase, and Oracle have partnered with Sun to develop the Java Database Connectivity (JDBC) API. JDBC is an API that provides object layer access to SQL databases. Sun's part
ners also include ODBMS vendors - the company hopes to position JDBC as an important interface for both relational and object databases.

Microsoft's direction with OLE DB and its database products represents a programming model that emphasizes component-level access. This model contrasts with most extant programming interfaces, such as embedded SQL, CLI, and ODBC. JDBC exposes objects suc
h as connections and statements but doesn't provide objects for working with a DBMS's constituent components. JDBC is closer to the level of abstraction that you see today in Remote Data Objects (RDO) and that you are likely to see in ActiveX Data Object
s. However, Java programmers may be able to work with components in the future, because Sun recently announced Java Beans, an initiative that will produce an API for using OpenDoc, ActiveX, and other components. SQL vendors haven't rushed to announce Ope
nDoc interfaces, although Oracle's Sedona project will reportedly support OpenDoc.

Objects or Traditional CLI?

For several years, the decision makers in Redmond have provided a variety of data access solutions. Microsoft's OLE DB will appeal to developers who require component-level access to databases. For those programmers opting for higher-level objects, Micro
soft's solution is ADO. JDBC, OLE DB, and ADO are attractive because objects provide abstraction and simplify development. Simplified programming means more rapid development, but reduced development cycles are often a tradeoff against performance. Also,
developers sometimes need features that the object implementation doesn't expose. For these reasons, ODBC drivers are likely to remain a vital plug-in capability.

New releases of components and objects typically improve performance and add features. It is difficult for the first generation of components or objects to encapsulate all of the functionality available by using traditional C function calls to a shared l
ibrary or dynamic link library (DLL). For example, DAO 1.0 didn't have sufficient methods and properties to provide functionality equivalent to direct calls to ODBC. As another example, Seagate Crystal Reports gives developers the choice of using compone
nts (VBX and OLE) or making direct calls to a DLL. The first VBX and OLE versions of Crystal Reports didn't provide the full range of capabilities available by calling its DLL.

The axiom about maturation improving functionality also applies to performance. Because ODBC evolved since 1992, driver developers studied the architecture and optimized their code. Today's drivers are capable of producing impressive performance. Recentl
y I saw a demonstration of Trilogy Technology Inc.'s OpenPath ODBC driver. The demo used repeated runs to populate a PowerBuilder DataWindow with 10,000 rows from an Oracle database. Over several runs, the OpenPath driver was consistently four to five se
conds faster than native SQL*Net performance. Such speed when using a CLI means that developers writing performance-critical applications are likely to get out a stopwatch when making the choice between objects or a CLI. But when rapid development time i
s the overriding concern, developers are likely to use an object solution such as Oracle Objects for OLE, ObjectConnect, SQL Sombrero, JDBC, or ActiveX Data Objects.

Ken North develops software, consults, teaches seminars, and writes. He is the author of Windows Multi-DBMS Programming (John Wiley & Sons, 1995). You can email Ken at kennorth@interserv.com.

Data Access: The Microsoft Perspective

North: Someone using current releases of Microsoft development products can choose from an array of data access techniques. Microsoft announced plans to unify Data Access Objects (DAO) and Remote Data Objects (RDO). Microsoft also recently released OD
BCDirect support in DAO. How does OLE DB fit into the picture, and what are Microsoft's plans for using OLE DB in its own products?
Nelson: Microsoft will provide a set of high-level automation interfaces that will eventually replace both DAO and RDO. These interfaces will provide access to the OLE DB functionality from any tool or language.

North: Microsoft has agreements with companies that are licensed to port OLE to non-Windows operating systems. Will OLE DB be available for Unix, Macintosh, or other systems? For which operating systems will Microsoft ship an OLE DB SDK?
Nelson: OLE DB SDK V1.0 will be available on Windows NT 4.0 and Windows 95. We are planning to make OLE DB available in all platforms where COM/DCOM is available through third parties.

North: In recent years, Microsoft has included ODBC drivers with some of its products. For which data formats (such as Exchange or Word) will Microsoft be shipping OLE DB data providers?
Nelson: The initial release of the SDK is focused on giving Independent Software Vendors a set of tools to begin providing and consuming OLE DB-based data. Part of the SDK is an ODBC provider so that any OLE DB consumer can access ODBC-based data. There
is also a OLE DB provider that lets ODBC consumers access OLE DB providers. We haven't announced other specific providers.

North: How does OLE DB fit into Microsoft's ActiveX architecture? Will Web developers using scripting products such as VBScript be able to use OLE DB data providers? Will developers using Visual J++ be able to use OLE DB in Java applets?
Nelson: OLE DB provides a COM-based way to get to any data from a C/C++ environment. As I mentioned earlier, Microsoft will provide automation interfaces so that all other languages can access that functionality.

North: Oracle has been working on an object-oriented repository code-named Sedona. Microsoft partnered with Texas Instruments in an effort to develop a repository. Enterprise developers switching to OLE DB will have to deal with object sharing and ver
sion control issues. Is the repository Microsoft's answer to those problems, or will other tools be available?
Nelson: Our work with Texas Instruments is in the area of co-design but not development. Object sharing and version control issues exist with or without OLE DB. We already have some solutions available; for instance, our SourceSafe product does this kind
of management. We have plans to evolve that functionality to a full-blown repository in the future.

North: Java proponents see applets and downloadable database drivers as an important solution for creating thin clients. What is Microsoft's solution for thin clients that use ODBC or OLE DB?
Nelson: When the drivers are downloaded does not help define a thin client. The answer is to keep the data access code on a remote server. This is already possible today in Microsoft Internet Information Server using the ODBC Database Connector. OLE DB w
ill provide remoting technology as well, over both HTTP and DCOM, so OLE DB service providers can reside in any network configuration.