The Micro$oft OLE DB Data Shaping Provider is an extension to OLE DB that allows for a more hierarchical representation of data. Every version of ADO since version 2.0 is capable of accessing it. I've only just learned how to use it, and I'm just as happy as can be.

If you will refer to the VB code below, I'll step you through the basics of this nifty tool. Say, for example, you wanted to represent a listing of all noders with a listing of their nodes and node dates under them. To do this you will need to create a ADO connection and two recordset objects, joining them together with the Data Shaping Provider. I've named the recordsets rsOuter and rsInner since we are going to be looping through them, and this gives you an idea of the relationship between the two. The key thing to remember is to add the term "Provider=MSDataShape;" to your connection string. I was initially confused by the two 'provider' terms in the connection string, but notice you are identifying a data provider and the shaping provider. ADO is smart enough to keep this straight; I'm apparently not.

The next thing to do is to fetch your data. In the next section it looks like I am building a funky SQL statement, and that's exactly what I'm doing. The syntax is what the MSDataShape provider requires to relate the two sets of data. The basic syntax is

SHAPE {SqlStatement1} APPEND ({SqlStatement2} AS recordset RELATE key1 TO key2)
Note the curly braces and parentheses. Keep these straight because the error messages sent back by the provider are not very descriptive if you screw up . The SHAPE command does exactly what it looks like it does. It appends two sets of data together, relating them by a common key field. The 'recordset' is the output parameter that contains the data.

Next, open the outer recordset object with the command we just made. We can instantiate the inner recordset with the field value after the AS keyword. The next several lines of code simply loop through the two recordsets and print them to the screen.

Why the heck would you want to do this? I'll leave that to your imagination, but one application that jumps out at me is for the aggregation of child data. It's pretty easy, using this tool, to create a report of parent data, noders for example, and aggregate some related child data, such as a count of their nodes. To do that, just pass an aggregate query as the second term of the SHAPE command.

I have tried this using M$ Access, $QL Server, and Oracle 8 without any trouble. I don't know about other databases. Since this functionality is dependent on the ADO provider rather than the RDBMS, I would expect it to work anywhere ADO works.

Source: MSDN, Visual Studio Magazine (April 2002), and a lot of tinkering.

Here's the code:


Private Sub Command1_Click()
    Dim cn          As New ADODB.Connection
    Dim rsInner     As New ADODB.Recordset
    Dim rsOuter     As New ADODB.Recordset
    Dim strSQL      As String
    
    With cn
        .ConnectionString = "Provider=MSDataShape;Data Provider=" & _
              "Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyData\Everything.mdb;" & _ 
              "Persist Security Info=False"
        .CursorLocation = adUseClient
        .Open
    End With
    
    strSQL = "SHAPE {SELECT noderid, nodername"
    strSQL = strSQL & " FROM noders ORDER BY nodername}"
    strSQL = strSQL & " APPEND ({SELECT nodeid, nodedate,"
    strSQL = strSQL & " noderid FROM nodes} AS rsInner"
    strSQL = strSQL & " RELATE noderid TO noderid)"
    
    rsOuter.Open strSQL, cn, adOpenKeyset, adLockOptimistic
    
    With rsOuter
        Set rsInner = .Fields("rsInner").Value
        Do While Not .EOF
            Debug.Print "Orders for NoderId " & .Fields("customerid").Value & _
                " a.k.a. " & .Fields("companyname").Value
            Do While Not rsInner.EOF
                Debug.Print " " & rsInner.Fields("nodeid").Value & " on " & _
                    rsInner.Fields("orderdate")
                rsInner.MoveNext
            Loop
            .MoveNext
        Loop
        rsInner.Close
        .Close
    End With
    
    Set rsInner = Nothing
    Set rsOuter = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Log in or register to write something here or to contact authors.