Thursday, June 25, 2009

(Video) Set Library List

For those of you who preferred visual tutorials, check this video.

Setting-up library list.
Source: youtube




Read More...

(Tutorial : AS400) Chapter 4 - SQL

Now that we've created a library and saw some basic AS/400 features let's have a look at how we can create and manipulate tables using SQL.

You must create the DEMO library and define it has your current library to proceed with this chapter.

Type the following Start SQL command on the system prompt:

STRSQL

This way you begin a SQL session in AS/400. This application accepts most of the common SQL syntax (CREATE,INSERT, DELETE, SELECT, DROP, etc).

Creating a table with SQL:
From the SQL command line we're going to create the CLIENTS table and then insert some rows. For this tutorial let's assume that all phone numbers have, at most, 9 digits.

Press F3 to leave the table view and type the following commands. Type:

CREATE LIBRARY DEMO/CLIENTS( id_cli numeric(10) PRIMARY KEY, name_cli char(50), birth_cli date, phone_cli numeric(9) )

Note: You can place the code in separate lines (like in the example) or in a continued fashion (without indentation) as long as you don't press the Enter key before you finish the command.

A message will appear saying that the table was created.

If you want to see more empty command lines just press Page Down or move the mouse scroll down. Now Type:

SELECT * FROM CLIENTS

This way you can see that the table was created and it has no information in it. So let's insert some new rows.

Press F3 to leave the table view and type the following commands.

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (1, 'Mary', '12/09/1967', '999999999')

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (2, 'Tom', '09/01/1979', '123456789')

Do the SELECT command again to check if the rows were correctly inserted

Press F3 to leave the SQL command line. A set of options will appear. On the field you should write the option number. Try typing a number outside the option range (for instance 5) and then press Enter. As one would expect, an error message appears on the bottom of the screen. Try pressing F3 or inserting another value. It won't work because the screen is blocked. You will probably find some blocking errors and when you do press Esc the screen will go back to normal.

Now select the option 1 to exit the application, saving the session.


SQL Built-In Functions:
There are some very useful SQL built-in functions on the AS/400 system.

Basic Functions
If you know SQL you may already be familiar with these functions.
MAX -Returns the maximum value from a set of pre-defined values.
MIN -Returns the minimum value from a set of pre-defined values.
AVG -Returns the average value of a set of pre-defined values.
SUM -Returns the sum of a set of pre-defined values.
COUNT -Returns the number of elements in a set of pre-defined values.

Example (returns the maximum ID_CLI value from all the rows in the table CLIENTS):
SELECT MAX(ID_CLI) FROM CLIENTS


Numeric Functions
ABS(N) -Returns the absolute value of N.
CEILING(N) -Returns the rounding of N to the unit above.
FLOOR(N) -Returns the rounding of N to the unit below.


String Functions
CHAR(N) -Returns the the string representation N.
CHAR_LENGTH(S) -Returns the length of a string.
CONCAT(S1, S2) -Concatenates S1 with S2.
SUBSTR(S, I, L) -Returns a substring of S, starting at index I of lenght L.
LOWER(S) -Returns the lowercase representation of S.
UPPER(S) -Returns the uppercase representation of S.
TRIM(S) -Removes spaces from the beggining and and of S.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial

Read More...

(Tutorial : AS400) Chapter 3 - Libraries

A library is similar to a folder in Windows. In AS/400 a library is another object that can contain other objects (executable objects, source files, etc).

Libraries can't contain other libraries. AS/400 is structured as a list, the opposite of Windows which has a tree-like structure.

Creating a library:
We're now going to create a library, called DEMO, where we'll place all the files from this tutorial. Type the command:

CRTLIB DEMO

Your library is now created.

Changing the current library (CURLIB):
You can change the current library towork more easily with objects.This way you don't have to specify the library name each time youwant to work with a file.

To change the library you are currently in type:

CHGCURLIB DEMO

All the objects you create will be placed on your CURLIB (if you don't explicitly specify the library name).

If you want to refer to the library you are currently in you can use it's name or the keyword CURLIB.

You can change your default library (the library where you are when you enter the system) so that you don't have to change your current library each time you enter the system:

CHGPRF CURLIB DEMO

Be aware that if you don't change your opening or your current library, you can get some compilation errors. If you have a reference to a file (without it's library name) in your source code the compiler won't find it if it is placed on another library.

Libray Lists:
Every command we use is stored in a specific system library. When a command library isn't explicitly identified, the system will search for the command in every library in its library list until the command if found. So, if you have 2 commands with the same name on different libraries, the system will execute the one that is placed on the up most library on the list.

You can only have files with the same name in the same library if they are of different types.

You can see the library list with the command:

DSPLIBL

You can see in the listing that there are different types of libraries:

SYS: System libraries. All the essential objects to the system (commands, applications, compilers, etc).
CUR: The library you're currently in.
USR: User libraries (can be created by the user or the system manufacturer).

Press F3 to go back to the previous screen.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial

Read More...

(Tutorial : AS400) Chapter 2 - Commands

In AS/400 the commands can be executed from the system prompt. You can find it on the lower part of the screen.

Underneath the system prompt there is usually a list of function keys with tasks specific to the menu you are currently in.

Place the cursor at the prompt line,type GO and then press F4. A new screen will appear, specific to the GO command, where you can define all the options for this command.The F4 key can help you complete the syntax of most AS/400 commands.

Type MAJOR on the “Menu” option and press Enter. This shows the same result as typing GO MAJOR in the command line. This command shows you a list with the most important commands in AS/400.


The word “More...” on the bottom-right side of the list shows that there are lines that aren't visible. You can see these lines by pushing Page Down or using the mouse scroll.


Place the cursor on top of any of the options on the list and press F1. A window will appear with help about the option you chose. You can use this feature in most of the menus and applications. To close the help window press F3.


You can choose an option by typing its number on the prompt and pressing Enter.


Let's go back to the start screen. Press F3.

Command Syntax:
AS/400 commands usually have two parts (often with three letters each):a verb and a noun. For instance, CTRLIB is the create library command and it has a verb CRT (create) and a noun LIB (library). There are some exceptions, like the GO command we saw earlier. In the following list you can see frequently used command verbs and nouns.

VERB MEANING
---- -------
CPY - Copy
DSP - Display
DLT - Delete
WRK - Work

NOUN MEANING
---- -------
DEV - Device
F - File
MSG - Message
SPLF - Spool File

If you can't remember a command syntax, use the command GO VERB, which will show you a list of commands ordered by their functions.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

(Tutorial : AS400) Chapter 1 - Introduction

AS/400 is a computational platform launched in 1988 by IBM. Currently it is officially named System i5, although the term AS/400 is still widely used, because of that we will be using the term AS/400 throughout the tutorial, just be aware it's not its official name anymore.

The machine's operating system is usually OS400. This system has several application from database managers to compilers, editors, etc. AS/400 supports several programming languages like Java, C, SQL, Assembly, COBOL, PHP, etc.

Minimum requirements to complete the tutorial:

Basic programming knowledge.
Basic database knowledge (relational model, SQL).
Access to an AS/400 server.
Access terminal to the AS/400 server (in this tutorial the examples are given using Moshasoft).
A small application will be built through each chapter. After the last chapter the application will be able to manage a set of shops and their clients. At the end of each chapter you will be given the source code to the complete application.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

Wednesday, June 24, 2009

(FAQ) How To Use SQL's SELECT CASE Statement

SQL provides a mechanism for returning different values in a SELECT clause based on Boolean conditions: the CASE statement. This statement resembles Visual Basics Select Case statement.

The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator. The syntax is:

CASE [expression]
WHEN [value | Boolean expression] THEN [return value]
[ELSE [return value]]
END

Sample:
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END


Read More...

(FAQ) How To Use OPNQRYF

