Alternatives to Batch Data Transfer: Data Transfer automation objects; Import files with QNTC; ADO and Windows Scripting Host

From Support

Document Title:

Alternatives to Batch Data Transfer: Data Transfer automation objects; Import files with QNTC; ADO and Windows Scripting Host

Document Description:

Client Access provides a batch data transfer function in the form of the RTOPCB and RFROMPCB console programs. This document discusses three alternatives to the Client Access batch data transfer programs.

The Client Access batch data transfer 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 OS/400 and a Windows PC; however, there are situations where their use may be difficult or impractical. These situations include: o Unattended environments. The batch data transfer functions do not allow users to specify the user profile or password to be used on the connection. This makes it very difficult to run the programs in an unattended environment such as a NT service program.

• The transfer is run on the PC, not as an OS/400 program. There may be situations where an OS/400 user must control transfers from the OS/400 side. Although an OS/400 user or program can initiate the command indirectly (for example by using RUNRMTCMD) this adds additional complexity.
• There is limited error handling. The batch programs signal error severities similar to the old DOS commands. There may be times when better error handling is required.

1) Windows Scripting Host and ActiveX Data Transfer Objects

If using Client Access R510 or later, Windows Scripting Host can be used with Data Transfer automation objects to automate a data transfer. These objects provide the easiest and best method for a simple script that will perform a data transfer and allow user ID and password to be specified. Windows scripting host is included in Windows 2000, Windows 98/Me. Windows 95 and Windows NT versions can be downloaded from the following Web site:

www.microsoft.com/scripting

Further information on Jscript and Vb Script syntax, use of the scripting host, and so on is also available at the Microsoft site. For further information on the automation objects, refer to the Client Access Express Toolkit. Below is a sample script that could be used to automate transfer. This script has the user ID and password coded in, but could be modified to accept them as input parameters. Additional parameters and error handling may also be desired.

Upload or Download (Transfer.vbs)

' 'This examples uses the Windows scripting host. This is included

'with Windows 2000 and 98. It can be downloaded from www.microsoft.com/scripting

'for both Windows 95 and NT. '

'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

2) Copy to/from Import File and QNTC

One alternative is to use the CPYTOIMPF and CPYFRMIMPF commands. These OS/400 commands provide function similar to Client Access data transfer. The commands can be combined 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 that performance will be slower than using Client Access Data transfer.

3) Use ADO and Windows Scripting Host

Another alternative to using batch files and Client Access batch data transfer is to use the Windows Scripting Host and Microsoft ActiveX Data Objects (ADO). ADO is installed as part of MDAC. MDAC updates are available at the following Web site:

www.microsoft.com/data

Included below are two scripts that demonstrate downloading a file and uploading a file. Error handling, parameter passing, and file formats may all need to be enhanced. Download (xfer.vbs) ' 'This examples uses the Windows scripting host. This is included

'with Windows 2000 and 98. It can be downloaded from www.microsoft.com/scripting

'for both Windows 95 and NT. '

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

' 'This examples uses the Windows scripting host. This is included

'with Windows 2000 and 98. It can be downloaded from www.microsoft.com/scripting

'for both Windows 95 and NT. ' '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."