MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB

Invoking the Data Shaping Service for OLE DB

See Also                Example    Related Topics

Creating a hierarchy is a two-step process, requiring connection to the Data Shaping Provider and then using the SHAPE command.

Connecting from Within OLE DB

The Data Shaping Service is an OLE DB provider and can be created by the Data Init Service like any other OLE DB provider. You can also create it using the CoCreateInstance method.

Before initializing the Data Shaping data source object, you should set the data provider property to the name (progID) of the data provider you wish to use. If a data provider is not required, the data provider can be set to none, such as "Data Provider = NONE". You should also set other properties that the Data Shaping Provider will need to connect to your data provider.

Use CoCreateInstance to create an instance of the Data Shaping Provider and pass in the appropriate connection properties. To view examples of using the Data Init Service and CoCreateInstance, see "Creating Data Source Objects" in Chapter 2 of the OLE DB Programmer's Reference (version 2.0 or later).

Connecting from within OLE DB to a remote data provider

The connection string to connect in OLE DB to a remote data store resembles the following:

Provider=MSDataShape;Data Provider=MS Remote;Remote Server=http://webaddress;Remote Provider=SQLOLEDB;Data Source=Pubs;User ID=eem;Password=pwd

Connecting from ADO

The ADO user must explicitly specify Provider=MSDataShape to use the Data Shaping Provider. It is not a semiautomatic service like session pooling and other OLE DB services. Use your provider name and its connection properties (data set name, user id, and so on) following the provider name of MsDataShape, as in this example:

Provider=MSDataShape;data provider=MSDASQL;Driver={SQL Server};Server=Lancer;Database=Sales;UID=JeffG;PWD=;

To create an empty hierarchical rowset that can later be populated with data or reshaped, it is not necessary to connect to a data provider. The connection string would be as follows:

Provider=MSDataShape;data provider=NONE

For more information about creating hierarchical rowsets, see "Fabricating Rowsets" later in this guide.

ADO Connection Example

The following example illustrates using the Shape grammar after invoking the Data Shaping Provider and connecting to a data provider:

Sub datashape()
   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim rsChapter As Variant

   cnn.Open "Provider=MSDataShape;data provider=MSDASQL;" & _
      "Driver={SQL Server};Server=Lancer;Database=Sales;" & _
      "UID=JeffG;PWD=;"

   rst.Open "SHAPE {select * from customer} APPEND ({select * from " & _
      "orders} AS chapter RELATE cust_id TO cust_id) ", cnn

   While Not rst.EOF
      Debug.Print rst("cust_fname"), rst("cust_lname"), rst("state"), _
      rst("cust_id")

         rsChapter = rst("chapter")
         While Not rsChapter.EOF
            Debug.Print rsChapter(0), rsChapter(1), rsChapter(2), _
            rsChapter(3)
            rsChapter.MoveNext
         Wend
   
         rst.MoveNext
   Wend

End Sub