Advanced Data Shaping

Mike Gunderloy

Mike Gunderloy provides some examples of ADO’s SHAPE…APPEND statement in action, including how to synchronize child and parent Recordsets. Mike also introduces the SHAPE…COMPUTE statement.

TheSHAPE…APPEND command lets you work with hierarchical data (see my article, "Data Shaping: Handling Non-Relational Data," in the December 1999 issue for the SHAPE…APPEND statement’s syntax). However, it’s hard to understand anything until you see some code. In this month’s article, I’m going to take a look at some examples of SHAPE statements. I’ll then introduce you to the SHAPE…COMPUTE statement.

You’ll find all of these examples in frmExample in the sample database available in the Download file (see also Figure 1). This form contains a set of command buttons, one for each example. When you click a button, the corresponding example is bound to a Hierarchical FlexGrid control on the form that lets you quickly see the Recordset returned by each example. The Recordsets on this form presume that you’ve got SQL Server or MSDE installed locally; if necessary, you can change the connection string in the form’s Load procedure to point to a different data source.

Single-level relation hierarchy

SHAPE {SELECT * FROM Customers} 
APPEND ({SELECT * FROM Orders} 
RELATE CustomerID TO CustomerID)

Multiple-level relation hierarchy

SHAPE {SELECT * FROM Customers} 
APPEND ((SHAPE {SELECT * FROM Orders} 
         APPEND ({SELECT * FROM [Order Details]} 
         AS rstOrderDetails 
         RELATE OrderID TO OrderID)) 
RELATE CustomerID TO CustomerID)

Parameterized hierarchy

SHAPE {SELECT * FROM Customers} 
APPEND ({SELECT * FROM Orders 
WHERE CustomerID = ?} 
RELATE CustomerID TO PARAMETER 0)

Although this Recordset will initially open faster than the equivalent relation hierarchy, moving from record to record is slower. As a result, you won’t see a performance difference between the two on the sample form. That’s because the Hierarchical FlexGrid control moves through all of the rows in the Recordset to populate itself.

Multiple relation hierarchy

SHAPE {SELECT * FROM Customers} 
APPEND({SELECT * FROM Orders WHERE ShippedDate >                               
                                                '1/1/97'}
RELATE CustomerID TO CustomerID) as rstNewOrders,
({SELECT * FROM Orders WHERE ShippedDate <=
                                                '1/1/97'}
RELATE CustomerID TO CustomerID) as rstOldOrders

If you scroll through the resulting Recordset, you’ll see that each parent record is associated with two distinct child Recordsets, one each for new and old orders.

Relation hierarchy with aggregate

SHAPE {SELECT * FROM Customers} 
APPEND ({SELECT *  FROM Orders} 
         RELATE CustomerID TO CustomerID),
MIN(Chapter1.ShippedDate) AS FirstShip

This creates a Recordset with Customer and Order information, plus an additional aggregate column that contains the minimum value from any record in the ShippedDate column for each customer.

SHAPE…COMPUTE

The general syntax of the SHAPE…COMPUTE statement is:

SHAPE {child_command} AS child_alias
COMPUTE child_alias, aggregate_field_list
BY group_field_list

The curly braces around the child_command are required. The child_command can be one of four things:

With SHAPE…COMPUTE, you must supply an alias for the child command after the AS keyword. This alias must be repeated as one of the columns listed in the COMPUTE clause. This alias defines the relation between the child Recordset and the implied parent Recordset.

The group_field_list is optional. If you supply a list of columns here, the parent Recordset is constructed so that each row has unique values in those columns, and the child Recordset is filtered to match. Any columns you list here will become columns in the parent Recordset. With this option, a record is generated in the parent Recordset for each unique combination of the fields that you list. The related records form the child Recordset for this parent record. If you don’t choose to supply a group_field_list, there will be only one row in the parent Recordset, and any aggregates it contains will refer to the entire child Recordset.

The aggregate_field_list is also optional. If you supply a list here, it must be composed of aggregate functions performed on fields in the child Recordset. Each entry in this list defines a column in the generated parent Recordset. Effectively, each row in the parent Recordset will represent a summary result generated from its child Recordset.

Aggregate functions

Customers.Orders.OrderDetails.Quantity

Table 1 shows the aggregate functions that are available in the SHAPE syntax.

Table 1. Aggregate functions supported in SHAPE.

Calculated expressions

So what does that all mean? Here’s an example of a grouping hierarchy that shows detail and aggregated information. The SHAPE…COMPUTE statement creates the parent Recordset from the child Recordset:

SHAPE {SELECT Customers.CustomerID AS CustID, 
       Customers.CompanyName, Orders.* 
       FROM Customers INNER JOIN Orders 
       ON Customers.CustomerID = Orders.CustomerID} 
       AS rstOrders 
