PC/Windows connectivity: Top 10 expert Q&As

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Physical connections to iSeries Configure the iSeries v5r4 to open the HTTPS port for incoming connections "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 Physical connections to iSeries Research Connecting to Printers Creating a remote output printing queue from the AS/400 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? Connecting to Printers Research FTP Simplify the process of converting a spool file from iSeries into an Excel spreadsheet Generically send a text file from the IFS via FTP Automate Client Access to find files on Windows scheduler SAVF by FTP on AS/400 -- error "source file not found" Use a virtual directory to move a .bmp file from the IFS to a remote server to run a software package Transferring binary files to IFS from a PC via FTP FTP from AS/400 to PC folder How to FTP without knowing the file name IFS folder error Creating the correct member type in a source physical file via FTP

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

cument.

[TABLE]

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.

[TABLE]

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

[TABLE]

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.

[TABLE]

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:

[TABLE]

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"

[TABLE]

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.

[TABLE]

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*

[TABLE]

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.

[TABLE]

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.