The Open Query File (OPNQRYF) command opens a file to a set of database records that satisfies a database query request. Once opened, the file looks like a database file opened using the Open Database File (OPNDBF) command, and the records in the file are accessed by high-level language programs that share the open data path (ODP). The path is closed, and all query resources are deallocated, using the Close File (CLOF) command.

This command is used to do any combination of the following database functions:

* Join records from more than one file, member, and record format. The join may be either equal or non-equal in nature.
* Calculate new field values using numeric and character operations on field values and constants.
* Group records by like values of one or more fields, and calculate aggregate functions, such as minimum field value and average field value, for each group.
* Select a subset of the available records, with selection both before and after grouping the records.
* Arrange result records by the value of one or more key fields.

Restrictions:

1. The user can use overrides to change the file, library, and member names specified for the FILE parameter. Overrides are ignored for the file and library specified for the FORMAT parameter, unless FORMAT(*FILE) is specified. Parameter values specified on an override command, other than TOFILE, MBR, LVLCHK, WAITRCD, SEQONLY, or INHWRT and SHARE, are ignored by the OPNQRYF command.
2. The OPNQRYF command does not share an existing open data path (ODP) in the job or activation group. If an existing SHARE(*YES) ODP in the job or activation group has the same file, library, and member name as the open query file open data path (ODP), the query file does not open and an escape message is sent.
3. Each subsequent shared open operation must use the same open options (such as SEQONLY) that are in effect when the OPNQRYF command is run.
4. Some system functions (such as the Display Physical File Member (DSPPFM) and Copy File (CPYF) commands) do not share an existing open data path. The OPNQRYF command cannot be used with those functions.
5. The file opened with the OPNQRYF command cannot be used in programs written in BASIC because BASIC does not share an existing open data path.
6. This command is conditionally threadsafe. In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA. This command is also not threadsafe and fails for Distributed Data Management (DDM) files of type *SNA.
7. Users of this command must have the following authorities:
* Execute (*EXECUTE) authority for any library that is needed to locate the files specified for the FILE and FORMAT parameters
* Object operational (*OBJOPR) authority for any physical or logical file specified for the FILE parameter, and one or more of the following data authorities for the physical file or based-on physical file members of a logical file member:
o Read (*READ) authority if the file is opened for input (using option *INP)
o Add (*ADD) authority if the file is opened for output (using option *OUT)
o Update (*UPD) authority if the file is opened for updates (using option *UPD)
o Delete (*DLT) authority if the file is opened for deletions (using option *DLT)
o *READ, *ADD, *UPD, and *DLT authority if the file is opened for all I/O operations (using option *ALL)
* *OBJOPR authority for any file specified for the FORMAT parameter
* Use (*USE) authority for any translate tables specified for the MAPFLD parameter (using option *USE)

Sample:
OVRDBF FILE(ARD) SHARE(*YES)

OPNQRYF FILE((ARD)) QRYSLT('ARDDEL *EQ ("' *CAT +
&DELCDE *CAT '") *AND ARPDCQ *NE ("' *CAT +
&ARDCQ *CAT '") *AND XDATE *LE ("' *CAT +
&TOYMD *CAT '")') KEYFLD(*FILE) +
MAPFLD((XPRN PRNCPL) (XDATE +
'%DIGITS(ARCRTD)'))
CALL PGM(ZPG308C) PARM(&PBFR &PBTO &COLDVF &COLDVT +
&PDTFR &PDTTO &USER &JOB &RDATE)

CLOF OPNID(ARD)

DLTOVR FILE(*ALL)


Read More...

(FAQ) How To Use Commitment Control

To use Commitment Control, you do the following:
• Use the CL commands CRTJRN (Create Journal), CRTJRNRCV (Create Journal Receiver) and STRJRNPF (Journal Physical File) to prepare for using commitment control, and the CL commands STRCMTCTL (Start Commitment Control) and ENDCMTCTL (End Commitment Control) to notify the system when you want to start and end commitment control. See the CL Reference for information on these commands.

• Specify commitment control on the file-description specifications of the files you want under commitment control.

• Use the COMMIT operation code to apply a group of changes to files under commitment control, or use the ROLLBACK operation code to eliminate the pending group of changes to files under commitment control.



Read More...

Tuesday, June 23, 2009

(FAQ) Here's a sample program that generates random number in AS/400 RPG ILE

Sample:

D C0Seed S 10I 0 Inz(0)
D C0RndNbr S 8F
D* Random Number Conversion
D RndNbr S 10I 0
D MaxNbr S 10I 0 Inz(9000000)
D MinNbr S 10I 0 Inz(1000000)
C*
C DoU RndNbr >= MinNbr
C*
C CallB 'CEERAN0'
C Parm C0Seed
C Parm C0RndNbr
C Parm *OMIT
C*
C Eval RndNbr = %DecH(C0RndNbr:30:29) * MaxNbr
C EndDo
C*
C Return


Read More...

Friday, June 19, 2009

(Tutorial : SQL) DECLARE CURSOR statement

