PC/Windows connectivity: Top 10 expert Q&As

Detailed description for both alternative and others can be found in this document.

[IMAGE]2. Send files to a Windows machine from a CLP program [IMAGE]Return to Table of Contents

How can I send files to a Windows machine from a CLP program?

It is possible to use QNTC. QNTC is a file system that allows iSeries programs to write data directly to a shared directory on Windows NT.

You will need the same user and password on the iSeries and the NT; you will need to define the directory required as shared on the NT side. When QNTC is configured correctly, the iSeries command CPY can copy from and to QNTC as if it is local.

[IMAGE]3. Automate the process of sending files from a PC to an iSeries[IMAGE]Return to Table of Contents

How can I automate the process of sending files from a PC to an iSeries?

Use any PC scheduler to launch script to FTP the required file to the iSeries.

For example:

FTP -s:myscriptfile.txt where myscriptfile.txt contains something like this:

USER as400user
as400password
cd qgpl
ascii
put pcfile as400file

[IMAGE]4. Access data in an external database from an RPG program[IMAGE]Return to Table of Contents

Can I access data in an external database from an RPG program?

* Install the driver.

* Write a small Java program to read the access file.

The Java program can be wrapped in a user-defined table function as described very nicely here.

[IMAGE]5. Pull an iSeries HTML file from the Web using HTTP [IMAGE]Return to Table of Contents

Can the iSeries pull an HTML file from the Web using HTTP?

In order to connect to a Web site from the iSeries via HTTP you can:

[IMAGE]6. Importing iSeries database files to Excel[IMAGE]Return to Table of Contents

How can I import iSeries database files to Excel?

Data Transfer Base Support and Data Transfer Excel Add-In (Under Data Access -- Data Transfer)

Once installed, activate the iSeries Access Data Transfer feature add-in:

1. Open Excel worksheet

2. Select Add-Ins from the Tools pull-down menu.

3. Select the iSeries Access Data Transfer feature

To set up an SQL statement that returns data to the worksheet:

1. From the pull-down menu "Data", select "Transfer Data from iSeries"

2. From Transfer Request dialog window, select "Create New"

3. Specify the iSeries system name

4. On next dialog window, specify the library/file name

5. On next dialog window, press the "Properties" button

6. Select the "SQL" tab

7. Select the option "Process Select as native SQL" and Press OK

8. Press the "Data Options" button

9. From the "Change SQL Select Options" dialog window, enter your SQL statement and press OK

10. Press Next

11. If you want to save this definition so you can run it again later, specify a file name with the .DTF extension

12. Press Finish

The results of your query should be returned to the active worksheet.

Excel also has some data transfer features that allow you access iSeries data using ODBC drivers to access the system. If you want to play around with those, here's how to get started:

1. From the pull-down menu "Data", select "Import External Data" -> "New Database Query"

[IMAGE]7. Send data from Excel to an iSeries database [IMAGE]Return to Table of Contents

How can I send data from Excel to an iSeries database?

I don't think you'll find any OS/400 feature that makes it substantially easier. To do that, you'd probably need to buy a third-party data movement or replication product.

One approach would be using the CPYFRMIMPF CL command.

1. Define target table with DDS or SQL.
2. Export Excel file into delimited format.
3. FTP delimited format file to your iSeries server.
4. Use CPYFRMIMPF CL command to load the delimited format file into the target table.

[IMAGE]8. Get a lot of files in one FTP script on the iSeries [IMAGE]Return to Table of Contents

How can I get a lot of files in one FTP script on the iSeries?

MGET can be used in FTP script.

* It's easy to set the destination library to be different then QGPL'. For example, lcd mylib will direct the files to mylib.

* If the file names are short enough, the iSeries FTP client will behave the way you want (i.e. new file for every remote file), however, for longer then 10 character file names, this may be an issue.

* The easiest way to work around this naming restriction is to direct the FTP incoming files to the directory on the IFS. For example:

lcd /tmp
mget longnamesintheremote server*

[IMAGE]9. Call an RPG program from VB using ODBC [IMAGE]Return to Table of Contents

What's the best way to call an RPG program from VB using ODBC?

It is possible to call an iSeries program from VB using stored procedure support as follows:

1. Get mdac up and running -- This is the Microsoft part.

2. Create the procedure on the iSeries using one of the compilers (i.e. RPG' Cobol' c' pure SQL).

3. Register the procedure using the create function as follows:

CREATE PROCEDURE QGPL.myproc
(parm1 OUT CHAR(10), parm2 OUT CHAR(10))
(EXTERNAL NAME QGPL.pgmname LANGUAGE CL SIMPLE CALL)

4. Embed the code for procedure call, this is using the CALL statement in the execute.

[IMAGE]10. Print a PDF file from the iSeries[IMAGE]Return to Table of Contents

How can I print a PDF file from the iSeries? You can use FTP to create a spool file with the PDF in it. For example:

1. crtprtf mylib/myprtf *usrascii
2. ftp loopback
user password
bin
cd mylib
put mypdf myprtf
quit

This creates a new spool file with the PDF file in it.