Alternatives to Batch Data Transfer

 

Client Access provides a batch data transfer function in the RTOPCB and RFROMPCB console programs. These programs allow unattended data transfer and can be used in DOS-style batch programs. The batch data transfer is useful for moving data files between the iSeries and a Windows PC; however, there are situations where their use may be difficult or impractical.

 

For example:

With Client Access R510 and above, you can use Windows Scripting Host with data transfer automation objects to automate a data transfer. These objects allow you to:

 

For more information on:

The following is a sample script (Transfer.vbs) that automates transfer with a hard-coded user ID and password. You can adapt this script to accept the user ID and password as input parameters or add additional parameters and error handling.

'cscript [host options...] [script name] [script options and parameters
'cscript //B "C:\transfer.vbs" fileName
'ex. cscript //B C:\Transfer.vbs C:\Request.dtf

Dim dbXfer 'As New cwbx.DatabaseTransfer

'Get input args for script.
'One input argument for transfer file name

Dim I 'as Integer
Dim fileName 'as String
Dim objArgs
Set objArgs = WScript.Arguments

'Print out arugments

For I = 0 To objArgs.Count - 1
WScript.Echo objArgs(I) 
Next
If objArgs.Count > 0 Then
fileName = objArgs(0)

'Create the output file

Set dbXfer = CreateObject("cwbx.DatabaseTransfer")

'Specify the user ID and password. If incorrect, user will 
'NOT be prompted, and an error is raised.

dbXfer.UserID = "MYUSR"
dbXfer.Password = "MYPWD"
dbXfer.Transfer fileName
If dbXfer.Errors.Count = 0 Then
WScript.Echo "File successfully transferred."
End If
Else
WScript.Echo "Transfer request not specified."
End If

The CPYTOIMPF and CPYFRMIMPF iSeries commands function similarly to Client Access data transfer. You can combine these commands with the QNTC file system to mount a Windows file share and copy the target or source file to or from a remote Windows PC.

 

Note: Performance is slower than using Client Access Data transfer.

 

You can also use the Windows Scripting Host and Microsoft ActiveX Data Objects (ADO). ADO is installed as part of MDAC. For MDAC downloads, see http://www.microsoft.com/en-us/download/details.aspx?id=5793.

 

The following two sample scripts (xfer.vbs) download and upload a file. You can adapt the error handling, parameter passing, and file formats to meet your needs. 

Script 1:

'cscript [host options...] [script name] [script options and parameters
'cscript //B "C:\xfer.vbs" /parm1 /parm2 '
'You can create a script file with the options:
'[ScriptFile]
'Path=C:\WINNT\Samples\WSH\showprop.vbs
'[Options]
'Timeout = 0
'DisplayLogo = 0
'BatchMode = 1

Const adClipString = 2
Dim cn 'As New ADODB.Connection
Dim rs 'As ADODB.Recordset
Dim FSO 'As Scripting.FileSystemObject
Dim hFile 'As Scripting.TextStream
Dim PcFileName 

'Get input args for script. You can pass file names in...

Dim I 'as Integer
dim objArgs
Set objArgs = WScript.Arguments
PcFileName = Mid(objArgs(0), 2)

'display all input args
'For I = 0 To objArgs.Count - 1
'WScript.Echo objArgs(I)
'Next
'Create the output file

Set FSO = CreateObject("Scripting.FileSystemObject")
Set hFile = FSO.CreateTextFile(PcFileName) 

'Download the target file

Set cn = CreateObject("ADODB.Connection")
cn.Open "DSN=RCHAS194;", "MYUSER", "MYPWD"
Set rs = cn.Execute("Select * from qiws.qcustcdt", , -1)

'You can format and write the data yourself or use one of
'the ADO provided output types
'This saves as ADTG or XML
'rs.Save "C:\test.txt", adPersistADTG
'You can use GetString for simple tab or comma delimited file Dim rd 'as String 

