ADO SHAPE Command - Producing Hierarchical Recordsets

In this article, we will see how an ADO Object can produce hierarchical Recordsets – one of the strong features which is not widely used in many real-time applications.

ADO Shape Command:

First of all, I would like to mention that this SHAPE Command is not particular to any of the Query Language (i.e., Oracle, SQL Server or Access). It can work in any of the mentioned databases. It’s a powerful command provided by ADO Object. Main objective of SHAPE command in ADO lets you to create a hierarchical Recordsets.

It's really useful and much better - faster than creating two Recordsets or a big join. Let's assume you have to write a Query that needs to show a Master Record (Let’s say Order) and related Child records (products in that order) - the routine way of doing it in VB/VBA is to,

• Open Order Master as a separate Recordset

• Open Order Details as a separate Recordset based on the Order Master Primary Key field values.

We will be opening 2 Recordset Objects in our code in order to accomplish this task. This example drills up to 2 Levels only ( i.e., Master – Detail ), there will be cases where we will be required to drill down up to 4 or 5 levels, in those cases opening 4 or 5 Recordsets can cause a lot of performance issue in Applications. In order to handle such cases, Hierarchical Recordsets can be used and it’s possible only by using ADO SHAPE Command. Here is how you use it:

Sample Syntax:

SHAPE {SELECT ord_no,cust_name FROM ord_mstr} AS Order APPEND ({SELECT ord_no, item_no FROM ord_details} AS OrderDet RELATE ord_no TO ord_no)

Let us see the usage more in detail with a sample code,

Create a Master Table as,

create table ord_mstr(ord_no int, cust_name varchar(10))

insert into ord_mstr values(100,'name1')

insert into ord_mstr values(200,'name2')

insert into ord_mstr values(300,'name3')

select * from ord_mstr

ord_no          cust_name

-----------    ----------

100                name1

200                name2

300                name3

Create a Detail table as,

create table ord_details(ord_no int, item_no varchar(5))

insert into ord_details values(100,'1itm1')

insert into ord_details values(100,'1itm2')

insert into ord_details values(200,'2itm1')

insert into ord_details values(300,'3itm1')

insert into ord_details values(300,'3itm2')

insert into ord_details values(300,'3itm3')

select * from ord_details

ord_no          item_no

-----------    -------

100                1itm1

100                1itm2

200                2itm1

300                3itm2

300                3itm3

300                3itm1

If you see the table values, ord_no 100 has 2 child records and 200 has 1 child record and 300 has 3 child records. We will see how we can bring this hierarchy in a single SQL statement using ADO SHAPE Command,

Create a New Standard EXE Project and paste the following code,

Dim cn As ADODB.Connection

Dim rsOrd As ADODB.Recordset

Dim rsOrdDet As ADODB.Recordset

Set cn = new ADODB.Connection

Set rsOrd = new ADODB.Recordset

Set rsOrdDet = new ADODB.Recordset

'mention the Provider Name as MSDataShapte

cn.Provider = "MSDataShape"

'specify the DSN Name for DataShape provider

'Here , test ODBC DSN points to a SQL Server Database
cn.Open "dsn=test"

'open the recordset using SHAPE Command containing master-detail set of records

'master records will be from ord_mstr

'detail records will be from ord_detail

'use the RELATE Keyword to relate your ord_mstr and ord_detail table

rsOrd.Open "SHAPE {SELECT ord_no,cust_name FROM ord_mstr} AS Order APPEND ({SELECT ord_no, item_no FROM ord_details} AS OrderDet RELATE ord_no TO ord_no)", cn, adOpenStatic

If rsOrd.RecordCount > 0 Then

rsOrd.MoveFirst

While Not rsOrd.EOF

'display field values from Master Table i.e., ord_mstr

MsgBox "Order No from Master = " & rsOrd.Fields("ord_no")

MsgBox "Customer Name from Master = " & rsOrd.Fields("cust_name")

'child records will be stored inside Master Recordset itself

'i.e., ord_details records will be stored inside ord_mstr recordset

'OrderDet Recordset object will be stored inside rsOrd Recordset

'we dont have to open a seperate recordset for fetching order_details table

Set rsOrdDet = rsOrd.Fields("OrderDet").Value

If rsOrdDet.RecordCount > 0 Then

rsOrdDet.MoveFirst

While Not rsOrdDet.EOF

MsgBox "Order No from Detail = " & rsOrdDet.Fields("ord_no")

MsgBox "Item No from Detail = " & rsOrdDet.Fields("item_no")

rsOrdDet.MoveNext

Wend

End If

rsOrd.MoveNext

Wend

Set cn = Nothing

Set rsOrd = Nothing

Set rsOrdDet = Nothing

End If

If you see the above code, the Master Recordset (rsOrd) itself has the reference for rsOrdDet(Order Details) Recordset. This is the beauty of the SHAPE Command and we can change the Query to drill down up to N-Levels ie., Master – Detail –Detail etc.,

References

Please check the below Microsoft link for more information about this SHAPE Command and how efficiently we can use it in our applications,

http://support.microsoft.com/kb/q189657/

Biography

I am a software developer and have worked on Microsoft technologies for about eight years now. I have always been fascinated by Microsoft technologies and with the advent of .NET , this fascination has reached new heights. I take a lot of interest in reading technical articles and equally enjoy writing them.