Creating an OLE DB table UDF

• define the table that the OLE DB provider returns
• specify LANGUAGE OLEDB
• identify the OLE DB rowset and provide an OLE DB provider connection string in the EXTERNAL NAME clause

OLE DB data sources expose their data in tabular form, called a rowset. A rowset is a set of rows, each having a set of columns. The RETURNS TABLE clause includes only the columns relevant to the user. The binding of table function columns to columns of a rowset at an OLE DB data source is based on column names. If the OLE DB provider is case sensitive, place the column names in quotation marks; for example, "UPPERcase".

The EXTERNAL NAME clause can take either of the following forms:

     'server!rowset'
  or
     '!rowset!connectstring'

For example, you can define an OLE DB table function and return a table from a Microsoft Access database with the following CREATE FUNCTION and SELECT statements:

   CREATE FUNCTION orders () 
     RETURNS TABLE (orderid INTEGER, ...)
     LANGUAGE OLEDB 
     EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
                   Data Source=c:\msdasdk\bin\oledb\nwind.mdb';

   SELECT orderid, DATE(orderdate) AS orderdate, 
                   DATE(shippeddate) AS shippeddate 
   FROM TABLE(orders()) AS t 
   WHERE orderid = 10248;

Instead of putting the connection string in the EXTERNAL NAME clause, you can create and use a server name. For example, assuming you have defined the server Nwind, you could use the following CREATE FUNCTION statement:

   CREATE FUNCTION orders () 
     RETURNS TABLE (orderid INTEGER, ...)
     LANGUAGE OLEDB 
     EXTERNAL NAME 'Nwind!orders';

OLE DB table functions also allow you to specify one input parameter of any character string data type. Use the input parameter to pass command text directly to the OLE DB provider. If you define an input parameter, do not provide a rowset name in the EXTERNAL NAME clause. The database passes the command text to the OLE DB provider for execution and the OLE DB provider returns a rowset to the database. Column names and data types of the resulting rowset need to be compatible with the RETURNS TABLE definition in the CREATE FUNCTION statement. You must ensure that you name the columns properly, because binding of the column names of the rowset is based on matching column names.

The following example registers an OLE DB table function, which retrieves store information from a Microsoft SQL Server 7.0 database. The connection string is provided in the EXTERNAL NAME clause. The table function has an input parameter to pass through command text to the OLE DB provider, so the rowset name is not specified in the EXTERNAL NAME clause. The query example passes in a SQL command text that retrieves information about the top three stores from a SQL Server database.

   CREATE FUNCTION favorites (VARCHAR(600)) 
     RETURNS TABLE (store_id CHAR (4), name VARCHAR (41), sales INTEGER)
     SPECIFIC favorites
     LANGUAGE OLEDB 
     EXTERNAL NAME '!!Provider=SQLOLEDB.1;Persist Security Info=False;
     User ID=sa;Initial Catalog=pubs;Data Source=WALTZ;
     Locale Identifier=1033;Use Procedure for Prepare=1;
     Auto Translate=False;Packet Size=4096;Workstation ID=WALTZ;
     OLE DB Services=CLIENTCURSOR;';

   SELECT *
   FROM TABLE (favorites (' select top 3 sales.stor_id as store_id,  ' || 
                          '        stores.stor_name as name,         ' ||
                          '        sum(sales. qty) as sales          ' ||
                          ' from sales, stores                       ' ||
                          ' where sales.stor_id = stores.stor_id     ' ||
                          ' group by sales.stor_id, stores.stor_name ' ||
                          ' order by sum(sales.qty) desc')) as f;