Shahar Mor, iSeries/i5 PC/Windows connectivity expert, has been answering Search400.com's member questions for more than five years. iSeries users have asked Shahar the same questions again and again. In an effort to save you time, we've compiled the top 10 questions asked about iSeries/i5 connectivity for you. Do you have a question that's not listed here? Ask Shahar your connectivity questions.
TABLE OF CONTENTS
1. Send a PC command from an iSeries program
2. Send files to a Windows machine from a CLP program
3. Automate the process of sending files from a PC to an iSeries
4. Access data in an external database from an RPG program
5. Pull an iSeries HTML file from the Web using HTTP
6. Importing iSeries database files to Excel
7. Send data from Excel to an iSeries database
8. Get a lot of files in one FTP script on the iSeries
9. Call an RPG program from VB using ODBC
10. Print a PDF file from the iSeries
|1. Send a PC command from an iSeries program||Return to Table of Contents|
How can I send a PC command from an iSeries program?
There are two methods you can use:
Detailed description for both alternative and others can be found in this .ti8lcaLfqR2.0@.214a2a77>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.
FTP -s:myscriptfile.txt where myscriptfile.txt contains something like this:
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?It is possible to access external data from the iSeries using JDBC. If the access has a pure JDBC driver, then you may do the following:
* 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 .HC0YczOokol.4@.6aea26b5>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?iSeries Access has an Excel Add-In that provides data transfer support. During iSeries Access installation process, install the following components:
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:
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
put mypdf myprtf
This creates a new spool file with the PDF file in it.
This was first published in June 2006