Similar in function to HLL file declarations (F-specs or FD's)
-No processing actually takes place - just definition

Host variables may be included in the statement

Created using an embedded SELECT command
-most SELECT clauses may be used - ORDER BY, GROUP BY, etc
Must be declared before being referenced

Sample:
C/EXEC SQL
C+ DECLARE empcsr CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ WHERE dpt = :dept
C+
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

(Tutorial : SQL) Selecting & Processing Multiple Rows

Steps to access multiple rows:

1. Declare cursor
2. Open cursor
3. Fetch a row (record)
4. Process row (UPDATE, INSERT, etc)
5. IF last row: go to Step 6,
ELSE go to Step 3
6. Close cursor


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

Wednesday, June 17, 2009

(Q&A) What is the structure of AS400?

(From: Anonymous)
Here's an overview of AS400.

The AS/400 was introduced in 1988 by IBM as a minicomputer for general business and departmental use. It underwent several rebrandings until its last rebrand in 2006 to the name of IBM System i. It remained in production until April 2008 when it was replaced by the IBM Power Systems line. It uses an object-based library-based operating system called IBM i. The operating system also underwent rebrandings in accordance with the name changes of the general line. At first it was called OS/400 (following the name schema that gave birth to OS/2 and OS/390). Later on became known as i5/OS in line with the introduction of the eServer i5 servers featuring POWER5 processors. Finally, it was called just IBM i coinciding with the 6.1 release.

Features include a DBMS (DB2/400), a menu-driven interface, multi-user support, dumb terminal support (IBM 5250), printers, as well as security, communications and web-based; which could be programmed either inside the (optional) IBM WebSphere application server or in PHP/MySQL[1] using a native port of the Apache web server.

While in Unix-like systems everything is a file, on the System i everything is an object, with built-in persistence and garbage collection. It also offers Unix-like file directories using the Integrated File System [2]. Java compatibility is implemented through a native port of the Java virtual machine.


Read More...

(Tutorial : SQL) SQLCODE Error Handling

SQLCODE (SQLCOD) contains return code
- = 0 Successful statement execution
- > 0 Successful, with warning condition
- < 0 Unsuccessful - statement failed

SQLCODE value indicates exact error or condition
- e.g.. 100 = Row not found (or end of file)
- e.g.. -552 = Not authorized to object

SQLCODE values have corresponding messages
- e.g.. SQL0100 = Row not found
-e.g.. SQL0552 = Not authorized to &1.

Sample:

C/EXEC SQL
C+ SELECT name INTO :nam
C+ WHERE emp = :number
C/END-EXEC
C If SQLCod < 0
C ExSr Error
C EndIf
C If SQLCod = 100
C ExSr NotFound
C EndIf

WHENEVER Error Handling

WHENEVER statement checks SQLCA
- can branch to a location based on condition
Three conditions:
a. SQLWARNING (SQLCODE > 0 except 100)
- OR (SQLWARN0 = 'W')
b. SQLERROR (SQLCODE < 0)
c. NOT FOUND (SQLCODE = 100)
Two possible actions - neither very good!
a. CONTINUE
b. GO TO label

Sample:

C/EXEC SQL
C+
C+ WHENEVER SQLERROR GO TO err
C+
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

Tuesday, June 16, 2009

(Tutorial : SQL) Selecting & Processing Multiple Rows

Steps to access multiple rows:
1. Declare cursor
2. Open cursor
3. Fetch a row (record)
4. Process row (UPDATE, INSERT, etc)
5. IF last row: go to Step 6,
- ELSE go to Step 3
6. Close cursor

DECLARE CURSOR statement

Similar in function to HLL file declarations (F-specs or FD's)
-No processing actually takes place - just definition

Host variables may be included in the statement

Created using an embedded SELECT command
-most SELECT clauses may be used - ORDER BY, GROUP BY, etc

Must be declared before being referenced

Sample:

C/EXEC SQL
C+ DECLARE empcsr CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ WHERE dpt = :dept
C+
C/END-EXEC

DECLARE CURSOR - more clauses

With Hold clause useful with Commitment Control
-By default, cursors are closed when Commit/Rollback commands
execute
-With Hold - keeps cursor open
-With Hold also an optional clause on the Commit/Rollback commands

Sample:

C/EXEC SQL
C+
C+ DECLARE empcsr CURSOR FOR
C+ WITH HOLD
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ WHERE dpt = :dept
C+ FOR UPDATE OF sal
C+
C/END-EXEC

OPEN statement

Actually executes the SQL Select statement

Builds the access path if necessary

Successful Open places the file cursor before the first row of
the result table

Cursor must be closed before it can be opened

Syntax: OPEN cursor-name

Sample:

C/EXEC SQL
C+
C+ OPEN empcsr
C+
C/END-EXEC

FETCH statement:

Two functions
-position the cursor for the next operation

C/EXEC SQL
C+
C+ FETCH NEXT FROM empcsr
C+
C/END-EXEC

-bring rows into the program

C/EXEC SQL
C+
C+ FETCH NEXT FROM empcsr
C+ INTO :number, :name, :salary
C+
C/END-EXEC

FETCH statement

Alternatives to Next processing:
-must define the cursor as a scrollable cursor in the declare statement

C/EXEC SQL
C+
C+ DECLARE empcsr SCROLL CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ ORDER BY empid
C+
C/END-EXEC
C/EXEC SQL
C+
C+ FETCH PRIOR FROM empcsr
C+ INTO :number, :name, :salary
C+
C/END-EXEC

Positioned Update and Delete Stmts

Update or delete the current row of an updatable cursor
-Can only be done after successful Fetch operation
-Add a "Where Current of" clause to the Update and Delete statements

C/EXEC SQL
C+ DECLARE empcsr CURSOR FOR
C+ SELECT nbr, nam, sal
C+ FROM emp
C+ ORDER BY empid
C+ FOR UPDATE OF sal
C/END-EXEC
C/EXEC SQL
C+ FETCH NEXT FROM empcsr
C+ INTO :number, :name, :salary
C/END-EXEC
C/EXEC SQL
C+ UPDATE emp
C+ SET sal = sal + :raise
C+ WHERE CURRENT OF empcsr
C/END-EXEC

Close Statement

Close the cursor
-Cursor must be opened in order to be closed
DB2/400 may close cursors for other reasons also:
-job end
-activation group ends
-program ends
-modules ends
-commit or rollback without a 'with hold' clause
-error handling......

C/EXEC SQL
C+
C+ CLOSE empcsr
C+
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

(Q&A) How Can I Retrive The Current User ID In RPG

(From: Lucia)
HI,Can you tell me how can i retrive the current user id in the rpg program?

You can use Program Status Data Structure to retrieve current user.

I* Program Status Data Structure
IPSDS SDS
I* User profile
I 254 263 S#USER


Read More...

(Tutorial : SQL) Using Structures in SQL

Host structures are groups of variables
- Data structures in RPG
- Group items in COBOL

Structures can be used in SQL statements
- Replaces list of variables

Sample:
D EMP DS
D Job 5 0
D Name 25
D Sal 7 2
D EmpNbr S 1
C/EXEC SQL
C+ SELECT POS, NAME, SAL
C+ INTO :EMP
C+ FROM EMPL WHERE NBR = :EmpNbr
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

(Tutorial : SQL) Rules: Embedding SQL in RPG Code

a. All SQL statements must be coded on a C spec

b. SQL statements begin with /EXEC SQL in positions 7-15
- with the slash in position 7

c. and end with /END-EXEC in positions 7-15

d. You can enter SQL statements on the same line as /EXEC SQL
- However, /END-EXEC must be on a separate line

e. Between beginning and ending delimiters, all SQL statements
must have + in position 7

f. SQL statements cannot go past position 80

g. SQL statements cannot be included via a /COPY statement


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

(Tutorial : SQL) Introduction To Embedded SQL

Why embed SQL in programs?
a. Perform dynamic selection functions
- ala OPNQRYF, except more flexible
b. Perform set-at-a-time functions under program control
c. Even to replace HLL I/O operations
- e.g., READ, WRITE, UPDATE, CHAIN

What can be embedded?
a. The SQL statements we have seen so far
- e.g., SELECT, UPDATE, INSERT, CREATE TABLE, etc.
b. Program control statements
- e.g., DECLARE CURSOR, OPEN, CLOSE, FETCH, COMMIT, ROLLBACK

RPG Interface - Source

a. Retrieve column/field values into program variables
b. One-to-one correspondence between SELECT list and INTO
list
c. SELECT....INTO expects only a SINGLE row/record
- multiple rows require the use of cursor operations

Sample:
* No F spec needed !
D EmpNbr S 5 0
D Name S 25
D Job S 1
C/EXEC SQL
C+ SELECT NAME, POS
C+ INTO :Name, :Job
C+ FROM EMPL
C+ WHERE NBR = :EmpNbr
C/END-EXEC


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial


Read More...

Monday, June 15, 2009

(FAQ) How To Search String Using FNDSTRPDM

By using the FNDSTRPDM command, you can bypass the Work with Members Using
PDM display and the Work with Objects Using PDM display and proceed directly
with the search.

To search for the character string Invoice in library ATEST in file DDSSRC
from the Find String Using PDM (FNDSTRPDM) display:

1. Type FNDSTRPDM on any command line.

2. Press F4 (Prompt). The Find String Using PDM display appears.

3. Type the string of characters, surrounded by quotation marks, that you
want PDM to search for in the Find 'string' prompt. For this example,
type 'Invoice' in the Find 'string' prompt.

4. Type the library, file, and member to be searched. To search for
additional members, type + on the line beneath the member line, and
spaces for additional members appear. For this example, search for
*ALL members in the file DDSSRC in library ATEST.

5. Type the option of the function that you want to perform on the
members containing the string in the Option prompt. To display the
description, type *DSPD in this prompt.

6. Type *PROMPT in the Prompt field to show an entry display for the
command chosen in the Option prompt every time a match is found for
the Find string.

7. Press F10 (Additional parameters) to see the remaining Find string
prompts.

8. Type 1 in the From column prompt, *RCDLEN in the To column prompt, and
*IGNORE in the Kind of match prompt.

Press Page Down (Roll Up) to enter all the remaining values, and press
Enter.

When a match is found, the appropriate display for the option chosen
appears. For this example, when the string Invoice is found, the
Display Member Description display appears.
Read More...

Tuesday, June 9, 2009

(Q&A) What is a Program Described File?

(From: Lucia)

Question:
what's the the programe description file in the rpg?what is it used for?how can i create it to be a physical file in the program?can you give me a example?

Answer:
Program Described Files
* like "flat" files
* contain only a record name and record length
* have no field information
* programmer subdivides the record inside the program
this is tedious and prone to error
* used to convert old files to AS/400 DB files
* used to send AS/400 data to another system which is not as smart as the AS/400

Sample:
In F Specs you have to define a file like this one;
FZPPFLE IPE F 71 DISK

ZPPFLE will have a total field length of 71. You need to define subfields in I Specs;
IZPPFLE NS 99
I 1 20 ZITEM
I 21 28 ZPPADT
I 29 392ZPPLPA
I 40 47 ZPPBDT
I 48 71 ZPPNAR
Read More...

(Q&A) How To Delete Old Spool File

(From: Prabhu)

Question:
hi want to create tool to delete old spool files. any please help me to give the code

Answer:
In creating you program just follow these simple steps:
1. Create a user space (QUSCRTUS API).
2. Generate a list of spooled files (QUSLSPL API).
3. Retrieve information from a user space using one of the following:
* QUSRTVUS API
* QUSPTRUS API
4. Retrieve more spooled file attribute information received from the user space
(QUSRSPLA API).
5. Call the created CL program to delete the spooled files.
CL program should contain the following:
a) Delete the specified spooled files (DLTSPLF command).
b) Send a message if the spooled file was deleted (SNDPGMMSG command).
6. Send a message to the user (QMHSNDM API).
7. Delete the user space (QUSDLTUS API).
Read More...