rd = vbCr & vbLf

'comma delimited with crlf record delimeter

S = rs.GetString(adClipString, , ",", rd, " ")
hFile.Write S
WScript.Echo "File successfully written"
Upload (RFromPCx.vbs)

Script 2:

'cscript [host options...] [script name] [script options and parameters
'cscript //B "C:\rfrompcx.vbs" /MYLIB /MYFILE /C:\file.txt
'[ScriptFile]
'Path=C:\WINNT\Samples\WSH\showprop.vbs
'[Options]
'Timeout = 0
'DisplayLogo = 0
'BatchMode = 1

Const ForReading = 1
Dim cn 'As New ADODB.Connection
Dim cmd 'As New ADODB.Command
Dim md 'As ADODB.Recordset
Dim NumCols
Dim FSO 'As Scripting.FileSystemObject
Dim hFile 'As Scripting.TextStream
Dim Lib 'As String
Dim TableName 'As String
Dim PCFileName 'As String
Dim S 'As String
Dim I 'As Long

'Read in parms

Dim objArgs
Set objArgs = WScript.Arguments
For I = 0 To objArgs.Count - 1
WScript.Echo objArgs(I)
Next
Lib =Trim( Mid(objArgs(0), 2) )
Tablename = Trim(Mid(objArgs(1), 2) )
PCFileName = Trim(Mid(objArgs(2), 2) )

'Connect

Set cn = CreateObject("ADODB.Connection")
cn.Open "DSN=RCHAS194;","user", "pwd"

'Get some basic metadata on the AS/400 file.
'Could use Set md = cn.OpenSchema(adSchemaColumns, Array(vEmpty, Lib, FileName))

Set md = cn.Execute( _
"SELECT COLUMN_COUNT from QSYS2.SYSTABLES WHERE TABLE_NAME =
'" & _
Trim(TableName) & "' AND TABLE_OWNER = '" & Trim(Lib) & "' ")
If md.EOF Then

'File does not exist.

End If
NumCols = md(0).Value

'Clear file if desired

cn.Execute "DELETE FROM " & Lib & "." & TableName

'Prepare the insert

Set cmd = CreateObject("ADODB.Command")
cmd.Prepared = True
Set cmd.ActiveConnection = cn
S = "INSERT INTO " & Lib & "." & TableName & " VALUES ("
For I = 1 To NumCols - 1
S = S & "?, "
Next
S = S & " ? ) "
cmd.CommandText = S
cmd.Parameters.Refresh 'Prepare the statement

'Open the PC file

Set FSO = CreateObject("Scripting.FileSystemObject")
Set hFile = FSO.OpenTextFile(PCFileName, ForReading)

'Read in the file and insert the row (this assumes CSV)

Dim RowData
Do While Not hFile.AtEndOfStream
LineNum = LineNum + 1

'Read on line

S = hFile.ReadLine

'Parse the data
'This will handle a simple CSV with no double quoted strings or
'embedded commas. More elaborate parsing may be needed.

RowData = Split(S, ",")
If UBound(RowData) - LBound(RowData) + 1 <> NumCols Then
Report error for column mismatch
End If

'"Cast" the data to the correct type.
'You can use variant for most types. If needed you
'can add special handling for each type below. Special handling
'may be needed for date/time/timestamp and binary(65535) fields.

For I = 0 To NumCols - 1

Select Case cmd.Parameters(I).Type
Case adChar, adVarChar, adLongVarChar
Case adInteger, adSmallInt
Case adDouble, adSingle
Case adNumeric, adDecimal
Case adBigInt
Case adVarBinary, adBinary
Case adDBDate
Case adDBTime
Case adDBTimeStamp
Case Else
cmd.Parameters(I).Value = RowData(I)

End Select

Next 'For I = 0 To NumCols - 1
cmd.Execute
Loop 'While Not hFile.AtEndOfStream
WScript.Echo "Successfully inserted " & LineNum & " rows of data."