"Old School" Data Transfer With IBM i Access Client Solutions Windows Application Package ActiveX

How To

Summary

There are some functions in the prior IBM i Windows client data transfer that are not present in the IBM i Access Client Solutions data transfer. For example, the 'No Conversion' PC file type is not provided. This technote explains how to reclaim that functionality.

Steps

The IBM i Access Client Solutions Windows Application Package provides the Programmer's Toolkit, which documents the programming interfaces provided in the Windows Application Package. Among those programming interfaces is an ActiveX object, cwbx, which can be easily leveraged to perform data transfers in the same manner that the obsolete IBM i Access for Windows client performed them. ActiveX components can be used in simple VB scripts so no compiler is necessary. It does not require much programming experience to get a working transfer given a working example.

Do not be overwhelmed by the size of the following example; it is almost entirely a listing of constant values that can be used in data transfer. The actual code doesn't start until the 385th line of the script.

' VB Script doesn't pull in header files so the following constants have been pulled from the

' cwbx.h header file located in C:\Program Files (x86)\IBM\Client Access\Toolkit\Include directory.

' These definitions are also defined in the Programmer's Toolkit in the 'Enumerations' section.

' Documentation for the ActiveX classes, methods, properties and events is found in the 

' Programmer's Toolkit.  The specific compiled help file is 

'  C:\Program Files (x86)\IBM\Client Access\Mri2924\cwbx.chm

' the Mri* directory name varies depending upon your installed language.

' Database Transfer PC File Type Constants

const  cwbdtFileTypeNotSet = 0

const  cwbdtASCIIText = 1

const  cwbdtDosRandom = 2

const  cwbdtBasicSequential = 3

const  cwbdtBasicRandom = 4

const  cwbdtDIF = 5

const  cwbdtNoConversion = 6

const  cwbdtDosRandomType2 = 8

const  cwbdtBIFF4 = 9

const  cwbdtBIFF3 = 10

const  cwbdtBIFF5 = 11

const  cwbdtCSV = 12

const  cwbdtWK4 = 13

const  cwbdtTabDelimitedText = 14

const  cwbdtBIFF7 = 15

const  cwbdtBIFF8 = 16

const  cwbdt123 = 17

const  cwbdt123v9 = 19

const  cwbdtUnicodeText = 20

const  cwbdtExcelXML = 21

const  cwbdtHTML = 100

' Database Transfer system file creation option constants 

const  cwbdtCreateMember = 0

const  cwbdtCreateFileAndMember = 1

const  cwbdtReplaceMember = 2

const  cwbdtAppendToMember = 3

' Database Transfer Time Separator Constants

const  cwbdtTimeSepNotSpecified = 0

const  cwbdtTimeSepColon = 1

const  cwbdtTimeSepPeriod = 2

const  cwbdtTimeSepComma = 3

const  cwbdtTimeSepBlank = 4

' Database Transfer Time format Constants

const  cwbdtTimeFmtHourMinSec = 0 

const  cwbdtTimeFmtUSA = 1 

const  cwbdtTimeFmtISO = 2 

const  cwbdtTimeFmtEUR = 3 

const  cwbdtTimeFmtJIS = 4

' Database Transfer Date Separator Constants

const  cwbdtDateSepNotSpecified = 0

const  cwbdtDateSepSlash = 1

const  cwbdtDateSepDash = 2

const  cwbdtDateSepPeriod = 3

const  cwbdtDateSepComma = 4

const  cwbdtDateSepBlank = 5

' Database Transfer Date Format Constants

const  cwbdtDateFmtJulian = 0

const  cwbdtDateFmtMonDayYr = 1

const  cwbdtDateFmtDayMonYr = 2

const  cwbdtDateFmtYrMonDay = 3

const  cwbdtDateFmtUSA = 4

const  cwbdtDateFmtISO = 5

const  cwbdtDateFmtEUR = 6

const  cwbdtDateFmtJIS = 7

' Database Transfer Decimal Separator Constants

const  cwbdtDecSepPeriod = 0

const  cwbdtDecSepComma = 1

' Database Transfer system object authority constants