Monday, June 8, 2009

(FAQ) How do I do FTP from/to my AS/400?

I) Manual method - Good for one shotters or for testing and learning.
A) Starting ftp from my PC to the 400
From your PC do the following:
Start
Run
cmd
From the command interface window:
ftp your400name
Enter your userid
Enter your password

For a list of commands type in ? and hit enter
By default you will be in the library system. To change to the directory
system then enter: quote site namefmt 1
To change to the library system: quote site namefmt 0
get is used to get a file from the remote to the local.
put is used to put a file to the remote from the local.
Here is a sample used to run a command on the 400:
quote rcmd crtpf mylib/myfile rcdlen(80)
quit is used to exit.
When transmitting a save file to the 400, the save file should already
exist. Also you should use the bin command to transmit the file in
binary.
B) Starting ftp from my 400 to another 400
Very similar to starting ftp from a pc to the 400.
Differences include:
While you still use ? to prompt for commands the prompting appears
different.
C) Starting ftp from my 400 to a PC.
You'll need to ensure that the PC has a ftp server loaded. Windows comes
with one, but normally you have to load that separately. There are
others available.

II) Automated, or scripting, method
A) Scripted ftp from the pc to the 400.
First create a .BAT file. Let's call ours FTPSCRIPT.BAT. In it we'll
have one line:
ftp -n -s:ftpscript.txt>ftpout.txt
The -n says not to open a system until we say so.
The -s says to used a script file and this is the name of the script file.
The >ftpout.txt will pipe the output to a text file. You can check this
file for transmission errors.
Now create another text file called FTPSCRIPT.TXT. In this place:
open myas400
user myuserid mypassword
cd mylib
quote rcmd crtpf mylib/myfile rcdlen(80)
put myfile.txt myfile
quote rcmd runpost
quit
Now when you type ftpscript it will run the ftpscript.bat file, which
will use the ftpscript.txt file. You can use notepad or some other
utility to check the ftpout.txt file for the status of the transmission.
An important thing to remember is that the command we used, runpost, will
have to set up the library list. It will not use any library list
defined by your user profile's job description.
B) Scripted ftp from 400 to another server.
First create a simple CL program. Let's call ours FTPSCRIPT. In it we'll
have these lines:
PGM PARM(&RMTSYSTEM)
DCL VAR(&RMTSYSTEM) TYPE(*CHAR) LEN(200)
OVRDBF FILE(INPUT) TOFILE(MYLIB/FTPSCRIPT)
OVRDBF FILE(OUTPUT) TOFILE(MYLIB/FTPOUTPUT)
FTP RMTSYS(&RMTSYSTEM)
DLTOVR FILE(INPUT)
DLTOVR FILE(OUTPUT)
CALL PGM(ANALYZER) /* Analyze FTP output */
ENDPGM
The file MYLIB/FTPSCRIPT should be a one field file. The record length
is pretty flexible.
The file MYLIB/FTPOUTPUT should also be a one field file. Again, the
record length is pretty flexible.
In the file MYLIB/FTPSCRIPT put the following
<- There should be one blank line first. This cancels
prompting for the user name and password. The user
statement will handle that.
user myuserid mypassword
cd mylib
quote rcmd crtpf mylib/myfile rcdlen(80)
put myfile.txt myfile
quote rcmd runpost
quit
Now when you type CALL FTPSCRIPT it will run the CL program FTPSCRIPT,
which will use the FTPSCRIPT file. You can use DSPPFM or some other
utility to check the FTPOUTPUT file for the status of the transmission.
The program analyzer would be written to check out the contents of
FTPOUTPUT and process accordingly.
An important thing to remember is that the command we used, runpost, will
have to set up the library list. It will not use any library list
defined by your user profile's job description
Read More...

Friday, June 5, 2009

(FAQ) Using OPNQRYF %WLDCRD

OPNQRYF %WLDCRD - same functionality with that of SQL LIKE keyword.
See sample code below:

Example:

PGM

OVRDBF FILE(PF001A) SHARE(*YES)

OPNQRYF FILE((PF001A)) +

QRYSLT('(PFRMKS=%WLDCRD("*Expired*"))') +

KEYFLD(*FILE)

CPYFRMQRYF FROMOPNID(PF001A) TOFILE(QTEMP/PF001A) +

MBROPT(*REPLACE) CRTFILE(*YES)

CLOF OPNID(PF001A)

DLTOVR FILE(*ALL)

Read More...

Thursday, June 4, 2009

(FAQ) Socket Programming Simple Client Program

