Access to Diverse Sources

OLE DB enables the development of applications that access diverse data sources. For example, consider a sales representative who wants to find all e-mail messages received within the last two days from Seattle customers, including their addresses, to which no one has yet replied. This query involves searching the mailbox file containing the sales representative's e-mail, as well as a Customers table stored in a Microsoft Access DBMS to identify customers. The query can be formulated in an extended SQL syntax as follows. (This query is designed for illustration only. It is not implemented in any product of which we are aware.)

SELECT  m1.*, c.Address FROM
 MakeTable(Mail, d:\mail\smith.mmf) m1,
 MakeTable(Access, d:\access\Enterprise.mdb, Customers) c
 WHERE m1.Date >= date(today(), -2)
  AND m1.From = c.Emailaddr
  AND c.City = "Seattle"
  AND NOT EXISTS
   (SELECT * FROM MakeTable(Mail, d:\mail\smith.mmf) m2
    WHERE m1.MsgId = m2.InReplyTo);

Assume that MakeTable is a function that makes the mail file D:\Mail\Smith.mmf into a table. It also exposes the Customers table from a Microsoft Access database (D:\Access\Enterprise.mdb). The function date takes a date and a number of days as arguments and produces a date.

OLE DB makes it easy for applications to access data stored in diverse DBMS and non-DBMS information sources. DBMS sources may include mainframe databases such as IMS and DB2, server databases such as Oracle and Microsoft® SQL Server™, and desktop databases such as Microsoft Access, Paradox, and Microsoft® FoxPro®. Non-DBMS sources might include information stored in file systems such as Windows NT® and UNIX, indexed-sequential files, e-mail, spreadsheets, project management tools, and many other sources.