��. ��: 189657 - �� : ��, 18 �� 2010 - ��: 2.0

�� SHAPE ADO

�� . �� .

�� -�� , �� JOIN �� GROUP BY.

�� : Xbase ��, �� SET �׸��, � Access �� "�� Virtual ��" �� , �� . �� , �� . �� , �� .

�� MSDataShape, �� -��.

�� SQLJOINANDGROUP BY�� JOIN, �� . �� . ��, �� recordset �� , �� recordset �� .

�� GROUP BY�� , �� . �� , �� .

�� . 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 ofSHAPE�� :

Relation Based
Parameter Based
Group Based

The first two are similar in that they produce a hierarchy that would otherwise be represented by a SQLJOINstatement. 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 theSHAPEclause. Simplified syntax is provided first, then examples with diagrams. Because theSHAPEsyntax 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 ownSHAPEstatements. 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]

��

��:

If you select identically named fields from different tables, you might need to alias them to ensure that theSHAPEparser works.
�� SHAPE APPENDfunctions similarly to anOUTER JOINin that a parent record is returned, even if no child records exist for it.
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.
If you use an aggregate function with theSHAPE APPENDsyntax, the aggregate value will occupy a field appended to the parent resultset, which also contains the fields from the parent statement. In contrast, theSHAPE COMPUTEANDSHAPE BYcreate a new parent level for the aggregates and the non-normalized-statement becomes the child recordset.
�� SHAPEprovider requires that you include Alias for the non-normalized-statement inCOMPUTEclause when usingSHAPE 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:

��: TheinnerSHAPE 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:

   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:

   rsCustSummary
        |
        +-----Contact
              Region
              CustTotal
              rsDetail
                   |
                   +----Customers.*
                        ExtendedPrice
              customerid
   Region

Grand Total:

   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 missingBYclause 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.

   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.

   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:

rsDetail
        |
        +----Customers.*
   Company
   rsOrders
        |
        +----Orders.*
   OrderCount
   customerid

Grouped Parent Related to Grouped Child:

   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:

   rsCustomers
        |
        +-----customers.*
              rsOrdByMonth
                 |
                 +-----rsOrders
                            |
                            +---- Orders.*
                       customerid
                       OrderYear
                       OrderMonth
   region

��

SHAPE Clause Formal Grammar

  <shape-command>      ::=  SHAPE <table-exp> [AS <alias>]
                            [<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 <alias>]
 
  <field-exp>          ::=  ( <relation-exp> ) | <calculated-exp>
 
  <relation_exp>       ::=   <table-exp> [AS <alias>] 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>          ::=   <name> | <number>
 
  <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 (<alias>)
                             | SDEV (<qualified-field-name>)
                             | ANY (<qualified-field-name>)
                             | CALC (<expression>)
 
  <qualified-field-name>::=  <alias>.<field-name> | <field-name>
 
  <alias>               ::=  <quoted-name>
 
  <field-name>          ::=  <quoted-name>
 
  <quoted-name>         ::=  "<string>" | '<string>' | <name>
 
  <name>                ::=  alpha [ alpha | digit | _ | # ...]
 
  <number>              ::=  digit [digit...]
 
  <string>              ::=  unicode-char [unicode-char...]
 
  <expression>          ::=  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 ownSHAPEcommand and display the field hierarchy or indicate the location of the syntax error.

��: 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.

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.
Create a new project. Add two text boxes (Text1, and Text2) and a command button (Command1).

Make both textboxes large enough to display several lines of text and set the following properties:

      Multiline: True                (Visual Basic only)
      Scrollbars: Vertical
      Font: Courier New 10 Point

�� �� ��, ����and add a reference to Microsoft ActiveX Data Objects Library.

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

� �� . Type theSHAPEcommand into Text1, click the command button, and the hierarchy appears in Text2.

��: 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"

��: 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.

��

ADO 2.0 Hierarchical Cursor Specification

For additional information aboutSHAPE APPENDsyntax and how to traverse hierarchical recordsets, please see the following article in the Microsoft Knowledge Base:

185425� (http://support.microsoft.com/kb/185425/EN-US/ ) ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java

��

�� :

Microsoft ActiveX Data Objects 2.7

��

kbdatabase kbhowto kbprovider kbmt KB189657 KbMtel

��

��: �� Microsoft �� . � Microsoft �� . ��, �� . �� , �� , �� . � Microsoft �� , �� . ��, � Microsoft �� .

� �� :189657� (http://support.microsoft.com/kb/189657/en-us/ )

��

�� (KB) ��

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

��

T��

��

United States (English)

This site in other countries/regions:

�� SHAPE ADO

��

��

��

Simplified Syntax

��:

��

Simple Relation Hierarchy:

Parameterized Hierarchy:

Compound Relation Hierarchy:

Multiple Relation Hierarchy:

Hierarchy with Aggregate:

Group Hierarchy:

Group Hierarchy with Aggregate:

Multiple Groupings:

Grand Total:

Complex Hierarchy:

Grouped Parent Related to Grouped Child:

SHAPE Clause Formal Grammar

VBA SHAPE Test Program

��

�� :

��-��:�

T��

��

��

This site in other countries/regions:

������ ������ ��� ������� SHAPE ADO

�� ����� �� ������

��������

������������ �����������

Simplified Syntax

����������:

������������

Simple Relation Hierarchy:

Parameterized Hierarchy:

Compound Relation Hierarchy:

Multiple Relation Hierarchy:

Hierarchy with Aggregate:

Group Hierarchy:

Group Hierarchy with Aggregate:

Multiple Groupings:

Grand Total:

Complex Hierarchy:

Grouped Parent Related to Grouped Child:

SHAPE Clause Formal Grammar

VBA SHAPE Test Program

��������

�� ����������� �� ���� �� ����� ������� ���:

������-�������:�

T��������� �����������

���������

����������� ������

�� SHAPE ADO

��

��

��

��:

��

��

�� :

��-��:�

T��

��

��