Socket API Calls to Create Simple Client Program
(Source: Scott Klement's Socket Programming)

Topics:
1. The socket() API call
2. The connect() API call
3. The send() and recv() API calls
4. Translating from ASCII to EBCDIC
5. The close() API call
6. Simple client program


1. The socket() API call

The previous sections explained how to find out the port number for a service name, and how to get the IP address for a host name. This section will utilize that information to create a simple client program.

The socket() API is used to create a socket. You can think of a socket as being the virtual device that is used to read & write data from a network connection.

The IBM manual page that documents the socket API is at this link: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/socket.htm

It lists the following as the prototype for the socket() API:

int socket(int address_family,
int type,
int protocol)


This tells us that the name of the procedure to call is 'socket', and that it accepts 3 parameters. Each one is an integer, passed by value. It also returns an integer. Therefore, the RPG prototype for the socket() API looks like this:

D socket PR 10I 0 ExtProc('socket')
D addr_family 10I 0 value
D type 10I 0 value
D protocol 10I 0 value


It's important to realize that the socket APIs can be used for other networking protocols besides TCP/IP. When we create a socket, we need to explain to the socket API that we wish to communicate using the IP protocol, and that we wish to use TCP on top of the IP protocol.

For address family, the manual tells us that we need to specify a value of 'AF_INET' if we wish to do network programming in the 'Internet domain'. Therefore, when we specify a value of 'AF_INET', what we're really telling the API is to 'use the IP protocol'.

Under the 'type' parameter, it allows us to give values of 'SOCK_DGRAM', 'SOCK_SEQPACKET', 'SOCK_STREAM' or 'SOCK_RAW'. The TCP protocol is the standard streaming protocol for use over IP. So, if we say 'SOCK_STREAM', we'll use the TCP protocol. As you might imagine, SOCK_DGRAM is used for the UDP protocol and SOCK_RAW is used for writing raw IP datagrams.

Finally, we specify which protocol we wish to use with our socket. Note that, again, we can specify IPPROTO_TCP for TCP, IPPROTO_UDP for UDP, etc. However, this isn't necessary! Because we already specified that we wanted a 'stream socket' over 'internet domain', it already knows that it should be using TCP. Therefore, we can specify 'IPPROTO_IP' if we want, and the API will use the default protocol for the socket type.

Now, we just have one problem: We don't know what integer values AF_INET, SOCK_STREAM and IPPPROTO_IP are! IBM is referencing named constants that they've defined in the appropriate header files for C programs, but we don't have these defined for us in RPG! But, if you do a bit of snooping into the 'System Openness Includes' library, you'll find that AF_INET is defined to be '2', SOCK_STREAM is defined to be '1', and IPPROTO_IP is defined as '0'. To make this easier for us, we'll make named constants that match these values, like so:

D AF_INET C CONST(2)
D SOCK_STREAM C CONST(1)
D IPPROTO_IP C CONST(0)


Now we can call the socket() API like so:

c eval s = socket(AF_INET:SOCK_STREAM:IPPROTO_IP)


2. The connect() API call

Once we have a socket to work with, we need to connect it to something. We do that using the connect() API, which is documented in IBM's manual at this location: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/connec.htm

It tells us here that the prototype for the connect() API looks like this:

int connect(int socket_descriptor,
struct sockaddr *destination_address,
int address_length)


So, as you can see, the procedure is named 'connect', and it accepts 3 parameters. An integer, a pointer to a 'sockaddr' structure, and another integer. It also returns an integer. This means that the RPG prototype will look like this:

D connect PR 10I 0 ExtProc('connect')
D sock_desc 10I 0 value
D dest_addr * value
D addr_len 10I 0 value


Looking further down the manual, we see that the a 'sockaddr' structure is defined as follows:

struct sockaddr {
u_short sa_family;
char sa_data[14];
};


Remember, the purpose of this structure is to tell the API which IP address and port number to connect to. Why, then, doesn't it contain fields that we can put the address and port numbers into? Again, we have to remember that the socket APIs can work with many different network protocols. Each protocol has a completely different format for how addresses work. This 'sockaddr' structure is, therefore, a generic structure. It contains a place to put the identifying address family, along with a generic "data" field that the address can be placed in, regardless of the format of the address.

Although it's not documented on IBM's page for the connect() API, there is actually a different structure called 'sockaddr_in' which is designed especially for internet addresses. The C definition for sockaddr_in can be found in the file QSYSINC/NETINET, member IN, if you have the System Openness Includes loaded. It looks like this:

struct sockaddr_in { /* socket address (internet) */
short sin_family; /* address family (AF_INET) */
u_short sin_port; /* port number */
struct in_addr sin_addr; /* IP address */
char sin_zero[8]; /* reserved - must be 0x00's */
};


To make it easier to use these structures in RPG, I like to make them based in the same area of memory. This means that you can look at the data as a 'sockaddr', or the same data as a 'sockaddr_in' without moving the data around. Having said that, here's the definition that I use for the sockaddr & sockaddr_in structures:

D p_sockaddr S *
D sockaddr DS based(p_sockaddr)
D sa_family 5I 0
D sa_data 14A
D sockaddr_in DS based(p_sockaddr)
D sin_family 5I 0
D sin_port 5U 0
D sin_addr 10U 0
D sin_zero 8A


Before we can call the connect() API, we need to ask the operating system for some memory that we can store our sockaddr structure into. Then, we can populate the sockaddr_in structure, and actually call the connect() API. Like so:

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++
D p_connto S *
D addrlen S 10I 0

C* Ask the operating system for some memory to store our socket
C* address into:
C eval addrlen = %size(sockaddr)
C alloc addrlen p_connto

C* Point the socket address structure at the newly allocated
C* area of memory:
C eval p_sockaddr = p_connto

C* Populate the sockaddr_in structure
C* Note that IP is the ip address we previously looked up
C* using the inet_addr and/or gethostbyname APIs
C* and port is the port number that we looked up using the
C* getservbyname API.
C eval sin_family = AF_INET
C eval sin_addr = IP
C eval sin_port = PORT
C eval sin_zero = *ALLx'00'

C* Finally, we can connect to a server:
C if connect(s: p_connto: addrlen) < 0
C*** Connect failed, report error here
C endif


3. The send() and recv() API calls

Once we've made a connection, we'll want to use that connection to send and receive data across the network. We'll do that using the send() and recv() APIs.

IBM's manual page for the send() API can be found at this link: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/send.htm

It tells us that the prototype for the send() API looks like this:

int send(int socket_descriptor,
char *buffer,
int buffer_length,
int flags)


Yes, the procedure is called 'send', and it accepts 4 parameters. Those parameters are an integer, a pointer, an integer and another integer. The send() API also returns an integer. Therefore, the RPG prototype for this API is:

D send PR 10I 0 ExtProc('send')
D sock_desc 10I 0 value
D buffer * value
D buffer_len 10I 0 value
D flags 10I 0 value


You may have noticed that for other 'char *' definitions, we put the 'options(*string)' keyword in our D-specs, but we didn't this time. Why? Because the send() API doesn't use a trailing null-character to determine the end of the data to send. Instead, it uses the buffer_length parameter to determine how much data to send.

That is a useful feature to us, because it means that we are able to transmit the null-character over the network connection as well as the rest of the string, if we so desire.

The flags parameter is used for 'out of band data', and for sending 'non-routed' data. You'll almost never use these flags. Why? Because 'out of band data' has never been widely adopted. Many TCP/IP stacks don't even implement it properly. In fact, for a long time, sending 'out-of-band' data to a Windows machine caused it to crash. The popular program called 'winnuke' does nothing more than send some out-of-band data to a Windows machine. The other flag, 'dont route' is really only used when writing routing applications. In all other situations, you want your packets to be routed! Therefore, it's very rare for us to specify anything but a 0 in the flags parameter.

The return value of the send() API will be the number of bytes sent, or a negative number if an error occurred.

Consequently, we typically call the send() API like this:

D miscdata S 25A
D rc S 10I 0

C eval miscdata = 'The data to send goes here'
C eval rc = send(s: %addr(miscdata): 25: 0)
c if rc < 25
C* for some reason we weren't able to send all 25 bytes!
C endif


The recv() API is used to receive data over the network. IBM has documented this API here: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/recv.htm

recv() is very similar to send(). In fact, the prototype for recv is nearly identical to send(), the only difference is the name of the procedure that you call. The prototype looks like this:

int recv(int socket_descriptor,
char *buffer,
int buffer_length,
int flags)


And, just like send, the RPG prototype looks like this:

D recv PR 10I 0 ExtProc('recv')
D sock_desc 10I 0 value
D buffer * value
D buffer_len 10I 0 value
D flags 10I 0 value


The obvious difference between send() and recv() is what the system does with the memory pointed to by the 'buffer' parameter. When using send(), the data in the buffer is written out to the network. When using recv(), data is read from the network and is written to the buffer.

Another, less obvious, difference is how much data gets processed on each call to these APIs. By default, when you call the send() API, the API call won't return control to your program until the entire buffer has been written out to the network. By contrast, the recv() API will receive all of the data that's currently waiting for your application.

By default, recv() will always wait for at least one byte to be received. But, if there are more bytes, it will return them all, up to the length of the buffer that you've requested.

In the send() example above, 25 bytes are always written to the network unless an error has occurred. In the recv() example below, we can receive anywhere from 1 to 25 bytes of data. We have to check the return code of the recv() API to see how much we actually received.

Here's a quick example of calling recv():

D miscdata S 25A
D rc S 10I 0

C eval rc = recv(s: %addr(miscdata): 25: 0)
c if rc < 1
C* Something is wrong, we didnt receive anything.
C endif


4. Translating from ASCII to EBCDIC

Almost all network communications use the ASCII character set, but the AS/400 natively uses the EBCDIC character set. Clearly, once we're sending and receiving data over the network, we'll need to be able to translate between the two.

There are many different ways to translate between ASCII and EBCDIC. The API that we'll use to do this is called QDCXLATE, and you can find it in IBM's information center at the following link: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/QDCXLATE.htm

There are other APIs that can be used to do these conversions. In particular, the iconv() set of APIs does really a good job, however, QDCXLATE is the easiest to use, and will work just fine for our purposes.

The QDCXLATE API takes the following parameters:
Parm# Description Usage Data Type
1 Length of data to convert Input Packed (5,0)
2 Data to convert I/O Char (*)
3 Conversion table Input Char (10)

And, since QDCXLATE is an OPM API, we actually call it as a program. Traditionally, you'd call an OPM API with the RPG 'CALL' statement, like this:

C CALL 'QDCXLATE'
C PARM 128 LENGTH 5 0
C PARM DATA 128
C PARM 'QTCPEBC' TABLE 10


However, I find it easier to code program calls using prototypes, just as I use for procedure calls. So, when I call QDCXLATE, I will use the following syntax:

D Translate PR ExtPgm('QDCXLATE')
D Length 5P 0 const
D Data 32766A options(*varsize)
D Table 10A const

C callp Translate(128: Data: 'QTCPEBC')


There are certain advantages to using the prototyped call. The first, and most obvious, is that each time we want to call the program, we can do it in one line of code. The next is that the 'const' keyword allows the compiler to automatically convert expressions or numeric variables to the data type required by the call. Finally, the prototype allows the compiler to do more thorough syntax checking when calling the procedure.

There are two tables that we will use in our examples, QTCPASC and QTCPEBC. These tables are easy to remember if we just keep in mind that the table name specifies the character set that we want to translate the data into. In other words 'QTCPEBC' is the IBM-supplied table for translating TCP to EBCDIC (from ASCII) and QTCPASC is the IBM supplied table for translating TCP data to ASCII (from EBCDIC).


5. The close() API call

This section documents the easiest, by far, socket API to call. The close() API. This API is used to disconnect a connected socket, and destroy the socket descriptor. (In other words, to use the socket again after calling close() you have to call socket() again).

Here's the IBM manual page that describes the close() API:< http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/apis/close.htm

The manual tells us that the prototype for close() looks like this:

int close(int fildes)


So, the procedure's name is 'close' and it accepts one parameter, an integer. It also returns an integer. The RPG prototype looks like this:

D close PR 10I 0 ExtProc('close')
D sock_desc 10I 0 value


To call it, we can simply to:

C eval rc = close(s)
C if rc < 0
C*** Socket didn't close. Now what?
c endif


Or, more commonly (because there isn't much we can do if close() fails) we do something like this:

C callp close(s)


Too easy to be a UNIX-Type API, right? Well, never fear, there's one complication. The system uses the same close() API for closing sockets that it uses for closing files in the integrated file system.

This means that if you use both sockets and IFS reads/writes in your program, that you only need to define one prototype for close(). Handy, right? Unfortunately, most people put all of the definitions needed for socket APIs into one source member that they can /COPY into all of the programs that need it. Likewise, the IFS prototypes and other definitions are put into their own /COPY member.

When you try to use both /COPY members in the same program, you end up with a duplicate definition of the close() API, causing the compiler to become unhappy.

The solution is relatively simple... When we make a header file for either sockets or IFS, we use the RPG /define and /if defined directives to force it to only include the close() prototype once. So, our prototype will usually look like this:

D/if not defined(CLOSE_PROTOTYPE)
D close PR 10I 0 ExtProc('close')
D sock_desc 10I 0 value
D/define CLOSE_PROTOTYPE
D/endif


6. Our first client program

We've learned a lot of new API calls over the past few sections. It's time to put these new APIs to use with an example program.

This program is a very simple http client. It connects to a web server on the internet, and requests that a web page (or another file) on the server be sent back to it. It then receives the data that the web server returns and displays it on the screen.

It's important to understand that most data that is sent or received over the internet uses the concept of 'lines of text.' A line of text is a variable-length string of bytes, you can tell the end of a line by looking for the 'carriage-return' and 'line-feed' characters. When these characters appear in the text, it means that its time to start a new line.

In ASCII, the 'carriage-return' character (CR) is x'0D', and the 'line-feed' character is x'0A'. When translated to EBCDIC, these are x'0D' and x'25', respectively.

Therefore, the pseudocode for this client program looks like this:

1. Look up the port number for the HTTP service and store it into the variable 'port'.
2. Look up the IP address for the hostname that was passed as a parameter, and store it in the variable 'IP'.
3. Call the socket() API to create a socket that we can use for communicating with the HTTP server.
4. Create a socket address structure ('sockaddr') that will tell the connect() API which host & service to connect to.
5. Call the connect() API to connect to the HTTP server.
6. Place a two-line 'request' into a variable. The first line will contain the phrase "GET /pathname/filename HTTP/1.0" which tells the HTTP server that we wish to get a file from it, and also tells the HTTP server where that file is. The "HTTP/1.0" means that we're using version 1.0 of the HTTP specifications (more about that later) The second line of the request is blank, that's how we tell the server that we're done sending requests.
7. Translate our request to ASCII so the server will understand it.
8. Call the send() API to send our request to the server.
9. Call the recv() API to read back 1 byte of the server's reply.
10. If an error occurred (that is, the server disconnected us) then we're done receiving, jump ahead to step 13.
11. If the byte that we've read is not the 'end-of-line' character, and our receive buffer isn't full, then add the byte to the end of the receive buffer, and go to step 9.
12. Translate the receive buffer to EBCDIC so we can read it, and display the receive buffer. Then go back to step 9 to get the next line of data.
13. Close the connection.
14. Pause the screen so the user can see what we received before the program ends.

Without further ado, here's the sample program, utilizing all of the concepts from the past few sections of this tutorial:

File: SOCKTUT/QRPGLESRC, Member: CLIENTEX1

H DFTACTGRP(*NO) ACTGRP(*NEW)

D getservbyname PR * ExtProc('getservbyname')
D service_name * value options(*string)
D protocol_name * value options(*string)

D p_servent S *
D servent DS based(p_servent)
D s_name *
D s_aliases *
D s_port 10I 0
D s_proto *

D inet_addr PR 10U 0 ExtProc('inet_addr')
D address_str * value options(*string)

D INADDR_NONE C CONST(4294967295)

D inet_ntoa PR * ExtProc('inet_ntoa')
D internet_addr 10U 0 value

D p_hostent S *
D hostent DS Based(p_hostent)
D h_name *
D h_aliases *
D h_addrtype 10I 0
D h_length 10I 0
D h_addr_list *
D p_h_addr S * Based(h_addr_list)
D h_addr S 10U 0 Based(p_h_addr)

D gethostbyname PR * extproc('gethostbyname')
D host_name * value options(*string)

D socket PR 10I 0 ExtProc('socket')
D addr_family 10I 0 value
D type 10I 0 value
D protocol 10I 0 value

D AF_INET C CONST(2)
D SOCK_STREAM C CONST(1)
D IPPROTO_IP C CONST(0)

D connect PR 10I 0 ExtProc('connect')
D sock_desc 10I 0 value
D dest_addr * value
D addr_len 10I 0 value

D p_sockaddr S *
D sockaddr DS based(p_sockaddr)
D sa_family 5I 0
D sa_data 14A
D sockaddr_in DS based(p_sockaddr)
D sin_family 5I 0
D sin_port 5U 0
D sin_addr 10U 0
D sin_zero 8A

D send PR 10I 0 ExtProc('send')
D sock_desc 10I 0 value
D buffer * value
D buffer_len 10I 0 value
D flags 10I 0 value

D recv PR 10I 0 ExtProc('recv')
D sock_desc 10I 0 value
D buffer * value
D buffer_len 10I 0 value
D flags 10I 0 value

D close PR 10I 0 ExtProc('close')
D sock_desc 10I 0 value

D translate PR ExtPgm('QDCXLATE')
D length 5P 0 const
D data 32766A options(*varsize)
D table 10A const

D msg S 50A
D sock S 10I 0
D port S 5U 0
D addrlen S 10I 0
D ch S 1A
D host s 32A
D file s 32A
D IP s 10U 0
D p_Connto S *
D RC S 10I 0
D Request S 60A
D ReqLen S 10I 0
D RecBuf S 50A
D RecLen S 10I 0

C*************************************************
C* The user will supply a hostname and file
C* name as parameters to our program...
C*************************************************
c *entry plist
c parm host
c parm file

c eval *inlr = *on

C*************************************************
C* what port is the http service located on?
C*************************************************
c eval p_servent = getservbyname('http':'tcp')
c if p_servent = *NULL
c eval msg = 'Can''t find the http service!'
c dsply msg
c return
c endif

c eval port = s_port

C*************************************************
C* Get the 32-bit network IP address for the host
C* that was supplied by the user:
C*************************************************
c eval IP = inet_addr(%trim(host))
c if IP = INADDR_NONE
c eval p_hostent = gethostbyname(%trim(host))
c if p_hostent = *NULL
c eval msg = 'Unable to find that host!'
c dsply msg
c return
c endif
c eval IP = h_addr
c endif

C*************************************************
C* Create a socket
C*************************************************
c eval sock = socket(AF_INET: SOCK_STREAM:
c IPPROTO_IP)
c if sock < 0
c eval msg = 'Error calling socket()!'
c dsply msg
c return
c endif

C*************************************************
C* Create a socket address structure that
C* describes the host & port we wanted to
C* connect to
C*************************************************
c eval addrlen = %size(sockaddr)
c alloc addrlen p_connto

c eval p_sockaddr = p_connto
c eval sin_family = AF_INET
c eval sin_addr = IP
c eval sin_port = port
c eval sin_zero = *ALLx'00'

C*************************************************
C* Connect to the requested host
C*************************************************
C if connect(sock: p_connto: addrlen) < 0
c eval msg = 'unable to connect to server!'
c dsply msg
c callp close(sock)
c return
c endif

C*************************************************
C* Format a request for the file that we'd like
C* the http server to send us:
C*************************************************
c eval request = 'GET ' + %trim(file) +
c ' HTTP/1.0' + x'0D25' + x'0D25'
c eval reqlen = %len(%trim(request))
c callp Translate(reqlen: request: 'QTCPASC')

C*************************************************
c* Send the request to the http server
C*************************************************
c eval rc = send(sock: %addr(request): reqlen:0)
c if rc < reqlen
c eval Msg = 'Unable to send entire request!'
c dsply msg
c callp close(sock)
c return
c endif

C*************************************************
C* Get back the server's response
C*************************************************
c dou rc < 1
C exsr DsplyLine
c enddo

C*************************************************
C* We're done, so close the socket.
C* do a dsply with input to pause the display
C* and then end the program
C*************************************************
c callp close(sock)
c dsply pause 1
c return

C*===============================================================
C* This subroutine receives one line of text from a server and
C* displays it on the screen using the DSPLY op-code
C*===============================================================
CSR DsplyLine begsr
C*------------------------
C*************************************************
C* Receive one line of text from the HTTP server.
C* note that "lines of text" vary in length,
C* but always end with the ASCII values for CR
C* and LF. CR = x'0D' and LF = x'0A'
C*
C* The easiest way for us to work with this data
C* is to receive it one byte at a time until we
C* get the LF character. Each time we receive
C* a byte, we add it to our receive buffer.
C*************************************************
c eval reclen = 0
c eval recbuf = *blanks

c dou reclen = 50 or ch = x'0A'
c eval rc = recv(sock: %addr(ch): 1: 0)
c if rc < 1
c leave
c endif
c if ch<>x'0D' and ch<>x'0A'
c eval reclen = reclen + 1
c eval %subst(recbuf:reclen:1) = ch
c endif
c enddo

C*************************************************
C* translate the line of text into EBCDIC
C* (to make it readable) and display it
C*************************************************
c if reclen > 0
c callp Translate(reclen: recbuf: 'QTCPEBC')
c endif
c recbuf dsply
C*------------------------
Csr endsr


Compile this program with: CRTBNDRPG PGM(CLIENTEX1) SRCFILE(xxx/QRPGLESRC) DBGVIEW(*LIST)

Run the program by typing: CALL CLIENTEX1 PARM('ods.ods.net' '/index.html')

(You should be able to use this to retrieve just about any web page)


Read More...

(FAQ) How to Automate AS400 File to Excel

When attempting to automate the downloads to Excel, I ran into a problem with using .csv because many of the fields contain imbedded commas. I wanted the user to be able to just click on the file and have all the data formatted. The answer is to copy to a tab-delimited import file first, then copy to a stream file making sure to use the .xls file extension.


CPYTOIMPF FROMFILE(&LIB/&FILE) TOFILE(QTEMP/TEMPXLS) +
MBROPT(*ADD) RCDDLM(X'0D') DTAFMT(*DLM) +
STRDLM(*NONE) FLDDLM(X'05') DATFMT(*USA) +
TIMFMT(*USA)

Hex '05' translates to a tab. And then all I had to do was copy to a file with an .xls extension:

CPYTOSTMF +
FROMMBR('/QSYS.LIB/QTEMP.LIB/TEMPXLS.FILE/TEMPXLS.MBR') +
TOSTMF(&STMF) +
STMFOPT(*REPLACE) CVTDTA(*AUTO) +
DBFCCSID(37) STMFCODPAG(850) ENDLINFMT(*LF)

Where &STMF = 'q:homedownloadmyfile.xls'
Read More...

Wednesday, June 3, 2009

(FAQ) How To Add Colors To Source Code

Here's a tip many people may not be aware of. When we are coding the programs we put a lot of comments to explain logic or functionality, instead of putting these comments in green color, generally we will prefer to put them in different colors. For doing this some people will use tools some will do programmatically. Instead of doing so we can add some colors to our program by using simple Client Access keyboard mapping.

Let us see how we can do this..

- Click on the "MAP" button
- Click on any key, for example I want to map my ALT + R for red color
- So click on R and type APL 28 in the box provided against Alt and save the settings.
- Now go to the source line, which you want to make in RED. Put your cursor just before the first word of the line and press ALT + R and enter, the source line will be displayed red in color.

For different colors follow this table:

APL 20 - Green
APL 21 - Green RI
APL 22 - White
APL 23 - White RI
APL 24 - Green UL
APL 25 - Green RI UL
APL 26 - White UL
APL 27 - ND
APL 28 - Red
APL 29 - Red RI
APL 30 - Blue
APL 31 - Blue RI
APL 32 - Yellow
APL 33 - Yellow RI
APL 34 - Blue UL
APL 35 - Blue UL RI

By using this technique we can change the colors for member text too... take option 13 and F4 for member you want to change the color first, then put your cursor just before the first letter in the text and press corresponding color key and press enter. Now you can see your member text will be displayed in different color.
Read More...

Tuesday, June 2, 2009

(FAQ) How to send email from iSeries

I. Simple message

You will need to know the internet address(es) of the recipient(s). Use the SNDDST command to send the message.

Parameters:
1. TYPE: Use *LMSG (Long Message, 5000 characters maximum)
2. TOINTNET: The internet address of the recipient. Use the “+” for additional addresses.


Recipient Type is
*PRI for the TO addressee
*CC for CC: addressees
*BCC for BCC: addressees.

3. DSTD: This field contains the subject line of your message
4. LONGMSG: The text of your message.

Example:

SNDDST TYPE(*LMSG) TOINTNET((someone@SomeDomain.com) (someoneelse@hotmail.com *PRI))
DSTD(Here is your file!!) LONGMSG(Your test worked great!. Please call me if you have questions/comments. )



II. Send a file as an attachment

The file must exist in the QDLS directory structure. You need to know the name of the file.
You will need to know the internet address(es) of the recipient(s). Use the SNDDST command to send the message.

Parameters:
1. TYPE: Use *DOC (The document specified on the Document prompt (DOC parameter)
or the Document identifier prompt (DOCID parameter) is sent. The user must
have authority for the document before it can be sent).
2. TOINTNET: The internet address of the recipient. Use the “+” for additional addresses.

Recipient Type is
*PRI for the TO addressee
*CC for CC: addressees
*BCC for BCC: addressees.

3. DSTD: This field contains the subject line of your message
4. MSG: The text of your message (256 characters maximum).
5. SNDFMT: *NOCHG
6. DOC: The name of the document in 8.3 format, e.g., TESTMAIL.TXT, or whatever name exists in the QDLS folder.
7. FLR: The name of the folder in the QDLS directory, e.g., PCUPLOAD

Example:

SNDDST TYPE(*DOC) TOINTNET((someone@SomeDomain.com *PRI) (someoneelse@hotmail.com *PRI))
DSTD(Here is your file!!) MSG(File TESTMAIL.txt attached) SNDFMT(*NOCHG)
DOC(TESTMAIL.TXT) FLR(PCUPLOAD)

First of all, you have to understand that the AS/400 supported E-mail long before the Internet came around, and SMTP became popular. It used an e-mail system called SNADS that IBM developed specifically for their business machines.

That's why commands like SNDDST seem so complicated. They weren't designed for today's Internet e-mail, they were designed for SNADS and later adapted for SMTP.

Before explaining how to set up e-mail, I'm making the following assumptions:

* Your system is already configured for TCP/IP.
* You already have DNS lookups working from your iSeries to Internet domains as well as local domains.
* You have an Internet Service Provider (ISP) who has given you an outgoing mail server to relay your e-mail. Or you have an internal SMTP server that your company uses for e-mail.


The first thing you'll want to do is configure your iSeries SMTP server. This is used when sending mail as well as receiving it. Type the following command:

Code:

CHGSMTPA AUTOSTART(*YES) MAILROUTER('your.isp.smtp.server') FIREWALL(*YES) ALWRLY(*BOTH)

* AUTOSTART(*YES) tells the system you want this server to start automatically when TCP/IP is started.
* the MAILROUTER keyword tells where the iSeries should send outgoing e-mail to. This'll be the internal SMTP server that your company uses for mail, or the one that your ISP provided you.
* The FIREWALL(*YES) keyword tells the iSeries to always send mail though the MAILROUTER, never try to deliver it directly.
* ALWRLY(*BOTH) is used to configure who is allowed to send mail though this server. Do not set this to *ALL or spammers will be able to use your machine to send spam without your consent. With *BOTH, you'll have to use the ADDSMTPLE command to control which computers can use your SMTP server (more later).


You'll also want to make sure that e-mail messages are not split up, even if they're big. This has always seemed strange to me, but it's controlled by the CHGPOPA (Change POP3 Attributes) command.

Code:

CHGPOPA MSGSPLIT(*NOMAX)

As I mentioned above, you'll need to configure which IP addresses are allowed to send mail through your iSeries system. In my shop, all the computers who can send mail have IP addresses that begin with 192.168.5, so I run the following command:

Code:

ADDSMTPLE TYPE(*ACCEPT) INTNETADR('192.168.5.0') SUBNETMASK('255.255.255.0')

You can run this command more than once if you need to add more than one subnet.

The next thing you'll need to do is set up a gateway that allows SNADS tools like SNDDST to send Internet e-mail. To do that, type the following commands:

Code:

ADDDIRE USRID(INTERNET GATEWAY) +
USRD(‘Internet SMTP gateway’) SYSNAME(INTERNET) +
PREFADR(NETUSRID *IBM ATCONTXT)

CHGDSTA SMTPRTE(INTERNET GATEWAY)

After running this command, the SNDDST command will use the gateway so that e-mail will be sent to the local SMTP server.

Now that SMTP is configured on your system, so go ahead and start the SMTP server

Code:

STRTCPSVR SERVER(*SMTP)

(If it was already running, use ENDTCPSVR to end it. Then use WRKACTJOB SBS(QSYSWRK) to make sure that all of the QTSMTPxxxx jobs end, then use STRTCPSVR to start it up again.)

Each user who needs to send e-mail using SNDDST will need to have a directory entry and SMTP e-mail address configured on the iSeries.

Code:

ADDDIRE USRID(KLEMSCOT S10262BA) USRD('Scott Klement')

Code:

WRKNAMSMTP TBLTYPE(*SYSTEM)

1. Use option 1=Add
2. Type the same name/address that you used on the ADDDIRE command
3. Enter the SMTP e-mail address for this user.


Once all of that is done, it should be easy to send off a test e-mail with SNDDST. For example:

Code:

SNDDST TYPE(*LMSG) +
TOINTNET(('unclebob@example.com')) +
DSTD('Put E-mail Subject Here') +
LONGMSG('Hi Bob. This is an E-mail message sent from my iSeries system.')

You should also be able to configure tools like Outlook, Firebird, Eudora, etc to send mail using your iSeries SMTP server. For user-written e-mail, that's nicer than using SNDDST. Though, SNDDST is still nice for automatic messages generated by programs.

There are also lots of free e-mail tools on the Web that can use this SMTP server. They provide many more capabilities than SNDDST does. But, alas, this message is getting too long, so I'll stop there.
Read More...

(Q&A) Missing Display File IGCDTA Parameter Field

(From: Kammy)

Question:
Do you know how can i use the chinese in as400?as far as i know ,there is one way that when i create the source file ,i should change the parameter of User specified DBCS data- > *YES,but now i can't find the paremeter of user specified DBCS data in the as400.

Answer:
In the Create Display File (CRTDSPF) command, just press F9 key to show all parameters.

Read More...

Monday, June 1, 2009

(Q&A) Step By Step Socket Programming

(From: Anonymous)

Question:
Hi.. I need to use socket programming in AS400. Can you please give me step-by-step instructions on how can I do that? Or atleast give me templates?

Your soonest reply is so much appreciated.

Reply:
Hi. Socket programming is a very advanced RPG programming, you have to be familiar with TCP-IP, Ports, and Socket API's. It's a very long discussion however, i can give you this link, it will discuss from the basic to the advanced programming techniques using sockets.

http://www.scottklement.com/rpg/socktut/tutorial.html
Read More...

(FAQ) How Can You Check Record Existence Without Using CHAIN or READ I/O Operation?

This is possible using File Information Data Structure (INFDS).
Here's the code:

Position Entry
6 F
7-52 Blank (if the information is specified on a separate continuation line)
53 K (indicates a continuation statement)
54-59 INFDS (identifies this data structure as a file information data structure)
60-65 Name of the file information data structure.

Also make the following entries on an input specification line:
Position Entry
6 I
7-12 Name of the file information data structure
13-18 Blank
19-20 DS
21-74 Blank.

For each subfield of the information data structure, make the following entries on an
input specification line:
Position Entry
6 I
7-43 Blank
44-51 *RECORD
52 Blank
53-58 Name of the subfield of the information data structure
59-74 Blank.

Read More...