The LA Fox Developer Newsletter

May 2000

ADO Jumpstart (Con’tfmmpage5)

SHAPE (SELECT * FROM “dbo”.”Customers”} AS Customers

APPEND ((SELECT * FROM "dbo"."Orders") AS Orders RELATE

“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 be-

comes 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 Custom-

ers

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.

To be continued..

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 program-

ming for 30 years and has worked with FoxPro for the last 6

years. You can reach him at CParker@FowlerSoftware.com.]

Join

LA Fox

Page 6