Invoking the Data Shaping Service for OLE DB

Creating a hierarchy is a two-step process, requiring that you connect to the Data Shaping Provider and then use 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: for example "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 the OLE DB Programmer's Reference (version 2.0 or later).

Connecting from within OLE DB to a Remote Data Provider

The connection string that connects to a remote data store by using OLE DB 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, you do not have 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 how to use the Shape grammar, after you have invoked the Data Shaping Provider and connected 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

See Also

Reference

Creating Data Source Objects

Data Source Object States

Concepts

Data Source Objects