const  cwbdtObjAuthReadWrite = 0

const  cwbdtObjAuthReadOnly = 1

const  cwbdtObjAuthAll = 2

const  cwbdtObjAuthNone = 3

' Database Transfer Existing PC File Option Constants

const  cwbdtOverwriteOutFile = 0

const  cwbdtAppendOutFile = 1

const  cwbdtFailDownload = 2

const  cwbdtCreateOverwriteOutFileEvenWhenEmptyResultsSet = 3

' Database Transfer HTML conversion type constants

const  cwbdtHTMLConvANSI = 0

const  cwbdtHTMLConvASCII = 1

const  cwbdtHTMLConvUTF8 = 2

'Database Transfer HTML Date/Time location constants

const  cwbdtDateTimeDoNotInclude = 0

const  cwbdtDateTimeUpperLeft = 1

const  cwbdtDateTimeLowerLeft = 2

const  cwbdtDateTimeUpperRight = 3

const  cwbdtDateTimeLowerRight = 4

'Database Transfer HTML Table Alignment Constants               

const  cwbdtTableAlignLeft = 0

const  cwbdtTableAlignCenter = 1

const  cwbdtTableAlignRight = 2

' Database Transfer HTML table width units constants

const  cwbdtSizePercentOfWindow = 0

const  cwbdtSizePixels = 1

' Database Transfer HTML Vertical Alignment constants

const  cwbdtVertAlignBrowserDefault = 0

const  cwbdtVertAlignTop = 1

const  cwbdtVertAlignMiddle = 2

const  cwbdtVertAlignBottom = 3

'Database Transfer HTML Horizontal Alignment Constants

const  cwbdtHorizAlignBrowserDefault = 0

const  cwbdtHorizAlignLeft = 1

const  cwbdtHorizAlignCenter = 2

const  cwbdtHorizAlignRight = 3

'Database Transfer HTML Cell Data alignment constants

const  cwbdtCellDataAlignSameAsRow = 0

const  cwbdtCellDataAlignLeft = 1

const  cwbdtCellDataAlignCenter = 2

const  cwbdtCellDataAlignRight = 3

'Database Transfer HTML Extra Rows Action Constants

const  cwbdtTruncExtraRows = 0

const  cwbdtGenerateMultTables = 1

const  cwbdtGenerateMultDocs = 2

' Database Transfer HTML Fewer Rows Action Constants

const  cwbdtIgnoreExtraRows = 0

const  cwbdtGenerateBlankRows = 1

' Database Transfer HTML Text Style Constants

const  cwbdtBrowserDefaultStyle = 0

const  cwbdtTextBold = 1

const  cwbdtTextItalic = 2

const  cwbdtTextUnderline = 4

const  cwbdtTextFixedWidth = 8

' Database Transfer HTML Caption Alignment Constants

const  cwbdtCaptionAlignBrowserDefault = 0

const  cwbdtCaptionAlignTop = 1

const  cwbdtCaptionAlignBottom = 2

const  cwbdtCaptionAlignLeft = 3

const  cwbdtCaptionAlignRight = 4

' Database Transfer HTML Text Size Constants

const  cwbdtTextNormal = 0

const  cwbdtTextSmall = 1

const  cwbdtTextLarge = 2

' Database Transfer Request Status Constants

const  cwbdtStatusCompletedOK = 0

const  cwbdtStatusCompletedWithWarning = 1

const  cwbdtStatusCompletedWithError = 2

const  cwbdtStatusConnectingToAS400 = 3

const  cwbdtStatusPerformingQuery = 4

const  cwbdtStatusReceivingData = 5

const  cwbdtStatusPerformingFileSetup = 6

const  cwbdtStatusSendingData = 7

const  cwbdtStatusRowsTransferred = 8

const  cwbdtStatusUpdatingPCFile = 9

' Database Transfer Data Error Type Constants

const  cwbdtWarningTruncation = 1

const  cwbdtWarningRounding = 2

const  cwbdtWarningMissingData = 3

const  cwbdtWarningUntranslatableField = 4

const  cwbdtErrorTruncation = 5

const  cwbdtErrorRounding = 6

const  cwbdtErrorMissingData = 7

const  cwbdtErrorUntranslatableField = 8

' Database Transfer Sort Sequence Type Constants

const  cwbdtSortAS400Default = 0

const  cwbdtSortHexadecimal = 1

const  cwbdtSortUserSpecifiedTable = 2

const  cwbdtSortSharedWeightTable = 3

const  cwbdtSortUniqueWeightTable = 4

' Database Transfer Return Code Constants

const  cwbdtFileAlreadyExists = 7002

const  cwbdtTooManyRecords = 7003

const  cwbdtNoMatchingData = 7004

const  cwbdtIncorrectData = 7005

const  cwbdtInvalidFieldType = 7006

const  cwbdtDataRounded = 7007

const  cwbdtTooManyDigits = 7008

const  cwbdtPCDataTruncated = 7009

const  cwbdtDataPadded = 7010

const  cwbdtAS400DataTruncated = 7011

const  cwbdtUnexpectedError = 7012

const  cwbdtInvalidRecordLength = 7013

const  cwbdtErrorClosingFile = 7014

const  cwbdtErrorOpeningFile = 7015

const  cwbdtErrorWritingFile = 7016

const  cwbdtDiskFull = 7017

const  cwbdtNoRecordsToTransfer = 7018

const  cwbdtDataMissing = 7019

const  cwbdtExtraDataNotTransferred = 7020

const  cwbdtFDFRequired = 7021

const  cwbdtFileNotFound = 7022

const  cwbdtFDFEmpty = 7023

const  cwbdtTooManyFields = 7024

const  cwbdtIncorrectFieldLength = 7025

const  cwbdtInvalidPCFile = 7026

const  cwbdtInvalidPCType = 7027

const  cwbdtInvalidFDF = 7028

const  cwbdtFieldTypeNotCompat = 7029

const  cwbdtFieldNameTooLong = 7030

const  cwbdtInvalidDecPosition = 7031

const  cwbdtNotEnoughMemory = 7032

const  cwbdtHostFileNameRequired = 7033

const  cwbdtPCFileNameRequired = 7034

const  cwbdtInvalidHostFile = 7035

const  cwbdtNoFilesInLibrary = 7036

const  cwbdtCantTransferSourceAndDB = 7037

const  cwbdtInvalidLibFileName = 7038

const  cwbdtInvalidFRFFile = 7039

const  cwbdtHostFileDoestExist = 7040

const  cwbdtFilesDontMatch = 7041

const  cwbdtInvalidColumnType = 7042

const  cwbdtInvalidSortSequence = 7043

const  cwbdtAppendFileDoesntExist = 7044

const  cwbdtLibraryDoesntExist = 7045

const  cwbdtLibListEntryRemoved = 7046

const  cwbdtAppendSourceWarning = 7047

const  cwbdtUploadFDFRequired = 7048

const  cwbdtTemplateDoesntExist = 7049

const  cwbdtTemplateTagRequired = 7050

const  cwbdtInvalidTemplateTag = 7051

const  cwbdtCreateInitFailure = 7052

const  cwbdtCantLoadWK4ConvFile = 7053

const  cwbdtNoValidData = 7054

const  cwbdtInvalidPCFileFormat = 7055

const  cwbdtInvalidWorksheet = 7056

const  cwbdtAS400File = 7057

const  cwbdtSSLNotAvailable = 7058

const  cwbdtCantChangeSecurity = 7059

const  cwbdtCantSetMember = 7060

const  cwbdtSQLStatementTooLong = 7061

const  cwbdtDatalinkNotSupported = 7062

const  cwbdtMemberNameRequired = 7063

const  cwbdtRequestAlreadyExists = 7064

const  cwbdtRowTooLong = 7065

const  cwbdtBadValueDefaulted = 7066

const  cwbdtExpressPathNotFound = 7067

const  cwbdtWK4NotEnoughMemory = 7068

const  cwbdtWK4MappingNotFound = 7069

const  cwbdtWK4InvalidConvTable = 7070

const  cwbdtWK4ConvInitError = 7071