COMPUTE rstOrders 
BY CustID, CompanyName

You’ll see if you run this example that the statement creates a parent Recordset that contains the CustID and CompanyName fields. The parent Recordset has one record for each unique combination of CustID and CompanyName in rstOrders. The child Recordset for each parent consists of the records with matching CustID and CompanyNames (the CustID and CompanyName fields are both repeated in the child Recordset).

Table 2. VBA functions available to CALC.

Synchronizing Recordsets

For a demonstration of this property, take a look at frmSync in the sample database. This form simply fetches the same Recordset twice, once synchronized and once unsynchronized, and dumps results to the Immediate Window. The code begins by creating a Recordset using SHAPE…APPEND, and then retrieves the child Recordset from the field that it’s stored in:

rstP.StayInSync = False
rstP.Open "SHAPE {" & _
     "SELECT CustomerID, CompanyName " & _
     "FROM Customers} APPEND ({SELECT " & _
     "CustomerID, OrderDate " & _
     "FROM Orders} RELATE CustomerID " & _
     "TO CustomerID)", cnn
    
Set rstC = rstP.Fields("Chapter1").Value

Now that I have two Recordsets, I can use the standard Recordset commands to move through the parent’s records and display a field from the parent and the child:

Debug.Print "Parent:" & rstP.Fields("CustomerID")
Debug.Print "Child:" & rstC.Fields("CustomerID")
Debug.Print "Executing MoveNext"
rstP.MoveNext
Debug.Print "Parent:" & rstP.Fields("CustomerID")
Debug.Print "Child:" & rstC.Fields("CustomerID")

The results are shown below. As you can see, while the code moves to the next record in the parent Recordset, the child Recordset is unaffected and remains on the first record in the first Chapter:

Parent:ALFKI
Child:ALFKI
Executing MoveNext
Parent:ANATR
Child:ALFKI

However, if I set the StayInSync property to True, like this, I get different results:

rstP.StayInSync = True

Now, when I move to the next record in the parent Recordset, the child Recordset is automatically set to the corresponding Chapter:

Parent:ALFKI
Child:ALFKI
Executing MoveNext
Parent:ANATR
Child:ANATR

A warning: When you’re fetching a child Recordset, you must use the Value property of the chapter field in the parent Recordset, as shown in the preceding code sample. If you try to omit this property, you’ll get a type mismatch error, even though Value is the default property.

As you can see, the SHAPE syntax gives you a powerful new way to link Recordsets together. Wherever you have a one-to-many relationship in your data, SHAPE…APPEND (and its sibling SHAPE…COMPUTE) can let you represent data in its natural format. The result can be simpler, easier to maintain, and (as a result) more bug-free code.

(Excerpted from Mike Gunderloy’s Visual Basic Developer’s Guide to ADO by permission of Sybex, Inc., ISBN 0-7821-2556-5. Copyright © 1999, Sybex, Inc. , 1151 Marina Village Parkway, Alameda, CA 94501. All rights reserved. For further information please contact info@sybex.com or 1-800-227-2346. Additional information may be obtained on the Sybex Web Site (http://www.sybex.com). No part of this article may be reproduced in any way without the prior written agreement and permission of Sybex, Inc.)

Download SHAPE.ZIP

Mike Gunderloy is a senior consultant with MCW Technologies, a Microsoft Solution Provider. His Visual Basic Developer’s Guide to ADO was recently published by Sybex, and he’s currently at work on Mastering SQL Server 7.5. When he’s not dealing with his computer, Mike is managing a horse, two llamas, three sheep, two dogs, seven cats, and numerous chickens and guinea fowl on his farm. MikeG1@mcwtech.com.

Sidebar: Foundation Concepts: Consumers, Providers, and Service Providers

OLE DB, on which ADO rests, provides a set of objects that allow you to work with data. OLE DB divides these objects into three groups: consumers, providers, and service providers. Consumers are objects or applications that use data provided by the other two kinds of objects. A provider is an object that can extract data from a data source and return it in the standard OLE DB format. A service provider can’t extract data, but, like a consumer, it can accept data from a provider. A service provider, unlike a straight consumer, also manipulates the data and can pass it on to a consumer or another service provider. Since a service provider can work with any ADO provider, service providers can be very flexible. Microsoft’s search engine, Index Server, has a data provider that you can use to extract data from the search engine’s catalog (for example "Find me all of the documents containing the words ‘Smart Access’"). Since the Index Server data provider returns data in the standard ADO format, you can use the Data Shaping server to manipulate that data. All of these features are available from any development tool that can work with objects, including Access 97 and Access 2000.