WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
ADO DAO
plus
ADSI LDAP CDO
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

ADO Data Shaping

 Keywords: Parent-Child Fabricated Recordsets Data Shaping
The attached sample is heavily commented. I plan to use this type of data arrangement to create multi-dimensional Object arrays, store business rules, processing logic. 
stan littlefield 
;////////////////////////////////////////////////////////////////
;// Winbatch - ADO Data Shaping                                //
;// Creating a Hierarchical Recordset from Scratch             //
;// This demo utilizes the standard Customer-Orders relation   //
;// But could prove useful for complex arrays                  //
;//                                                            //
;// Stan Littlefield - March 3, 2003                           //
;////////////////////////////////////////////////////////////////

BoxOpen("Data Shaping Demo","Creating Parent-Child Fabricated Recordset")
cXML   = StrCat( dirget(),"CustOrd.XML" )
If FileExist( cXML ) Then FileDelete( cXML )

;// to create different child order description
;// for later searching
aData    = ArrDimension(5,0,0,0,0)
aData[0] = "Widgets"
aData[1] = "Rubber Bands"
aData[2] = "ThingaMaBobs"
aData[3] = "WhatchaMaCallit"
aData[4] = "M-16 Rifle"


;// indicate the Shape Provider, no Data Provider
;// note: the Shape Provider comes with MDAC
cConn  = "Provider=MSDataShape;Data Provider=NONE;"

;// create the SHAPE Command - note the NEW keyword
;// the syntax takes some getting used to
;// note how columns are created, you can use words like adChar
;// directly, no need for setting them up as variables
cSQL   = "SHAPE APPEND NEW adInteger AS CustId, NEW adChar(50) AS Name, NEW AdChar(50) as add1, "
cSQL   = StrCat( cSQL, "NEW adChar(50) AS City, NEW adChar(50) AS Country, ")
cSQL   = StrCat( cSQL, "( ( SHAPE APPEND NEW adInteger AS OrderId, NEW adInteger AS CustId, " )
cSQL   = StrCat( cSQL, "NEW adChar(20) AS OrdDesc ) AS Orders RELATE CustId TO CustId) " )
;// P.S. - you could nest several generations of grand-children
;//        like OrderDetail under Orders, etc..

;// Open the Recordset, create Object variables for columns
;// this 'bulk' way of doing things could probably be optimized
;// with Winbatch Object arrays
RS     = ObjectOpen("ADODB.Recordset")
RS.Open( cSQL,cConn,3,3,-1)
fld1   = RS.Fields("CustId")
fld2   = RS.Fields("Name")
fld3   = RS.Fields("Add1")
fld4   = RS.Fields("City")
fld4   = RS.Fields("Country")
child  = RS.Fields("Orders")  ;recordset within a recordset

;// add first parent record
RS.AddNew()
fld1.Value = 1001
fld2.Value = "Stan Littlefield"
fld3.Value = "Raleigh, NC"
fld4.Value = "United States"
RS.Update()

;// then add a second
RS.AddNew()
fld1.Value = 1002
fld2.Value = "Bob Smith"
fld3.Value = "San Francisco, CA"
fld4.Value = "United States"
RS.Update()

RS.MoveFirst()
ord    = child.Value
child1 = ord.Fields("CustId")
child2 = ord.Fields("OrderId")
child3 = ord.Fields("OrdDesc")

;// append multiple child records to first parent
For i = 28 to 34
   ord.Addnew()
   child1.Value = 1001
   child2.Value = i
   child3.Value = aData[ i mod 5 ]
   ord.Update()
Next

;// note: two ways to persist Recordset to Disk
;RS.Save( cXML, 0 ) ; save in ADTG format
                    ; preferred way for parent-child Recordsets
                    ; this is a binary format, and data can be persisted
                    ; (1) while updates are pending (2) with parameters

RS.Save( cXML, 1 ) ; save in XML format
                   ; for this demo, easier to read,
                   ; but note restrictions (above)
RS.Close()

;// Now re-open and add even more child elements
;// to the First Record
RS.Open(cXML,"Provider=MSPersist;",3,3,256)
RS.MoveFirst()
fld1   = RS.Fields("CustId")
n      = fld1.Value
child  = RS.Fields("Orders")
ord    = child.Value
child1 = ord.Fields("CustId")
child2 = ord.Fields("OrderId")
child3 = ord.Fields("OrdDesc")
For i = 50 to 55
   ord.Addnew()
   child1.Value = n
   child2.Value = i
   child3.Value = aData[ i mod 5 ]
   ord.Update()
Next

;// now add a few to the second parent
RS.MoveNext()
n      = fld1.Value
For i = 1 to 10
   ord.Addnew()
   child1.Value = n
   child2.Value = i
   child3.Value = aData[ i mod 5 ]
   ord.Update()
Next

;// re-save updated Recordset
RS.Save( cXML, 1 )
RS.Close()
ObjectClose(RS)
BoxShut()
Message("View With Text Editor","%cXML% Created.")
Exit
;//////////////////////////////////////////////////////////////

Article ID:   W15593

File Created: 2003:05:13:11:29:10
Last Updated: 2003:05:13:11:29:10