const  cwbdtWK4FilesWrongLevels = 7072

const  cwbdtTooManyFiles = 7073

const  cwbdtErrorAddingLibToList = 7074

const  cwbdtWK4ConversionError = 7075

const  cwbdtBadTransferRequest = 7400

const  cwbdtUnicodeConversionError = 7401

const  cwbdtPCFileSameAsFDFFile = 7402

const  cwbdtTransferRequestNotExist = 7403

const  cwbdtTransferRequestExists = 7404

const  cwbdtWK4NoTranslationDLL = 7405

const  cwbdtWK4NonMatchingLevels = 7406

const  cwbdtWK4OpenInitializationError = 7407

const  cwbdtWK4InvalidTable = 7408

const  cwbdtWK4NoMappingForCCSID = 7409

const  cwbdtBadLibraryListIndex = 7410

const  cwbdtMemberNameNotSpecified = 7411

const  cwbdtDatabaseServerError = 7412

const  cwbdtLibraryAlreadyInList = 7463

const  cwbdtLibraryNotFoundInList = 7464

const  cwbdtLibraryNameNotValid = 7465

const  cwbdtGeneralLibListFailure = 7466

const  cwbdtDBStartError = 7467

const  cwbdtSystemConfigFailure = 7468

const  cwbdtCancelTimedOut = 7470

const  cwbdtCancelInProgress = 7471

const  cwbdtCancelProcessedSuccessfully = 7472

const  cwbdtInvalidMemberName = 7474

const  cwbdtUnsupportedFieldType = 7475

const  cwbdtCannotUseExcelFDF = 7476

' Database Return Code Constants

const  cwbdbCannotContactServer = 6001

const  cwbdbAttributesFailure = 6002

const  cwbdbServerAlreadyStarted = 6003

const  cwbdbInvalidDRDAPackageSize = 6004

const  cwbdbRequestMemoryAllocationFailure = 6005

const  cwbdbRequestInvalidConversion = 6006

const  cwbdbServerNotActive = 6007

const  cwbdbParameterError = 6008

const  cwbdbCloneCreationError = 6009

const  cwbdbInvalidDataFormatForConnection = 6010

const  cwbdbDataFormatInUse = 6011

const  cwbdbInvalidDataFormatForData = 6012

const  cwbdbStringArgumentTooLong = 6013

const  cwbdbInvalidInternalArgument = 6014

const  cwbdbInvalidNumericArgument = 6015

const  cwbdbInvalidArgument = 6016

const  cwbdbStatementNotSelect = 6017

const  cwbdbStreamFetchNotComplete = 6018

const  cwbdbStreamFetchNotActive = 6019

const  cwbdbMissingDataProcessor = 6020

const  cwbdbIllegalCloneRequestType = 6021

const  cwbdbUnsolicitedData = 6022

const  cwbdbMissingData = 6023

const  cwbdbParameterInvalidBitstream = 6024

const  cwbdbConsistencyTokenError = 6025

const  cwbdbInvalidFunction = 6026

const  cwbdbFormatInvalidArgument = 6027

const  cwbdbInvalidColumnPosition = 6028

const  cwbdbInvalidColumnType = 6029

const  cwbdbRowVectorNotEmpty = 6030

const  cwbdbRowVectorEmpty = 6031

const  cwbdbMemoryAllocationFailure = 6032

const  cwbdbInvalidConversion = 6033

const  cwbdbDatastreamTooShort = 6034

const  cwbdbSQLWarning = 6035

const  cwbdbSQLError = 6036

const  cwbdbSQLParameterWarning = 6037

const  cwbdbSQLParameterError = 6038

const  cwbdbListServerWarning = 6039

const  cwbdbListServerError = 6040

const  cwbdbListParameterWarning = 6041

const  cwbdbListParameterError = 6042

const  cwbdbNDBFileServerWarning = 6043

const  cwbdbNDBFileServerError = 6044

const  cwbdbFileParameterWarning = 6045

const  cwbdbFileParameterError = 6046

const  cwbdbGeneralServerWarning = 6047

const  cwbdbGeneralServerError = 6048

