Alternatives to Batch Data Transfer: Data Transfer Automation Objects; Import files with FTP/QNTC; ActiveX Data Objects

Resolving The Problem

Important Note: This document discusses Client Access for Microsoft Windows 95 and Windows NT, Client Access Express, IBM® iSeries Access, and IBM System i Access products. These names essentially refer to the same product; however, the functionality and name changed over the last several releases. For the purposes of this document, the terms Client Access, Client Access Express, iSeries Access, and System i Access can be used interchangeably. Where a difference is important, the version of the product is used to identify the differences.

Client Access, iSeries Access, and System i Access provide a batch data transfer function in the form of the RTOPCB and RFROMPCB console programs. The Client Access and iSeries 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 the operating system and a Microsoft Windows PC. However, there are situations where batch data transfer may be difficult or impractical to use. These situations include:

o	Unattended environments. With the exception of Client Access Express V5R1 or later, 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 Windows service program.
o	Synchronization with the transfer. The batch transfer is run in a separate process on the PC. There may be situations where an OS/400 or i5/OS user must control transfers from the operating system side or where the additional processing on the transferred file is required. Synchronizing with a separate process adds increased complexity.
o	Robust 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) The Client Access ActiveX Data Transfer Objects

When using Client Access Express V5R1 or later, the Client Access ActiveX Data Transfer automation objects can be used to create a customized data transfer program or script. The automation objects offer the following advantages over the batch transfer console applications:

o	Support for unattended environments such as Client Access Incoming Remote Command (IRC) and Windows scheduler. All the automation objects can accept a user id and password allowing them to be used with Windows Services. Prompting for sign-on information can also be controlled.
o	Better error handling. The transfer objects provide easy access to error information as well as statistics on the transfer results.
o	Better integration with existing programs. Because they are ActiveX automation objects, they can be used in virtually any programming environment available for Windows. This includes full programming languages such as Microsoft Visual Basic and C as well as scripting languages such as Windows Scripting Host and Microsoft Office Visual Basic for Applications (VBA).

In addition to the samples in the Client Access Toolkit the following examples are available at:

ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/

rxferpcb.zip - This batch program is similar to RTOPCB and RFROMPCB; however, it also accepts user profile and password parameters (example: rxferpcb c:\myxfer.dtf myuser mypwd ). Use this program in unattended environments such as IRC.

Note: RXFERPCB was added to Client Access Express and iSeries Access in V5R1 service pack SI10376 and V5R2 service pack SI09809.

wshXfer.zip - Windows Scripting Host (WSH) example of an unattended batch data transfer.

2) Copy to/from Import File and QNTC

The CPYTOIMPF and CPYFRMIMPF commands provide the operating system equivalent to Client Access data transfer. These OS/400 and i5/OS commands provide function similar to Client Access data transfer. The commands can be combined with the QNTC file system (or FTP) to copy the target/source file to/from a remote Windows PC. Note that performance will be slower than using Client Access Data transfer.

NETXFER.MBR at ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/ includes CL Program source demonstrating the use of the QNTC file system and the CPYTOIMPF command as an alternative to an automated data transfer. This sample transfers a database file to a PC file format with character and numeric conversions performed, without the use of Client Access or an equivalent product.

3) ActiveX Data Objects

Another alternative to using batch files and Client Access batch data transfer is to use Microsoft ActiveX Data Objects (ADO). Like all automation objects, ADO can be used with a full programming language or with most scripting languages (such as VBA and WSH). With ADO the user can import/export the data to a file or use it directly in an application.

The Client Access toolkit includes examples of using ADO to directly import and export data to IBM Lotus 1-2-3 and Microsoft Excel. wshXfer.zip at ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/ includes an example of an unattended batch data transfer that uses ADO and WSH.

4) Direct Imports/Exports

Client Access provides a data transfer add-in for Microsoft Excel that allows direct import and export of data from within the application.

Many applications have their own import and export tools. For example, Microsoft Excel, Word, and Microsoft Access can all import data directly from ODBC. Microsoft Access has an export feature that works over ODBC. Consult the product documentation for further information.