Microsoft KB Archive/189657

= How To Use the ADO SHAPE Command =

Article ID: 189657

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q189657



SUMMARY
This article describes the ADO SHAPE command syntax for producing hierarchical recordsets, and explains how to traverse hierarchical recordsets. VBA sample code is also provided.



MORE INFORMATION
You can use hierarchical recordsets as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data.

Hierarchical recordsets are used in many products: Xbase products use the SET RELATION command, Access uses "Segmented Virtual Tables" internally for reports with grouping levels, and so forth. Hierarchies give you the ability to build one or several recordsets, define groupings, and specify aggregate calculations over child recordsets. Although you could implement similar functionality through code, this functionality shifts much of the mundane work from the developer to the system.

Hierarchical recordsets are available through the MSDataShape provider, which is implemented by the client cursor engine.

Hierarchical recordsets differ from SQL JOIN and GROUP BY statements in that with a JOIN, both the parent table fields and child table fields are represented in the same recordset. With a hierarchical recordset, the recordset contains only fields from the parent table. In addition, the recordset contains an extra field that represents the related child data,which you can assign to a second recordset variable and traverse.

When you are performing aggregate functions using GROUP BY and aggregate operators, only aggregate values appear in the recordset. With hierarchical recordsets, the aggregate values are represented in the parent recordset and the detail records are in the child recordset.

You can create three types of shapes, and each type has its own strengths and weaknesses. You need to choose the mechanism that best fits the needs of your application and the environment you will be running your application in. The types of SHAPE are as follows:
 * Relation Based
 * Parameter Based
 * Group Based

The first two are similar in that they produce a hierarchy that would otherwise be represented by a SQL JOIN statement. They differ in that all the parent and child records are read into a local cache before any processing continues in the relation-based hierarchy. This type of hierarchy has a high initial overhead when retrieving the records, but the overhead is low after the initial retrieval.

Initially, parameter-based hierarchies only read the parent records and fetch the child records on demand. Though the initial overhead is reduced, you must issue a new child query for each parent record that is accessed, and you must maintain the connection to the datasource for as long as the recordset is open.

The group-based hierarchy is equivalent to producing an aggregate SQL statement joined to a detail SQL statement or performing aggregate functions on non-normalized data. You cannot update the summary columns and calculated columns because they might be derived from more than one record. Like relation-based hierarchies, all records must be read up front.

Hierarchical recordsets are made available by the SHAPE clause. Simplified syntax is provided first, then examples with diagrams. Because the SHAPE syntax can get quite complex, the formal grammar for the SHAPE clause is provided at the end of the article to allow you to extend the examples. You can also use the program at the end of this article to test your own SHAPE statements. The examples use tables from the Northwind sample database.

Simplified Syntax
SHAPE {parent-statement} APPEND Aggregate | ({child-statement} [As Alias]         RELATE parent-field TO child-field | parameter-marker                 [, parent-field TO child-field | parameter-marker ...]) [, Aggregate | ({child statement})...]

SHAPE {non-normalized-statement} [AS Alias] COMPUTE Aggregate | Alias | ({child-statement} [As Alias] RELATE parent-field TO                                       child-field | parameter-marker) [, Aggregate | Alias | ({child-statement}...)] [BY grouping-field [, grouping-field]]

SHAPE {non-normalized-statement} [AS Alias] BY grouping-field [, grouping-field]

NOTES:

 * 1) If you select identically named fields from different tables, you might need to alias them to ensure that the SHAPE parser works.
 * 2) The SHAPE APPEND functions similarly to an OUTER JOIN in that a parent record is returned, even if no child records exist for it.
 * 3) Aggregates can only operate on fields in the immediate children of the recordset. To operate on fields in grandchildren, and so forth, you must produce intermediate aggregates. See the Group Hierarchy with Aggregate example at the end of this article for an illustration.
 * 4) If you use an aggregate function with the SHAPE APPEND syntax, the aggregate value will occupy a field appended to the parent resultset, which also contains the fields from the parent statement. In contrast, the SHAPE COMPUTE and SHAPE BY create a new parent level for the aggregates and the non-normalized-statement becomes the child recordset.
 * 5) The SHAPE provider requires that you include Alias for the non-normalized-statement in COMPUTE clause when using SHAPE COMPUTE. Failure to do so results in a message that the functionality is not supported, even though it doesn't result in a syntax error.

Simple Relation Hierarchy:
  SHAPE  {select * from customers} APPEND ({select * from orders} AS rsOrders          RELATE customerid TO customerid) which yields:   Customers.* rsOrders |       +Orders.* In the previous diagram, the parent recordset contains all fields from the Customers table and a field called rsOrders. rsOrders provides a reference to the child recordset, and contains all the fields from the Orders table. The other examples use a similar notation.

