OLE DB user-defined table functions

Microsoft OLE DB is a set of OLE/COM interfaces that provide applications with uniform access to data stored in diverse information sources. The OLE DB component DBMS architecture defines OLE DB consumers and OLE DB providers.

An OLE DB consumer is any system or application that consumes OLE DB interfaces; an OLE DB provider is a component that exposes OLE DB interfaces. There are two classes of OLE DB providers: OLE DB data providers, which own data and expose their data in tabular format as a rowset; and OLE DB service providers, which do not own their own data, but encapsulate some services by producing and consuming data through OLE DB interfaces.

The database manager simplifies the creation of OLE DB applications by enabling you to define table functions that access an OLE DB data source. The database manager is an OLE DB consumer that can access any OLE DB data or service provider. You can perform operations including GROUP BY, JOIN, and UNION on data sources that expose their data through OLE DB interfaces. For example, you can define an OLE DB table function to return a table from a Microsoft Access database or a Microsoft Exchange address book, then create a report that seamlessly combines data from this OLE DB table function with data in your database.

Using OLE DB table functions reduces your application development effort by providing built-in access to any OLE DB provider. For C, Java™, and OLE automation table functions, the developer needs to implement the table function, whereas in the case of OLE DB table functions, a generic built-in OLE DB consumer interfaces with any OLE DB provider to retrieve data. You only need to register a table function as LANGUAGE OLEDB, and refer to the OLE DB provider and the relevant rowset as a data source. You do not have to do any UDF programming to take advantage of OLE DB table functions.

To use OLE DB table functions with the database, you must install OLE DB 2.0 or later, available from Microsoft at http://www.microsoft.com. If you attempt to invoke an OLE DB table function without first installing OLE DB, the database returns SQLCODE -465, SQLSTATE 58032, reason code 35. For the system requirements and OLE DB providers available for your data sources, refer to your data source documentation. For the OLE DB specification, see the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.