The idea behind the ADO hierarchial record sets is this.
Say you have customers
and you have Current Orders
and you have Old Orders
You can run a query (odd as it may seem, but very powerful)
SHAPE {SELECT * FROM customers}
APPEND ({SELECT * From orders Where orderdate
#1/1/1996# And customerid = ?}
RELATE customerid TO PARAMETER 0) AS rsOldOrders,
({SELECT * From orders WHERE orderdate >= #1/1/1996#}
RELATE customerid TO customerid) AS rsRecentOrders
What you get back is a single record set, of all the customers like a normal Select *
from Customers. But there is two extra fields RsOldorders, and rsRecentorders. They
are a full record set in their own right. You can then (somehow in delphi) assign the
record set to another recordset and use the normal
while (X.EOF = FALSE) do
....
Movenext
end;
So you might have Customers, and their orders, and each order has another sub record
set, etc..
The whole thing is handled via ADO. Puts things like the BDE to shame, well it is new
technology.
The part I can not get to work in Delphi is assigning the child record set back to a
recordset object.
in VB this is all you need.
Set rsTitle = rsPub!rsOldOrders.Value
Do While Not rsTitle.EOF
List1.AddItem (" " & rsTitle!orderdate)
rsTitle.MoveNext
Loop
rsTitle.Close
here is some VB code which does exactly what I want in Delphi.
Private Sub Command2_Click()
Dim cn As ADODB.Connection, rsPub As ADODB.Recordset, _
rsTitle As ADODB.Recordset, SQL As String, rsRecent As ADODB.Recordset
Set cn = New ADODB.Connection
Set rsPub = New ADODB.Recordset
cn.Provider = "MSDataShape"
cn.Open "dsn=OLE_access_NWIND"
' I have a edit field on a VB form which has this SQL code.
'
' 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
List1.Clear
rsPub.Open Me!Text1.Text, cn, adOpenStatic, adLockReadOnly, adCmdText
Do While Not rsPub.EOF
List1.AddItem ("cUSTOMER " & rsPub!CompanyName)
List1.AddItem ("")
List1.AddItem (" Old Orders")
List1.AddItem (" ==========")
Set rsTitle = rsPub!rsOldOrders.Value
Do While Not rsTitle.EOF
List1.AddItem (" " & rsTitle!orderdate)
rsTitle.MoveNext
Loop
rsTitle.Close
List1.AddItem (" ")
List1.AddItem (" Recent Orders")
List1.AddItem (" =============")
Set rsRecent = rsPub!rsRecentOrders.Value
Do While Not rsRecent.EOF
List1.AddItem (" " & rsRecent!orderdate)
rsRecent.MoveNext
Loop
rsRecent.Close
rsPub.MoveNext
List1.AddItem ("")
Loop
rsPub.Close
cn.Close
Set rsTitle = Nothing
Set rsPub = Nothing
Set cn = Nothing
End Sub
Chris
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
