RecordSet 5: DataShape provider

<< Click to Display Table of Contents >>

Navigation:  Examples > RecordSet >

RecordSet 5: DataShape provider

Previous pageReturn to chapter overviewNext page

The example below shows how you can use the DataShape provider to get a one to many related resultset back in one call, and how you can access the data from the child resultset:

 

FUNCTION Start

 LOCAL cnn AS ADOConnection

 LOCAL rst AS ADORecordset

 LOCAL rstTitleAuthor AS ADORecordset

 LOCAL oCons AS COnsole

 oCons := Console{}

 cnn := AdoConnection{}

 rst := AdoRecordset{}

 cnn:Provider := "MSDataShape"

 cnn:Open ( "Data Provider=SQLOLEDB;" + ;

 "Server=(local);uid=sa;pwd=;database=pubs",NIL,NIL,NIL)

 //STEP 1

 rst:StayInSync := TRUE

 rst:Open ( "SHAPE  {select * from authors} APPEND ({select * from titleauthor} " + ;

 " RELATE au_id TO au_id) AS chapTitleAuthor",  cnn,NIL,NIL,NIL)

 //STEP 2

 rstTitleAuthor := AdoRecordset{rst:[Collect,"chapTitleAuthor"]}

 DO WHILE .NOT. rst:EOF .and. Rst:AbsolutePosition < 5

 oCons:WriteLine(rst:[Collect,"au_fname"]+" "+rst:[Collect,"au_lname"]+" "+ ;

         rst:[Collect,"state"]+" "+ rst:[Collect,"au_id"])

 //STEP 4

 DO WHILE .NOT. rstTitleAuthor:EOF

         oCons:WriteLine("> "+AsString(rstTitleAuthor:[Collect,1])+" "+ ;

         AsString(rstTitleAuthor:[Collect,2]) + " "+ ;

         AsString(rstTitleAuthor:[Collect,3])+" "+ ;

         AsString(rstTitleAuthor:[Collect,4]))

         rstTitleAuthor:MoveNext()

 ENDDO

 rst:MoveNext()

 ENDDO

 oCons:WriteLine("Press Enter to continue")

 oCons:Read()