Most of us are familiar with accessing information stored in relational databases. However, not everything fits readily into a relational model. This is true even for data that is persisted in a traditional relational database – sometimes it would be better to organize data in a hierarchical, rather than relational fashion. This article will describe how this can be accomplished using the MSDataShape provider for OLE DB. We will address the creation of hierarchical recordsets from existing data, and, in addition, we will cover the Shape language, which is an extension to SQL used with the Shape OLE DB provider to create and modify hierarchical recordsets. In the second part of this article, we will address the fabrication of hierarchical recordsets to create complicated data structures.

Why Bother With Hierarchy?

Hierarchical recordsets and the Shape language make sense once you're used to them, but it takes time and effort to learn how to work with them. Hierarchical recordsets are also not suited to every situation. Therefore, we should first describe, in a general way, the problems for which hierarchical recordsets are the appropriate solution.

First and foremost, hierarchical recordsets are useful for modeling parent–child relationships. Consider, for instance, the relationship between customers, orders, and line items in a typical customer database. In order to view a customer's history with our customer support, we might issue a query like this:

select       Customer.CustomerID, & _
             Customer.FirstName, & _
             Customer.LastName, & _
             Incident.IncidentID, & _
             Incident.OpenDate
   from Order left outer join Incident
   on Incident.CustomerID = Customer.CustomerID

This query will return a single recordset, with one row for each order. The customer information is repeated in each row of the recordset, and the order information shows up as NULL for any customers that don't have incidents. This duplication of data is a little wasteful. It would be better if we could organize the orders as a separate recordset, and associate that recordset with a customer record, instead of duplicating data in the single recordset.

Hierarchical recordsets are useful for modeling complicated, hierarchical data structures. This can be especially beneficial when your users need the database functionality on their machines, but don’t want to install a full–blown database application. By fabricating hierarchical recordsets, it is possible to create, modify, and persist this complicated data without accessing any external data source. In part two of this article, we'll touch on using hierarchical recordsets in this way.

Shaping Data

Let's return to the example of the customer database. In order to get the data as a hierarchical recordset, we begin by splitting our query into its constituent parts. Then, in VB or VBScript, we create an ADO connection and recordset object as follows:

  objConn.Open "Provider=MSDataShape;Data Provider=MSDASQL;" & _
                "Server=Simon;User ID=User;" 
   
   objRst.Open "SHAPE {Select CustomerID, FirstName, LastName} " & _
               "AS rstCustomer APPEND ({Select IncidentID, CustomerID, OpenDate} " & _
               "AS rstIncident RELATE CustomerID TO CustomerID) AS rstIncident", & _
               objConn, adOpenStatic, adLockOptimistic

The connection string is important here. In order to use the extended features of Shape, we have to specify the MSDataShape OLE DB provider as shown. Lots of other features in ADO and OLE DB can be left unspecified, to be taken care of "behind the scenes." Here, however, we have to be explicit.

Also, note that Data Shaping requires that a second provider be specified on the connection string. Besides explicitly specifying the MSDataShape provider, you need to specify the data provider property.

Next, take a look at the text in the recordset that we're using. You’ll notice that there are two SQL statements, surrounded by some curly braces. This is a very simple SHAPE command. All SHAPE commands follow this basic syntax.

The SHAPE keyword appears first, followed by the SQL statement that defines the parent recordset. This SQL statement must be enclosed in curly braces. Next, the APPEND keyword appears, followed by the statement that creates the child recordset, contained in parentheses. The child statement consists of another SQL statement, also contained in curly braces, followed by the AS keyword and the alias of the child recordset. This name becomes the name by which we can address the child recordset in the parent recordset's Fields collection, so it's important to name it something intuitive and meaningful. I recommend against using generic names such as rstChild . This can become very confusing, especially when dealing with deep hierarchies or recordsets with multiple children. Also, it's important to note that you can, on purpose or inadvertently, create child recordsets with duplicate names. Generally, this is of concern when reshaping recordsets. (We'll cover reshaping later). When using the DataShape provider, the Connection supports another dynamic parameter, Unique Reshaped Names . By default, this is set to false; so it is possible create recordsets with duplicated names. Set this property to true in the connection string, or through the properties collection, to avoid this.

