Database Search

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

TechHome

OLE COM ADO CDO ADSI LDAP

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

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

Primer: Fabricated Hierarchical Recordsets

Keywords: 	  Primer: Fabricated Hierarchical Recordsets

Below is a script, which I think illustrates interesting possibilities for WB's OLE Extender.

Whereas LAFF may prove to be an excellent source for creating on-the-fly databases, I prefer a fabricated Recordset since (a) you may store/retrieve data in several data types not supported by LAFF; (2) Once persisted to a file, a fabricated Recordset can be treated with generic ADO functions, SQL commands, and moved to a variety of data sources (including LAFF, which I posted a script for).

An issue with both is the need to duplicate columns in order to achieve 1 to many relationships.

MDAC comes with a DataShape driver, which although cryptic at first, permits on-the-fly parent-child-grandchild relationships without data redundancy.

Anyway, if you are interested, play around with the script - I have hit a few snags, but have a pretty good idea where I want to take the concept.

; /////////////////////////////////////////////////////////////
; 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:   W15233

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

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