const  cwbdbExitProgramWarning = 6049

const  cwbdbExitProgramError = 6050

const  cwbdbDataBufferTooSmall = 6051

const  cwbdbNLConversionError = 6052

const  cwbdbCommunicationsError = 6053

const  cwbdbInvalidArgumentAPI = 6054

const  cwbdbMissingDataHandler = 6055

const  cwbdbRequestDatastreamNotValid = 6056

const  cwbdbServerUnable = 6057

const  cwbdbWorkQueueStartError = 6058

const  cwbdbWorkQueueCreateError = 6059

const  cwbdbInitializationError = 6060

const  cwbdbServerAttributesError = 6061

const  cwbdbClientLevelError = 6062

const  cwbdbClientLangFeatureCodeError = 6063

const  cwbdbClientCCSIDError = 6064

const  cwbdbTranslationIndicatorError = 6065

const  cwbdbReturnServerAttributesError = 6066

const  cwbdbServerAttributesRequest = 6067

const  cwbdbReturnAttributesError = 6068

const  cwbdbServerAttributesMissing = 6069

const  cwbdbServerLangFeatureCodeConvError = 6070

const  cwbdbServerLevelConversionError = 6071

const  cwbdbServerLanguageTableError = 6072

const  cwbdbServerLanguageLibraryError = 6073

const  cwbdbServerLanguageIDError = 6074

const  cwbdbCommDequeueError = 6075

const  cwbdbCommEnqueueError = 6076

const  cwbdbUnsupportedColumnType = 6077

const  cwbdbServerInUse = 6078

const  cwbdbServerRelationalDBConvError = 6079

const  cwbdbServerFunctionNotAvailable = 6080

const  cwbdbFunctionNotValidAfterConnect = 6081

const  cwbdbLastStreamChunk = 6099

' Uncomment the next line if you just want to blast through errors

'On Error Resume Next

' The basic structure is:

'  - Create a download request object so that formatting options can be defined.

'  - Define the system that the transfer is from.

'  - Define the IBM i library file and member in the format Library/File(Member).

'  - Define the PC file name and path.

'  - Set any format options that are needed.

'  - Perform the transfer.

'  - Display the results of the transfer.

' Create download request object

set dataTransferDownloadRequest = CreateObject("cwbx.DatabaseDownloadRequest")

' Create AS400 object

set as400 = CreateObject("cwbx.AS400System")

' Define the system to connect To

as400.Define "RCH750A"

set dataTransferDownloadRequest.System = as400

' Set the PC and IBM i file names

dataTransferDownloadRequest.AS400File.Name = "mikswens/noconvtest(noconvtest)"

dataTransferDownloadRequest.PCFile.Name = "S:\sf\mikswens\DataTransferActiveX\NoConversion\data.txt"

' Set the date Format

dataTransferDownloadRequest.format.SetDateFormat cwbdtDateFmtISO

' Set the PC file data Type

dataTransferDownloadRequest.PCFile.FileType = cwbdtNoConversion

' Download the data

dataTransferDownloadRequest.Download

  ' Display the results of the transfer request 

  Set tr = dataTransferDownloadRequest.TransferResults 

  MsgBox "CompletionTime = " & tr.CompletionTime & vbCr & _ 

  "Rows Transferred = " & tr.RowsTransferred & vbCr & _ 

  "Tables Transferred = " & tr.TablesTransferred & vbCr & _ 

  "Data Errors Occurred = " & tr.DataErrorsOccurred & vbCr & _ 

  "Data Warnings Occurred = " & tr.DataWarningsOccurred & vbCr & _ 

  "Number of Data Errors in collection = " & tr.DataErrors.Count 

  For Each dataerr In tr.DataErrors 

  MsgBox "Error Type = " & dataerr.ErrorType & vbCr & _ 

  "Error Row = " & dataerr.Row & vbCr & _ 

  "Error Column = " & dataerr.Column 

  Next

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips

"Old School" Data Transfer With IBM i Access Client Solutions Windows Application Package ActiveX

How To

Summary

Steps

Document Location

Was this topic helpful?

Document Information

UID

Share your feedback

Need support?