MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB
Once data has been fetched and formed into hierarchies, you can reuse those rowsets in new relationships. These reshape operations are performed only outside the data store, allowing prototyping and experimentation with the data.
Reshaping allows a SHAPE command to use an existing rowset, created under the same connection, as the child rowset of a newly created rowset. The existing rowset is unaffected by this reshaping except that it acquires a new parent rowset.
The following example, where the connection remains active, illustrates the ability to reshape hierarchies using the Shape language. First, rsOrders and rsOrderItems are related by order_id. The second SHAPE command creates a computed parent of rsOrderItems that is related by item_id. Finally, rsCustomer is created as the parent of rsOrders related by cust_id.
Rs1.open _
"SHAPE {select * from Orders} as rsOrders " & _
"APPEND ({select * from OrderItems}" & _
"AS rsOrderItems" & _
"RELATE order_id TO order_id)"
RS2.open
"SHAPE rsOrderItems AS rsItems " &_
"COMPUTE rsItems BY item_id"
RS3.open
"SHAPE {select * from Customers} AS rsCustomer " & _
"APPEND (rsOrders RELATE cust_id TO cust_id)" _
This results in the following hierarchy:
The rowset alias is used if it is specified in the SHAPE command that creates the rowset. Otherwise, a unique name is automatically generated by the Data Shaping Provider with the format of dsrowset<n>, where n is a unique but not necessarily sequential number beginning with 1.
In case of name collisions, the naming depends on the Unique Names property of the Data Shaping provider. If True, the user name of the cursor is appended, if necessary, with a number unique for the shape namespace. For example, a collision on a cursor named orders will generate orders_<n>. If False, the synonym name is valid and becomes the value of the Name property. This rowset will not be able to participate in reshape commands until after all other rowsets with the same name are closed.
The source of a shape component can also be a hierarchical cursor name. For each rowset, the name is set by default from the associated alias, if specified, in the SHAPE command that creates it.
The SHAPE command always refers to Recordsets already created in the same session. That is, when reshaping, the SHAPE command explicitly refers to rowsets already created by the same MSDataShape provider session object.
The APPEND clause cannot be used to add new columns to an existing (reshaped) rowset.
Reshaping of parameterized rowsets is not supported.
When working in ADO and accessing child Recordsets in a hierarchy, the entire child Recordset is not exposed. However, the following reshape technique gives you chapterless access to the Recordset without giving it a new parent. This applies both to computed and to related hierarchies. It will not work for parameterized child rows because they are not reshapable, and parameterized rowsets are never accessible as a complete rowset.
In the following example, reshaping rsOrderItems produces a Recordset scoped to include all rows in rsOrderItems, exposing all the child rowsets.
rs1.open "SHAPE {select * from Orders} APPEND ({select * from " &_
"OrderItems} as rsOrderItems RELATE order_id TO order_id)",con
rs2.open "shape rsOrderItems",con