Washington, September 15-18, 1999 – London, November 21-24, 1999

 

Understanding UDA and OLE DB

Dino Esposito

Universal Data Access (UDA) is Microsoft's strategy that aims to provide high-performance access to a variety of data sources. OLE DB is the layer of code that actually implements it. OLE DB can be seen as a superset of ODBC that stems out directly from the UDA vision. While these principles have been around for some time, they still sound odd to many developers. The point is that these developers are now migrating from ODBC to OLE DB and from RDO or DAO to ADO and sometimes they'll run into problems. The aim of this presentation will be introduce the key facts that somehow justify this troubleshooting, and address the reasons for using OLE DB and its mid-term advantages. Frequently you'll find that enterprise datastores are comprised of heterogeneous data: legacy archives on mainframes, RDBMS tables, documents, faxes, email, and spreadsheets. Coping with this wide range of formats of data is a frequent problem and can be an annoyance. You don't have a standard and easy-to-use interface to work with so many different data types, and you can't mix and join together different data sources. It seems that you have so many different types of data whereas really, data is just data.

In presence of legacy data, and needing a universal format, you can think of moving everything to a new information system based on a single DBMS server and you can write a universal layer of code on the top of it. This is what OLE DB providers actually do. This presentation will discuss the role of OLE DB and its high-level counterpart ADO in designing Web applications that can access in the same manner heterogeneous types of data regardless of the physical structure and their location.

Dino Esposito Dino (desposito@artis.net) is a trainer and consultant based in Rome, Italy. He specializes in Windows and scripting and authored Visual C++ Windows Shell Programming and Windows Scripting Host Programmer's Reference both from Wrox Press. Dino is also a contributing editor to Microsoft Internet Developer, MSJ and MSDN News. He's the cofounder of www.vb2themax.com, a VB-oriented Web site and works for Artis srl, a Rome-based consulting company active in the system integration area and Web-based architectures.

Data in an Enterprise Scenario

A corporate information system I've seen recently had the typical traits of a modern datastore, where the advent of the Internet and the advanced use of office products contributed to having a really heterogeneous collection of data formats. Despite the location and the structure, they are all part of the enterprise information property. As a developer, you're called upon to manage this data and mix, join, update, compare, retrieve, filter it in your applications. For this reason, you should welcome the possibility to access in a uniform manner different data.

That company I worked for had an IBM mainframe to hold sensitive and somewhat official data: invoices, customer payments, goods, etc. This appeared to be working successfully until they expanded into the Internet. All the information coming from and going to the Web (new customers, orders, customer care) is stored in a sort of primary cache machine based on SQL Server 6.5 (now 7.0) and Windows NT Server. This machine, equipped with IIS, also worked as the Intranet server. Batch procedures take care of moving data to the mainframe following a certain schedule.

In addition, lots of Word and Excel documents are used throughout the various offices and, although local, they are definitely data to considered. For example, a few small and messy applications were written as Excel worksheets, and never ported to Visual Basic. There were also documents with company policies, faxes, and on top of all this, vast amounts of email.

All this information is available to applications and users through different channels and products. Some applications used VB5 and RDO, others were C++ programs using ODBC and SNA Server. I counted also a few VBScript scripts and ASP pages based on ADO.

My intervention consisted of writing a module to bring email at the same level of a SQL Server table and to assist their developers in moving from RDO to ADO. Another project we're developing involves the host access. Some programs intrusively access tables via SNA Server and ODBC, while others rely on calling remote procedures. In every case, there is waste of middleware code that is hard and expensive to maintain.

OLE DB assists in solving such a complex task. An OLE DB provider that reads the folders of the Simple MAPI default client instantaneously converts your email into a relational table. Another much more complex provider is planned to encapsulate host access in a magic black box where programmers enter COBOL procedure names and get ADO recordsets from VB forms!

Universal Data Storage and Access

