Functionality and Syntax

[Previous] [Next]

Hierarchical recordsets are powerful and flexible. While I'll include examples of some of the syntax options shortly, they will not be exhaustive by any means. The documentation on the SHAPE syntax was initially lacking but has improved in the MDAC 2.1 and MDAC 2.5 releases. Take a look at the documentation in the Data Access SDK and the online Knowledge Base at the Microsoft Web site (http://support.microsoft.com/search) for more information.

The Data Environment Designer

The Data Environment designer included with Visual Basic 6 and Microsoft Office 2000 Developer Edition is a helpful tool for building hierarchical queries.

I don't actually use the Data Environment designer in the applications I build. But if I ever need to generate a hierarchical query, I build it using the Data Environment designer and then look at the query string the designer generated. Once I realized I could right-click the top level of the hierarchical query in the Data Environment designer and see the syntax it generated, I decided I didn't need to learn the syntax by heart.

Reshaping

Earlier, you learned that different internal structures contain different levels of standard hierarchies. In the customers and orders standard hierarchy example, all the orders are kept in the same structure. Starting with version 2.1 of ADO, you can access the different levels of the hierarchy as though they were separate Recordset objects.

If you use the following code to open your customers and orders hierarchy

 strSQL = "SHAPE {SELECT * FROM Customers} AS Customers " & _ "APPEND ({SELECT * FROM Orders} AS Orders " & _ "RELATE CustomerID TO CustomerID) AS Orders" Set rsCustomers = New ADODB.Recordset rsCustomers.CursorLocation = adUseClient rsCustomers.Open strSQL, cnNorthwind, adOpenStatic, _ adLockBatchOptimistic, adCmdText Set rsOrders = rsCustomers.Fields("Orders").Value 

the rsOrders Recordset will contain the orders for the first customer in the rsCustomers Recordset.

But if you change the code slightly, you can view orders in the rsOrders Recordset for all the customers. Rather than set the rsOrders Recordset to the Orders field in the rsCustomers Recordset, you can use the following code:

 Set rsOrders = New ADODB.Recordset rsOrders.CursorLocation = adUseClient rsOrders.Open "SHAPE Orders", cnNorthwind, adOpenStatic, _ adLockBatchOptimistic, adCmdText 

Note the syntax. Use the SHAPE keyword, and specify the name of the hierarchy level you want to examine. This is the same name you specified in the AS clause of the query string you used to generate the hierarchy.

Reshaping the data this way provides additional means of referencing the same data structures. If you were to use the previous code snippet to view all orders and then you change a value in a field in the rsOrders Recordset, you would see that change in the hierarchy as well.

Hierarchy Examples

Let's build a fairly large hierarchy by following a series of steps. To save space and make the query readable, we'll mainly use the concise but lazy SELECT * FROM full-table queries.

Basic hierarchical query

Start by retrieving the orders for a particular customer:

 SELECT * FROM Orders WHERE CustomerID = 'ALFKI' 

