6
|
|
6 |
|
|
The LA Fox Developer Newsletter
|
|
May 2000
|
|
ADO
Jumpstart
(Con’tfmmpage5)
“CustomerlD” TO “CustomeriD”) AS Orders
If your first thought is, “Gee, this is like setting relations in Visual FoxPro,” you are indeed correct. It is exactly the same principle. If the Shape syntax is broken down, the task becomes manageable. The first clause in the code begins with the keyword SHAPE, to signify that what follows is not pure SQL, but rather, Data Shape language. The Data Shape language is a super-set of SQL, which is why you need to use MSDataShape as the OLE DB provider. MSDataShape can interpret and execute Shape commands. Finally, the last portion of the first command specifies that the results of the SQL statement are to be aliased as Customers.
In the next set of commands, things get a bit complicated, especially when the hierarchy is nested an additional one or two levels (This is the case when order details are added, as we’ll do in the next example).
You can interpret the keyword APPEND as “Append the results of the next SQL statement to the results of the previous SQL statement.” Of course, just appending records won’t suffice. Rather, you must provide a rule that specifies how the records are to be related. This is where the RELATE keyword comes into play.
You can interpret the RELATE keyword as,
“When
appending records, do so based on these join fields”. In this case, the join is between the CustomerlD column in the Customers table and the CustomeriD column in the Orders table.
Finally, we need to alias the data that was just appended as Orders. The following code sets up the objects and creates the hierarchical recordset:
#lnclude adovfp.h
Local oRecordset,oConnectlon,ocommand, cShpStr oRecordset
=
CreateObject(”adodb.recordset”) oConnection
=
createObject(”adodb.connectlon”) cShpStr
=
‘SHAPE (SELECT
*
FROM “dbo”.”Customers”} AS Customers
cShpStr
=
cShpStr
+
‘APPEND ((SELECT
*
FROM “dbo”.”Orders”}; AS Orders’
cShpStr
=
cShpStr
+
‘RELATE “CustomeriD” TO “customeriD”) AS Orders’
With oConnection .Provider
=
“MSDataShape”
.ConnectionString
=
“Data \ Security
lnfoFalse;User ID=sa;Initial catalog=Northwind;Data Source=JVP” Open
EndWith
With oRecordset .ActiveConnection
=
oconnection .Source
=
cShpStr .CursorType
=
adOpenStatic .LockType
=
adLockBatchOptimistic .CursorLocation
=
adUseClient .Open
|
EndWith
The question at this point is, “How is the data appended?” The technique is rather clever. When you append a recordsetto another recordset, you do so through a Field object. If you query the Count property of the Fields collection, you discover that the value of 12 is returned. However, in SQL Server, you see that the Customers table only has 11 fields. The twelfth field, in this case, is actually a pointer to the Orders recordset. The rows in the Orders recordset for a given row in the Customers recordset are only those for that customer. The following code illustrates just how powerful hierarchical recordsets are:
oRecordset. MoveFirst
Do While !oRecordset.Eof
With oRecordset
?.Fields(”Customerld”).Value,.Fields(”CompanyName”).Value
End
With
oOrders
=
oRecordset.Fields(”orders”).Value
Do
While
!oOrders.Eof With oOrders
?Chr(9),.Fields(”Customerld”).Value,.FIelds(”orderdate”).Value
MoveNext
End
With
EndDo
oRecordset. MoveNext
EndDo
With the basics of hierarchical recordsets out of the way, we can turn our attention to a more complicated, real-life example. The following example adds several dimensions to the recordset.
Multi-User
(Con’t from page 9)
endif
*_
Go back to record we were on when we started.
*_
If the saved pointer is less than zero, then
we were on an appended record, so just stay where we are now.
if InHere> 0
go InHere
endif
return IIOK
(Ed.Note: Charile Parker is a project leader for Fowler Software Design in Eldorado Springs, Colorado. He has been programming for 30 years and has worked with FoxPro for the last 6 years. You can reach him at CParker@FowlerSoftware.com.]
|
|
Page 6
|
|
|
6 |
|