Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Sunday, December 19, 2010

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

' 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 \

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;\

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
  Next i
  ' advance to next record
  imp.RecordNumber = imp.RecordNumber + 1


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.

No comments:

Post a Comment