What Is OLE DB?

OLE DB is to diverse unstructured data what ODBC is to structured SQL-based databases. It is a unifying access method that provides a single route to accessing any data. So in a way similar to that used by ODBC, OLE DB employs a layered model. Thus diagrams of the two architectures might at first seem similar, as you can see in Figure 13-6 and Figure 13-7.

Figure 13-6 ODBC architecture

Figure 13-7 OLE DB architecture

OLE DB is different in two important aspects, however. First, unlike ODBC, OLE DB is based on OLE technology rather than on a simple API approach. Second, also unlike ODBC, OLE DB is not constrained by requiring all data access and manipulation to be performed via SQL. The only criterion that OLE DB imposes is that the data returned by a command be in the form of a table. So provided that the result of a file system query can be represented by a table (which ultimately all data can be—that is, a table of just one row and one column), an application using OLE DB should be able to retrieve and display the data.

Actually, I have cheated. There is a third major distinction between OLE DB and ODBC. OLE DB is heterogeneous in the same way that DAO builds added value onto ODBC by enabling the user to execute table joins between different data sources. For example, consider the following SQL code:

SELECT  * 
FROM  employees, deptsales
WHERE employees.deptid = deptsales.deptid

In this example, the employees table could be in a SQL Server database containing information for a corporate human resources system, and the deptsales table could be a Microsoft FoxPro table maintained by the sales department.

In a similar way, OLE DB enables data to be joined from vastly different types of data sources, as shown here:

REPORT email_address
TO system_supervisor
USING email_sent, stored_attachments
WHERE email_sent.owner = stored_attachments.owner
    AND stored_attachments.size > 10k

In this example, the email_sent table is exposed by an OLE DB object that can read an e-mail system, and the stored_attachments table is a table that represents a particular set of disk files. I have altered some of the keywords from their SQL equivalent to emphasize the fact that OLE DB is not SQL-based and commands can be syntactically quite different.

One last point to remember is that because OLE DB uses the OLE Component Object Model (COM) infrastructure, OLE DB is perhaps the best choice for access to data in a COM environment.