Now add the order details in a parameterized hierarchy, but only for the returned orders. Use a parameterized query here so that you don't retrieve the order details for orders that aren't in the parent level of the Recordset. (You could do that in a nonparameterized query as described earlier, but it's worth seeing the parameterized query syntax in this large example.) Then define a calculated field that contains the cost of that particular order item. Here's the simple (nonhierarchical) parameterized query to retrieve this data:

 SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ? 

When you add this query to a hierarchical query, you get the following:

 SHAPE {SELECT * FROM Orders WHERE CustomerID = 'ALFKI'} AS Orders APPEND ({SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ?} AS OrderDetails RELATE OrderID TO PARAMETER 0) AS OrderDetails 

The SHAPE keyword lets the data shape provider know that this is a hierarchical query. Enclose the parent level of the hierarchy in curly braces, and then name that hierarchy level (Orders) with the AS clause. Use this name whenever you want to reshape the hierarchy. The APPEND keyword notes that you're adding a field to the Recordset. In this case, the new field contains the order details. You need to surround the definition of this field with parentheses.

The first step in defining this field is to enter the query that defines the data and enclose it in braces. Name the hierarchy level—in this case, OrderDetails—with the AS keyword. Use the RELATE keyword to define the relationship between the parent and child levels of the hierarchy, with the field from the parent query listed first. You use PARAMETER 0 here rather than a field from the child query because you're using a parameterized query. That's the end of the definition for the field added by the APPEND keyword, so you should add the closing parenthesis and name the field with the AS clause.

Now that we've covered most of the pieces of the SHAPE query, let's add more levels, one at a time, and show the new query in each case.

Adding fields

In the OrderDetails level of the hierarchy, there's a field that contains the total cost (Quantity * UnitPrice) of each order. Add a field to the Orders level of the hierarchy that contains the total cost of all the orders for this customer. You can also add a field that contains the number of orders for this customer. Your new hierarchical query should look like this:

 SHAPE {SELECT * FROM Orders WHERE CustomerID = 'ALFKI'} AS Orders APPEND ({SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ?} AS OrderDetails RELATE OrderID TO PARAMETER 0) AS OrderDetails, SUM(OrderDetails.ItemTotal) AS OrderTotal, COUNT(OrderDetails.OrderID) AS NumItems 

The syntax for these new fields is straightforward. For each function (SUM and COUNT), specify the function name and include the field on which to base the calculation as the parameter. This parameter contains the name of the hierarchy level as well as the field in that level.

Adding a sibling

Now let's add a field to the Orders level of the hierarchy that contains information about the shipping company used for this order. This is considered a sibling chapter to the order details data because it's at the same level in the hierarchy. Your hierarchical query now looks like this:

 SHAPE {SELECT * FROM Orders WHERE CustomerID = 'ALFKI'} AS Orders APPEND ({SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ?} AS OrderDetails RELATE OrderID TO PARAMETER 0) AS OrderDetails, SUM(OrderDetails.ItemTotal) AS OrderTotal, COUNT(OrderDetails.OrderID) AS NumItems, ({SELECT * FROM Shippers} AS Shipper RELATE ShipVia TO ShipperID) AS Shipper 

Note that this syntax is similar to the syntax you used earlier when adding order details. In the RELATE clause of the Shipper hierarchical level, the field names aren't the same. In this case, that's because the Orders and Shippers tables have different names for fields that contain the same data. The name of the field (ShipVia) in the parent level (Orders) of the relationship is on the left side of the TO clause, while the name of the field (ShipperID) in the child level (Shipper) is on the right.

Grouping data

Now let's take this hierarchy and group the data by employee. This grouping will partition the order details into sections—each group of orders placed by a particular employee:

 SHAPE (SHAPE {SELECT * FROM Orders WHERE CustomerID = 'ALFKI'} AS Orders APPEND ({SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ?} AS OrderDetails RELATE OrderID TO PARAMETER 0) AS OrderDetails, SUM(OrderDetails.ItemTotal) AS OrderTotal, COUNT(OrderDetails.OrderID) AS NumItems, ({SELECT * FROM Shippers} AS Shipper RELATE ShipVia TO ShipperID) AS Shipper) AS Orders COMPUTE Orders BY EmployeeID 

In this code, you added another SHAPE keyword, enclosed the entire query in parentheses, and then named the field that contains the data—in this case, Orders. Then you entered the COMPUTE keyword to specify that you plan to group the data; you specified the name of the hierarchy level that you'll perform the grouping on, followed by the BY keyword; and finally you entered the name of the field on which to group.

Adding fields to groups

Let's complete this hierarchy by displaying the number of orders for each employee and the total cost of the orders each employee entered into the system:

 SHAPE (SHAPE {SELECT * FROM Orders WHERE CustomerID = 'ALFKI'} AS Orders APPEND ({SELECT [Order Details].*, Quantity * UnitPrice AS ItemTotal FROM [Order Details] WHERE OrderID = ?} AS OrderDetails RELATE OrderID TO PARAMETER 0) AS OrderDetails, SUM(OrderDetails.ItemTotal) AS OrderTotal, COUNT(OrderDetails.OrderID) AS NumItems, ({SELECT * FROM Shippers} AS Shipper RELATE ShipVia TO ShipperID) AS Shipper) COMPUTE Orders, COUNT(Orders.OrderID) AS NumOrders, SUM(Orders.OrderTotal) AS EmployeeTotal BY EmployeeID 

Whew. In Appendix B, we'll discuss the Huge Hierarchy sample that further builds on this hierarchy to display product information for each line item in each order. We'll also add information about the employees (such as name and title) to the hierarchy.

Adding blank fields

Another feature of the data shape provider is that you can add blank fields to a query. For example, say you wanted to add a Comments field to your customers query that doesn't correspond to a field in the Customers table. What I like about this feature is that you can modify the contents of this field and the data shape provider will ignore these changes. You can use the field like a scratch pad, knowing that ADO won't try to force those changes into your database. Here's an example of the syntax:

 SHAPE {SELECT * FROM Customers} AS Customers APPEND NEW adVarChar(64) AS Comments 

You specify the NEW keyword to tell the data shape provider to add a blank field. Then you state the data type for the field and use the AS keyword to name it.

You can even build a hierarchy without a connection to a database. Here's a hierarchy that uses the NEW keyword to create blank fields in both a parent level and a child level:

 SHAPE APPEND NEW adInteger AS ParentID, NEW adVarChar(32) As ParentName, ((SHAPE APPEND NEW adInteger AS ChildID, NEW adInteger AS ParentID, NEW adVarChar(32) AS ChildName) RELATE ParentID TO ParentID) AS Child 

Note that the child query is enclosed in parentheses rather than curly braces.

Working without a connection

How do you use the data shape provider in your connection string without connecting to your database? Set the Data Provider argument of the connection string to None, as shown here:

 Provider=MSDataShape;Data Provider=None;