In general, child recordsets are referred to as chapters. Some documentation shows child recordsets being named with the prefix chp . I think this is a little confusing, since really a chapter is just a recordset nested within another recordset. Therefore, I recommend sticking with the familiar rst prefix. The alias is used to get a reference to the child recordset, nested within the parent, like this code snippet, which gets the first incident for the first customer, if there is one:

If Not rstCustomer.EOF Then
   rstCustomer.MoveFirst
   Set rstChild = rstCustomer.Fields("rstCustomer").Value
   If Not rstChild.EOF Then
      rstChild.MoveFirst
      Response.Write rstChild.Fields("IncidentID").Value
   End If
End If

The RELATE keyword follows the AS keyword and the child recordset's alias. This keyword allows us to specify which columns in the parent and child recordsets ought to be 'joined' together. In a SHAPE query without parameters, like the one that we are working with here, there must be a match between the parent recordset and the child recordset. Records that don’t match don’t show up in the child recordset.

It's important to note that the two fields don't need to have the same name. For instance, the primary key in the Customer table could simply be called ID , and the Incident table could have a foreign key reference to it called CustomerID . As long as their data types match, they could be used to join the two recordsets together.

This is similar to doing an inner join in SQL. I don't recommend using SHAPE as a replacement for inner joins, though – at least, not without a very good reason. The SQL statements in the parent and child are both passed through to the database and executed. The resultsets are then returned to the client cursor engine, which takes care of formatting and combining the resultsets appropriately. In some cases, we might pull a lot of unnecessary data across the wire, and we might overburden our client with a job better handled by the server. Remember that SHAPE , like any other technology, is not appropriate to every task. It's important to think about the problem at hand and determine the right approach.

In the case of the query that we’re working with, however, it is probable that some customers won't have any support incidents opened in their names. We can restrict the amount of data that we request in a variety of ways, as you will see.

Provider Commands

The commands that are between the curly braces are passed on to the database. This means that we aren't limited to placing ad hoc SQL in these commands. For instance, I've found that this is a useful way to use stored procedures in SHAPE commands.

If you’re like me, you don’t like to put straight SQL into your compiled applications. Doing so makes code much harder to maintain, because changes to the underlying data require that we change the SQL and recompile our application. That's no fun. SHAPE does support stored procedures natively, and we'll cover that later in the article when we talk about parameterized SHAPE commands. However, I find the syntax for doing this a little unwieldy, as you will see. We're able to affect a compromise between ad hoc SQL and native SHAPE handling of stored procedures by using the SQL exec keyword. This way, we can still store our SQL code in the database, while avoiding cumbersome SHAPE syntax.

We can change our SHAPE command to use stored procedures this way:

objRst.Open "SHAPE {exec GetCustomer} AS rstCustomer APPEND ({exec GetIncident} " & _
            "AS rstIncident RELATE CustomerID TO CustomerID) AS rstIncident", & _
            objConn, adOpenStatic, adLockOptimistic

If a stored procedure has a parameter list, I prefer to build the SQL string outside of the SHAPE command, and then put it into the SHAPE command like this:

strSQL = "exec GetCustomer CustomerID=" & lngCustomerID

objRst.Open "SHAPE {" & strSQL & "} AS rstCustomer APPEND ({exec GetIncident} " & _
            "AS rstIncident RELATE CustomerID TO CustomerID) AS rstIncident", & _
            objConn, adOpenStatic, adLockOptimistic

The increase in readability and maintainability is, I think, significant.

There are other implications to the fact that everything between the curly braces gets passed to the database. First, SQL contained in a SHAPE command does not necessarily have to return a recordset. In fact, any SQL statement can go in between the curly braces. Even Data Definition Language (DDL), such as DROP TABLE commands, is legal between the curly braces. So, the following are all valid, though somewhat useless, SHAPE commands that return no recordsets:

strSQL = "DROP TABLE Customer"
objRst.Open "SHAPE {" & strSQL "}"

strSQL = " DELETE FROM Incident Where OpenDate < GetDate() "
objRst.Open "SHAPE {" & strSQL "}"

