Go Procedural: Loading More Than One Target Record Per Source Record (Part 2)

Second in a two-part series on using RIFL, DJImport, and DJExport to load more than one target record for a given source record



This is the expanded example with a command (DJExport) for writing the records. Refer back to Part 1 for a complete description of these problem these posts are solving.

The DJExport object is configured as a MySQL target. The table will be created on-the-fly with two columns, HOTEL_ID and SERVICE_ID. If you want to reuse an existing table definition, try DJX with insert statements.

Include "C:\Users\Carl2\Cosmos9_Work\WorkspaceDemos\AtdHotel.itable.rifl"
Include "C:\Users\Carl2\Cosmos9_Work\WorkspaceDemos\AtdService.itable.rifl"

' prevents eof errors calling imp.Fields() for last line
On Error Resume Next

Dim sHotelName
Dim aServices(10)
Dim i
Dim sHotelId
Dim sServiceId
Dim fldHotelId As DJField
Dim fldServiceId As DJField

' initialize incore lookup functions; if error, make sure in install dir or Include exists
AtdHotel_Init()
AtdService_Init()

' imp references DJImport from Process Variables
Set imp = New DJImport "Excel XP"

' string created using wizard
imp.ConnectString = "Database=C:\\Users\\Public\\Documents\\Data\\ATD Test \
Data.xls;Table=hotels;Encoding=OEM;HeaderRecordRow=1"

Set djexp = New DJExport "MySQL (ODBC 3.5)"

Set fldHotelId = djexp.NewField
fldHotelId.Name = "HOTEL_ID"

Set fldServiceId = djexp.NewField
fldServiceId.Name = "SERVICE_ID"

djexp.ConnectString = "Database=ATD;Table=ATD_HOTEL_SERVICE;Encoding=OEM;ConstraintDDL=;\
CommitFrequency=0;SystemTables=False;Views=True;Synonyms=False;DSNType=User &\ System;AutoCommit=False;BulkOperations=False;DriverCompletion=complete;IdentifierQuotes=Default;\
ModifyDriverOptions=True;DriverOptions=DSN=ATD;MaxDataLength=1048576;PrimaryKey=;\
TransactionIsolation=serializable;UseCursors=False"

While imp.AtEOF = 0
  
  ' split comma-separated list into array
  aServices = Split(imp.Fields("Services"), ",")
  
  For i = LBound(aServices) to UBound(aServices)
   
   ' look up ids in database
   sHotelName = imp.Fields("Hotel Name")
   sHotelId = AtdHotel_HOTEL_ID_Lookup(sHotelName, "")
   sServiceId = AtdService_SERVICE_ID_Lookup(aServices(i), "")
   
   'LogMessage("INFO", "Writing " & sHotelId & ", " & sServiceId)
   
   djexp.Fields("HOTEL_ID") = sHotelId
   djexp.Fields("SERVICE_ID") = sServiceId
   djexp.PutRecord
   
  Next i
 
  ' advance to next record
  imp.RecordNumber = imp.RecordNumber + 1

Wend

Set imp = Nothing
Set djexp = Nothing

This post is replicated from http://my.opera.com/walkerca/blog/2010/12/08/go-procedural-loading-more-than-one-target-record-per-source-record-part-2.