Hi Wilfred, long time no see.
When I was young, in the mists of time (and about when you were conceived), there was
a battle between hierarchical databases, network databases and relational databases.
They each had very significant advantages and very significant disadvantages.
The serious gotchas for relational databases were that they were so slow and the
presentation of data was a flat 2D structure that was clumsy to program against. In
spite of that they won the race and virtually every DB is now a relational database.
The ODBMS developments are an attempt to recapture that structure.
When you design your tables, then Codd's rules are great. But when you access the DB,
use a shape statement rather than a flat join. I think you will be very pleasantly
surprised how much code it saves you.
In my opinion it is a great leap forward (or is it back) !
-----Original Message-----
From: Wilfred Verkley [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 04, 1999 11:52 AM
To: Multiple recipients of list delphi
Subject: RE: [DUG]: ADO Hierarchial Record Sets
Does anyone remember the 1rst Normal Form rule? If Codd were dead, he would
be turning in his grave.
-----Original Message-----
From: Chris Crowe [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 3 June 1999 08:27
To: Multiple recipients of list delphi
Subject: RE: [DUG]: ADO Hierarchial Record Sets
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
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
application/ms-tnef
