Creating Shaped Hierarchical Recordset

 Keywords:

; /////////////////////////////////////////////////////////////
; Simple Example of Creating 'shaped' hierarchical Recordset //
; Customers related to Payments by Month                     //
;                                                            //
; 1. Set up Connection with DataShape Provider               //
; 2. Concatenate string to create a Recordset Object; note   //
;    this can be written like a VB string, using ADO         //
;    constants, i.e. adChar, without having to first         //
;    translate them to Integer Values.                       //
;                                                            //
;                                                            //
; Stan Littlefield 02/02/2002                                //
; /////////////////////////////////////////////////////////////

BoxOpen("Shaped Hierarchical Recordset Primer","Fabricating The Recordset")


;vars for output files
cATG             = StrCat(DirGet(),"CustPay.atg")
cTXT             = StrCat(DirGet(),"CustPay.txt")

;ADO constants
adOpenStatic     = 3
adLockOptimistic = 3

;OLE connection/Object
cConn            = "Provider=MsDataShape;Data Provider=NONE"
DB               = ObjectOpen("ADODB.Connection")
RS               = ObjectOpen("ADODB.RecordSet")
RS1              = ObjectOpen("ADODB.RecordSet")
DB.Open(cConn)

;this next line just for de-bugging
If FileExist("c:\windows\wwwbatch.ini") Then FileDelete("c:\windows\wwwbatch.ini")

;complete structure to create Parent/Child Recordset with MsShape
cShape  = "SHAPE APPEND NEW adInteger AS CustID,"
cShape  = StrCat(cShape," NEW adChar(25) AS FirstName, NEW adChar(25) AS LastName," )
cShape  = StrCat(cShape," NEW adChar(12) AS SSN, NEW adChar(50) AS Address," )
cShape  = StrCat(cShape," ((SHAPE APPEND NEW adInteger AS CustID," )
cShape  = StrCat(cShape," NEW adChar(3) AS Month,  NEW adInteger AS Payment)" )
cShape  = StrCat(cShape," AS Payments RELATE CustID TO CustID) " )

RS.Open(cShape, DB, adOpenStatic, adLockOptimistic, -1 )

; Examine what has been Created
; The last field you see will be named Payments, which is
; a reference to the child data, related by CustID
flds    = RS.Fields
nCnt    = flds.Count
For i = 0 To ( nCnt -1 )
   f    = RS.Fields(i)
   n    = f.Name
   Message( Strcat( "Field ",i+1 ),n )
Next

BoxText("Adding Sample Record, with Child Records")
RS.AddNew()
f       = RS.Fields("CustID")
f.Value = 1999
f       = RS.Fields("FirstName")
f.Value = "Stan"
f       = RS.Fields("LastName")
f.Value = "Littlefield"
f       = RS.Fields("SSN")
f.Value = "999-99-9999"
f       = RS.Fields("Address")
f.Value = "Raleigh, North Carolina"

;use 2nd Recordset Object
f       = RS.Fields("Payments")
RS1     = f.Value

RS1.AddNew()
g       = RS1.Fields("CustID")
g.Value = 1999
g       = RS1.Fields("Month")
g.Value = "JAN"
g       = RS1.Fields("Payment")
g.Value = 350
RS1.AddNew()
g       = RS1.Fields("CustID")
g.Value = 1999
g       = RS1.Fields("Month")
g.Value = "FEB"
g       = RS1.Fields("Payment")
g.Value = 200

ObjectClose(RS1)
RS.Update()

If FileExist( cATG ) Then FileDelete( cATG )
If FileExist( cTXT ) Then FileDelete( cTXT )
RS.Save(cATG,0) ; Saved as ADTG, not XML, The driver does not permit
                ; saving in standard XML
RS.Close()
ObjectClose(RS)
ObjectClose(DB)

; now, to re-open saved data
; cConn   = "Provider=MsDataShape;Data Provider=NONE;"
; tried (above) it didn't work, so just went to MsPersist

cConn   = "Provider=MsPersist"
DB      = ObjectOpen("ADODB.Connection")
RS      = ObjectOpen("ADODB.RecordSet")
RS1     = ObjectOpen("ADODB.RecordSet")
DB.Open(cConn)
RS.Open(cATG, DB, adOpenStatic, adLockOptimistic, -1 )

; add yet another child record for March
f       = RS.Fields("CustID")

If f.Value == 1999
   f       = RS.Fields("Payments")
   RS1     = f.Value
   RS1.AddNew()
   g       = RS1.Fields("CustID")
   g.Value = 1999
   g       = RS1.Fields("Month")
   g.Value = "MAR"
   g       = RS1.Fields("Payment")
   g.Value = 75
Endif

RS.MoveFirst()

; Create Ascii output file
cOut    = FileOpen(cTXT,"WRITE")
FileWrite(cOut,"Parent/Child Data Output from %cATG%")
FileWrite(cOut,"==================================================")

While ! RS.EOF()
   parfld = RS.Fields("LastName")
   parval = parfld.Value
   var    = strcat( "Customer: ",StrTrim(parval), " [PAYMENTS]",@CRLF)
   rsfld  = RS.Fields("Payments")
   RS1    = rsfld.Value
   While ! RS1.EOF()
      childfld = RS1.Fields("Month")
      childval = childfld.Value
      var = strcat( var,"   ",childval)
      childfld = RS1.Fields("Payment")
      childval = childfld.Value
      var = strcat( var," ",childval,@CRLF)
      RS1.MoveNext()
   EndWhile
   FileWrite(cOut,var)
   RS1.Close()
   RS.MoveNext()
EndWhile

ObjectClose(RS1)
RS.Close()
ObjectClose(RS)
ObjectClose(DB)

BoxShut()
FileClose(cOut)
RunWait("Notepad",cTXT)

exit


; extra code snippets, tried and un-tried

; create a 'retrieval' SHAPE command
cShape  = "SHAPE { SELECT CustID,FirstName,Lastname } "
cShape  = strcat(cShape,"APPEND ( { SELECT * FROM Payments } ")
cShape  = strcat(cShape,"RELATE CustID TO CustID ) ")

Article ID:   W15269

File Created: 2002:09:05:13:50:54

Last Updated: 2002:09:05:13:50:54

Database Search

TechHome

Creating Shaped Hierarchical Recordset