7
|
|
7 |
|
|
The LA Fox Developer Newsletter
|
|
June
2000
|
|
ADO Jumpstart
(Con’t from page 6)
Because a hierarchy exists, the ability to create drill-down interfaces becomes a fairly simple task.
The preceding Visual FoxPro code illustrates how to traverse the hierarchy.
Perhaps you want to use Microsoft Word or Excel as a reporting tool. With a combination of Visual FoxPro COM servers, ADO, and Automation, the process becomes manageable. The first and third parts of the solution have been around. However, only now that a set of COM objects exists to handle and work with data as Visual FoxPro does natively can the solution become a reality.
Hierarchical Recordsets and Recursive Relationships
One of the nice features of SQL Server, and of most other server back ends is provision for recursive relations. The following is the SQL Server 7.0 database diagram for the Northwind database:
|
In the Northwind database, the Employees table employs recursion to support a manager/staff relationship. Both managers and staff are employees. In some cases, it happens that some employees report to other employees. In Visual FoxPro, you can create the same sort of relation by opening a table twice using
two
different aliases. In ADO, the task is totally supported and is quite easy to implement. The following is the Shape syntax:
SHAPE (SELECT
*
FROM “dbo”.”Employees”} AS Managers APPEND ((SELECT
*
FROM “dbo”.”Employees”} AS Staff RELATE ‘EmployeelD’ TO ‘ReportsTo’) AS Staff
The following Visual FoxPro code displays a list of managers and the staff that reports to each manager:
#Include adovfp.h oRecordset
=
CreateObject(”adodb.recordset”) oConnection
=
CreateObject(”adodb.connection”)
cShpStr
=
‘SHAPE (SELECT
*
FROM “dbo”.”Employees”} AS Managers
cShpStr
=
cShpStr
+
‘APPEND ((SELECT
*
FROM “dbo”.”Employees”} AS Staff’
cShpStr
=
cShpStr
+
‘RELATE “EmployeelD” TO “ReportsTo”) AS Staff’
With oConnection
.Provider
=
“MSDataShape”
.ConnectionString
=
“Data Provider=SQLOLEDB.1 ;Persist Security Info=False;User IDsa;lnitial CatalogNorthwind;Data Source=JVP”
.Open
EndWith
With oRecordset .ActiveConnection oConnection .Source
=
cShpStr .CursorType
=
adOpenStatic .LockType
=
adLockBatchOptimistic .CursorLocation
=
adUseClient .Open
End With
Do While !oRecordset.Eof
oStaff
=
oRecordset.Fields(”staff”).Value
If oStaff.Recordcount> 0
With oRecordset ?.Fields(”firstname”).Value
+
“
“+;
.Fields(”lastname”).Value,; .Fields(”Title”).Value
Do While !oStaff.Eof
With oStaff
?Chr(9),;
.Flelds(”firstname”).Value
+“
“+;
.Fields(”lastname”).Value,; .Fields(”Title”).Value
EndWith
oStaff.MoveNext
EndDo
EndWith
Endif
oRecordset.MoveNext
EndDo
The output appears as follows:
|
|
Page 7
|
|
|
7 |
|