Invoking the Data Shaping Service for OLE DB
Important
This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.
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