Parameterized Hierarchy:
  SHAPE  {select * from customers} APPEND ({select * from orders where customerid = ?} AS rsOrders          RELATE customerid TO PARAMETER 0) This results in the same hierarchy as the simple relation hierarchy.

Compound Relation Hierarchy:
This sample illustrates a three-level hierarchy of customers, orders, and order details:   SHAPE  {SELECT * from customers} APPEND ((SHAPE {select * from orders} APPEND ({select * from [order details]} AS rsDetails                  RELATE orderid TO orderid)) AS rsOrders          RELATE customerid TO customerid) which yields:   Customers.* rsOrders |       +Orders.* rsDetails |                +[Order Details].*

Multiple Relation Hierarchy:
This sample illustrates a hierarchy involving a parent recordset and two child recordsets, one of which is parameterized:   SHAPE  {SELECT * FROM customers} APPEND ({SELECT *           FROM orders            WHERE orderdate < #1/1/1998# AND customerid = ?}            RELATE customerid TO PARAMETER 0) AS rsOldOrders, ({SELECT *           FROM orders            WHERE orderdate >= #1/1/1998#}            RELATE customerid TO customerid) AS rsRecentOrders which yields:   Customers.* rsOldOrders |       +Orders.* rsRecentOrders |       +Orders.*

Hierarchy with Aggregate:
  SHAPE  (select * from orders}   APPEND ({select od.orderid, od.UnitPrice * od.quantity as ExtendedPrice from [order details] As od} RELATE orderid TO orderid) As rsDetails,         SUM(ExtendedPrice) AS OrderTotal which yields:    Orders.*   rsDetails       |       +orderid            ExtendedPrice   OrderTotal

Group Hierarchy:
  SHAPE  {select customers.customerid AS cust_id, orders.* from customers inner join orders on customers.customerid = orders.customerid} AS rsOrders COMPUTE rsOrders BY cust_id which yields:   rsOrders |      +cust_id Orders.* cust_id

Group Hierarchy with Aggregate:
NOTE: The inner SHAPE clause in this example is identical to the statement used in the Hierarchy with Aggregate example.   SHAPE (SHAPE  {select customers.*, orders.orderid, orders.orderdate                     from customers inner join orders                     on customers.customerid = orders.customerid}            APPEND  ({select od.orderid, od.unitprice * od.quantity as ExtendedPrice from [order details] as od} AS rsDetails RELATE orderid TO orderid),                   SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS rsOrders COMPUTE rsOrders, SUM(rsOrders.OrderTotal) AS CustTotal, ANY(rsOrders.contactname) AS Contact BY      customerid which yields:   rsOrders |       +Customers.* orderid orderdate rsDetails |                 +orderid ExtendedPrice OrderTotal CustomerTotal Contact customerid

Multiple Groupings:
<pre class="fixed_text">  SHAPE (SHAPE {select customers.*,                          od.unitprice * od.quantity as ExtendedPrice                   from (customers inner join orders on customers.customerid = orders.customerid) inner join                  [order details] as od on orders.orderid = od.orderid}                          AS rsDetail           COMPUTE ANY(rsDetail.contactname) AS Contact,                   ANY(rsDetail.region) AS Region,                   SUM(rsDetail.ExtendedPrice) AS CustTotal,                   rsDetail           BY customerid) AS rsCustSummary COMPUTE rsCustSummary BY     Region which yields: <pre class="fixed_text">  rsCustSummary |       +-Contact Region CustTotal rsDetail |                  +Customers.* ExtendedPrice customerid Region

Grand Total:
<pre class="fixed_text">  SHAPE (SHAPE {select customers.*,                          od.unitprice * od.quantity as ExtendedPrice                   from (customers inner join orders on customers.customerid = orders.customerid) inner join                  [order details] as od on orders.orderid = od.orderid}                          AS rsDetail           COMPUTE ANY(rsDetail.contactname) AS Contact,                   SUM(rsDetail.ExtendedPrice) AS CustTotal,                   rsDetail           BY customerid) AS rsCustSummary COMPUTE SUM(rsCustSummary.CustTotal) As GrandTotal, rsCustSummary Note the missing BY clause in the outer summary. This defines the Grand Total because the parent rowset contains a single record with the grand total and a pointer to the child recordset. <pre class="fixed_text">  GrandTotal rsCustSummary |       +-Contact CustTotal rsDetail |                 +Customers.* ExtendedPrice customerid

Complex Hierarchy:
This example illustrates a hierarchy that contains one parent rowset, two child rowsets, one of which is parameterized, and a group detail. <pre class="fixed_text">  SHAPE {select customers.* from customers} AS rsDetail COMPUTE rsDetail, ANY(rsDetail.companyname) AS Company, ({select * from orders where customerid = ?}                  RELATE customerid TO PARAMETER 0) AS rsOrders, COUNT(rsOrders.orderid) AS OrderCount BY customerid which yields: <pre class="fixed_text">rsDetail |       +Customers.* Company rsOrders |       +Orders.* OrderCount customerid

Grouped Parent Related to Grouped Child:
<pre class="fixed_text">  SHAPE (SHAPE {select * from customers}           APPEND ((SHAPE {select orders.*, year(orderdate) as OrderYear,                                  month(orderdate) as OrderMonth                           from orders} AS rsOrders                    COMPUTE rsOrders                    BY customerid, OrderYear, OrderMonth) RELATE customerid TO customerid) AS rsOrdByMonth ) AS rsCustomers COMPUTE rsCustomers BY     region which yields: <pre class="fixed_text">  rsCustomers |       +-customers.* rsOrdByMonth |                +-rsOrders |                           + Orders.* customerid OrderYear OrderMonth region

SHAPE Clause Formal Grammar
<pre class="fixed_text"> <shape-command>      ::=  SHAPE <table-exp> [AS ] [<shape_action>]

<shape-action>      ::=  APPEND <aliased-field-list> | COMPUTE <aliased-field-list> [BY <field-list>] | BY <field-list>

<table-exp>         ::=  {<native-sql-statement>} | ( <shape-command> ) <aliased-field-list> ::= <aliased-field> [, <aliased-field...] <aliased-field>     ::=  <field-exp> [AS ] <field-exp>         ::=  ( <relation-exp> ) | <calculated-exp> <relation_exp>      ::=   <table-exp> [AS ] RELATE <relation-cond-list> <relation-cond-list> ::=  <relation-cond> [, <relation-cond>...] <relation-cond>     ::=   <field-name> TO <child-ref> <child-ref>         ::=   <field-name> | PARAMETER <param-ref> <param-ref>         ::=   | <field-list>        ::=   <field-name [, <filed-name>] <calculated-exp>    ::=   SUM (<qualified-field-name>) | AVG (<qualified-field-name>) | MIN (<qualified-field-name>) | MAX (<qualified-field-name>) | COUNT | SDEV (<qualified-field-name>) | ANY (<qualified-field-name>) | CALC <qualified-field-name>::= .<field-name> | <field-name> ::= <quoted-name> <field-name>         ::=  <quoted-name> <quoted-name>        ::=  " " | ' ' | ::= alpha [ alpha | digit | _ | # ...] ::= digit [digit...] ::= unicode-char [unicode-char...] ::= an expression recognized by the Jet Expression service whose operands are other non-CALC columns in the same row.

VBA SHAPE Test Program
The following VBA program code enables you type in your own SHAPE command and display the field hierarchy or indicate the location of the syntax error.

WARNING: USE THE CODE PROVIDED IN THIS ARTICLE AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. <ol> <li>In the ODBC Administrator of the Control Panel add a DSN for the Microsoft Access 97 ODBC driver called OLE_DB_NWIND_JET pointing to the Northwind (or NWIND) database.</li> <li>Create a new project. Add two text boxes (Text1, and Text2) and a command button (Command1).</li> <li> Make both textboxes large enough to display several lines of text and set the following properties: <pre class="fixed_text">     Multiline: True                (Visual Basic only) Scrollbars: Vertical Font: Courier New 10 Point </li> <li>On the Project menu, choose References and add a reference to Microsoft ActiveX Data Objects Library.</li> <li> Add the following code: Private Sub Command1_Click Dim cn As ADODB.Connection, rs As ADODB.Recordset Me!Text2.Text = "" Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Provider = "MSDataShape" cn.Open "dsn=OLE_DB_NWIND_JET" On Error Resume Next rs.Open Me!Text1.Text, cn, adOpenStatic, adLockReadOnly, adCmdText If Err Then MsgBox Error ListChapteredFields rs, 0 rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub

Private Sub LogText(ByVal sLine As String) If Me!Text2.Text = "" Then Me!Text2.Text = sLine Else Me!Text2.Text = Me!Text2.Text & vbCrLf & sLine End If  End Sub

Private Sub ListChapteredFields(ByVal rs As ADODB.Recordset, _                                     ByVal Level As Long) Dim I As Long For I = 0 To rs.Fields.Count - 1 LogText Space$(Level * 3) & rs(I).Name If rs(I).Type = adChapter Then ListChapteredFields rs(I).Value, Level + 1 End If    Next I   End Sub </li> <li>Run the project. Type the SHAPE command into Text1, click the command button, and the hierarchy appears in Text2.</li></ol>

NOTE: The following text provides an example of using the Microsoft Jet OLEDB provider with the SHAPE provider: cn.Provider = "MSDataShape" cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0" NOTE: If you misspell field or table names when using the Access 97 ODBC driver or JOLT providers, you will receive the following message:

Too few parameters. Expected n.

Other providers might produce a different message.

<div class="references_section">