After all, the main point with UDA is not that it's universal, nor it's capability to shield you from database details, nor the access to non-relational data. What makes UDA an option to consider carefully when evaluating a data access technology is that it can glue together, almost like a single relational table, all the enterprise data, despite the nature of this data. When it comes to data access, there are only two choices: universalize the data access as UDA does, or universalize the data structure (UDS) by moving everything to a single all-encompassing database server. The main difference between UDA and UDS lies in data migration. UDA endeavors to glue together what your clients have today. UDS forces them to pack anything into the new DBMS, included non-relational and file-based documents. From an enterprise's point of view, this is not at all a secondary point, I guess. With UDA you have to rewrite your code but leave the data unchanged. With UDS you have to move or upgrade all your data, and you end up rewriting at least part of your code as well.

To exemplify, UDA is the theory that OLE DB puts into practice. UDS is the theory you find in the latest release of Oracle 8i, where you can import files and have them indexed and treated as native tables but paying the price of having a brand new file system and a whole new world for your data and your information system.

Where Do ODBC and OLE DB Differ?

There are lots of similarities and differences as well between ODBC and OLE DB. The basic idea is that OLE DB is ODBC done in a more general and all-encompassing way. A fairly simple (and limitative) way of looking at it is this: OLE DB is ODBC extended to non-relational data. This is certainly true but there's more to it that that.

OLE DB is much more independent from the physical structure of the data and provides components that let you work also without a connection to a database. Any groups of rows that are returned are managed by an independent object, and aren’t copied in the application's memory. This basic change opens a new world to applications.

OLE DB is not strictly based on SQL. Commands are just command strings and all the dialog between client and servers develops through COM. OLE DB, like ODBC, also has a client and a server side (called consumers and providers) but they all use COM and only COM. For a developer, it's not difficult to understand the concepts of consumers and providers. For writing either consumers or providers, all a developer needs is knowledge of COM and the OLE DB SDK.

This makes it much easier to write OLE DB providers than it was writing ODBC drivers. Furthermore, being independent from SQL and connections allows you to return, through COM, any data which can expressed in a tabular format, including the windows currently opened, email messages, an Excel spreadsheet, or a directory listing. This variety of applications makes it more likely that you have the need for custom OLE DB providers.

By comparing the way ODBC-compliant and OLE DB compliant applications access their data you see how much more powerful OLE DB could be. When using ADO, the default provider is the OLE DB provider for ODBC, but you can access any of the registered providers - it's just a matter of specifying what you need. A provider is just a COM inproc object.

OLE DB and Legacy Data

An OLE DB architecture is based on two types of modules: consumer and provider. The former acts as a client and connects to the data source, issues commands and receives recordsets in return. The latter works more like a server, encapsulating a resource. It receives commands, processes them and returns data in a tabular format, even though your data may not be in this tabular format to begin with. A consumer executes a series of steps: initializing the connection, creating a session and issuing a command. The command can be a SQL string or anything else the specified provider understands.

ADO is an object model that fully encapsulates the OLE DB infrastructure and exposes a simplified object model that looks like DAO and RDO, but that doesn't necessarily reflect the structure of the data. Calling through different providers with ADO is as easy as assigning the COM name (progID) of the provider to an ADO specific property called Provider.

OLE DB has been created to help integrating legacy data, where by legacy I mean data that can't be moved or migrated, data that must remain in its native state. From this point of view, there's a strict relationship between OLE DB and this data since OLE DB is just a way to expose custom and non-standard data in a standard way.

OLE DB, ADO and ASP

The example attempts to illustrate how transparent for the programmer can be using a custom OLE DB provider. The demo provider is the one that the ATL Wizard builds by default. It returns as records the attributes of a file. It doesn't support SQL of course and a command is simply given by the file spec.

Dim rs

Set rs = CreateObject("ADODB.Recordset")

rs.CursorLocation = 3  'adUseClient

rs.Open "c:\*.*", "FileSystem.FileSystem.1"

Set rs.ActiveConnection = Nothing

---