> -----Original Message-----
> From: Wilfred Verkley [mailto:[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.
More to the point, I can't remember ever seeing 'SHAPE' and 'RELATE' in
my SQL text book (by C.J. Date). I take it they are 'extensions' to SQL
92?
> -----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
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