‘ This is less innocuous than the first two, since it will raise an ‘error to the client

strSQL = "RAISEERROR 1000, ‘Raised from A Shape command’"
objRst.Open "SHAPE {" & strSQL "}"

If a SQL statement returns more than one recordset, only the first is actually used. Other recordsets are ignored.

The Compute Clause

The compute clause allows us to perform aggregate functions on columns returned in our SHAPE command. In general, I prefer to do aggregate functions in SQL. However, since the SQL aggregate functions typically return multiple recordsets, this isn’t possible in SHAPE . The SHAPE language provides numerous aggregate functions. The following code performs a Count operation on the IncidentID column, telling us how many incidents have been opened for each customer. Other operations that are possible resemble the aggregate functions found in MS–SQL, such as COUNT , MIN , MAX , and AVG .

objRst.Open  "SHAPE {exec GetIncident} AS Incident "  & _
             "COMPUTE IncidentSum, Count(Incident.IncidentID) BY CustomerID", & _
             objConnection, adOpenStatic, adLockOptimistic

The CALC Clause

The CALC keyword allows us to perform VBA functions on columns returned in the SHAPE command. I have not found this to be very useful, as I prefer to do this kind of operation outside of the recordset object. Most of the VBA functions, such as Pmt , IsNull , and StrComp , are supported.

Reshaping Recordsets

Once a recordset is created, the DataShape service allows us to add it to other recordsets, or address it as though it were not a chapter of a parent recordset. This kind of operation is called reshaping. It can come in very handy if you have a single child recordset that can be used across multiple parents. This way, we only need to obtain the child recordset once, and can recycle it without having to needlessly roundtrip to the server.

Parameterized Shape Commands

I’ve save this for last, because in my opinion parameterized SHAPE commands are the most complicated of the concepts inherent in basic SHAPE syntax. First, an example of parameterized ad hoc SQL. This statement accepts a single parameter, the CustomerID , and uses it to join the recordsets returned from the Customer table to those returned from the Incident table.

objRst.Open "SHAPE {Select CustomerID, FirstName, LastName} " & _
            "AS rstCustomer APPEND ({Select IncidentID, CustomerID, OpenDate where CustomerID = ?} " & _
            "AS rstIncident RELATE CustomerID TO Parameter 0) AS rstIncident", & _
            objConn, adOpenStatic, adLockOptimistic

The question mark represents the parameter, much like the calling syntax used in ODBC. As we step through the recordset, the child SQL is reissued for each row, and only records matching the criteria are returned and added to the child recordset. As you can see, this could rapidly become difficult to hand–edit as the recordsets are nested deeper and parameters are added. You can execute stored procedures this way as well, but the syntax can seem somewhat strange. This code snippet gives an example of what the resulting SHAPE looks like:

strShape = "SHAPE {{? = CALL GetCustomer( ?) }}  " & _
           "AS rstCustomer APPEND ({{? = CALL GetIncident( ?, ?) }}  " & _
           "AS rstIncident RELATE 'CustomerID' TO  PARAMETER 0)"

objRst.Open strShape, objConn,adOpenStatic, adLockOptimistic

The question marks on the inside of parentheses represent parameters passed to the stored procedure. Question marks on the outside of the parameter represent the return value of the stored procedure. You cannot, however, persist a parameterized query with the ADO recordset's Save method, nor can you use it in a disconnected recordset. This is why I prefer to build SQL strings manually and put them into my SHAPE commands with the SQL exec keyword, as I showed you above.

It is possible to use the Data Environment Designer (DED) that ships with VB 6.0 to format the SHAPE statement for you. Although a discussion of the DED is beyond the scope of this article, you should know that it is available. The DED is a good tool for becoming more familiar with SHAPE , but should not be used as a substitute for becoming familiar with SHAPE syntax.

Conclusion

In this article, we have covered some of the more important concepts that surround the Data Shape provider for OLE DB, and we have seen how Data Shaping can be used to work with hierarchical data. In the second half of this article, we will demonstrate how fabricated recordsets can be used to model complicated, hierarchical data structures.