PC/Windows connectivity: Top 10 expert Q&As

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

2. Send files to a Windows machine from a CLP program

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.

3. Automate the process of sending files from a PC to an iSeries

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

4. Access data in an external database from an RPG program

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.

5. Pull an iSeries HTML file from the Web using HTTP

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:

6. Importing iSeries database files to Excel

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"

7. Send data from Excel to an iSeries database

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.

8. Get a lot of files in one FTP script on the iSeries

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*

9. Call an RPG program from VB using ODBC

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.

10. Print a PDF file from the iSeries

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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Physical connections to iSeries "Time" saving programming tips for iSeries Run command on a PC Working with ODBC and dates Why is my job taking twice as long? Problems with new server and PC Take control of your iSeries network security -- Part 2 The iSeries and MS Office make good partners Microsoft computing: Integrating the iSeries and Microsoft Office i5/OS and Microsoft Office Integration Handbook, Third Edition -- Chapter 3 Know-IT-All Chapter Quiz: i5/OS and Microsoft Office Integration Handbook, Third Edition Physical connections to iSeries Research Connecting to Printers Fast guide to Redbooks and guides on printing/output Print a PDF file from the iSeries Problems with new server and PC iSeries to PC printing on HP printers Problems printing from the iSeries Invisible traffic that steals bandwidth Top five tips for improving network performance Error during printer emulation Is there a way to continue output after logging off server? Why can we no longer print to local printers using V4R5? Connecting to Printers Research FTP File transfer DTF "hooks" SAVF FTP on AS400 LAN: 504 Binary image transfer required for save files Listing contents of multiple FTP directories on AS/400 Transferring data via FTP on AS/400 systems More tips for securing FTP on your System i System i security: Watch for hackers using FTP System i FTP: Fast guide to Redbooks and articles System i crash course: FTP How to import an iSeries file The iSeries and MS Office make good partners

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary