IBM i (AS400) fans only - tips and tricks to start RPGfree programming - simple examples of RPG Free

#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. SQL has no direct access to IBM i multiple-member physical file but OS/400 support an SQL alias statement . Therefore, I need to create an alias for the member I want to access with SQL. I give an example using the IBM i PUB400 public server. In library VDOTEST1, file QDDSLF I have 8 members. CLANA01L CLANA02L CLANA03L CLANA04L CLANA05L CLANA06L CNCON01L OTORD01L I want to access member CLANA03L via SQL. I type STRSQL (Enter) then I create the ALIAS: create alias A_CLANA03L for VDOTEST1.QDDSLF(CLANA03L) The alias is a persistent object.  The ALIAS object has attribute DDMF. Now I can access the member with SQL: Display data from CLANA03L Don't forget to remove the ALIAS with a "DROP" command: DROP ALIAS myLibrary/myAlias That’s it. Thanks to the suggestion of my friend Don Anderson I add this way: SELECT CAST(LINE AS CHAR(120))                      FROM TABLE (   

#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. In most of IBMi we have a large number of QUERIES. Often these QUERIES are provided to end users, with all limitations of this practice. I think this practice is deplorable. How about turning these queries into SQL and then writing an RPG with embedded SQL? The QUERY to SQL translation process is really simple, it only takes one command: RTVQMQRY QMQRY(MyQueryLib/MyQuery) SRCFILE(MyLib/MySrcFile) ALWQRYDFN(*YES) That's it! Below an example of a cursor declaration in an SQLRPGLE program (Sql embedded) // preapre the data recordset exec sql declare C1 cursor for select OTORD00F.OTANN0, OTORD00F.OTCOR0, CLANA00F.CLNOM0 from OTORD00F inner join CLANA00F on OTCCL0 = CLCCL0 where OTANN0 like :F1ANN0lke and OTCOR0 between :F1COR0str and :F1COR0end and CLNOM0 like :F1NOM0lke order by OTCOR0 for read only; Here is a complete SQLRPGLE sample program with SQL embedded . #IBMiSam

New feature:  F18=Search string into listed files WSF utility, works like WRKSPLF but... New feature: Searching a string into  spooled files New feature: Generate a PDF from a spooled file #IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. If you are interested in this program  Download WSF *SAVF  (New version with Create PDF option) Need some help loading the *savf?  HOW TO GET *SAVF FROM YOUR DESKTOP TO YOUR IBM I SYSTEM Users often have many spool-file rows. How to  quickly   find the last done? how to quickly find the one produced on October 15th? These and other options are just a click away. The WRKSPLF command does not show spool-file rows sorted by Creation-Date/Creation-Time, so the last one is not the first one in the list. I thought a different way of looking at spooled-file rows might be useful. In this program (WSF) the spool-file rows appears in order of Creation-Date/Creation-Time, with the last one at the top of the

#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. First of all look at  this post  and compile and populate OTORD00F file to use this example. Let's create a query called Q01A : ccm   represents our parameter to pass to the query. Type F3 Now let's write the CL to pass the ccm parameter to the query: Q01ACL CL ****************** Inizio dati *******************************              PGM        PARM(&CCM)                                          DCL        VAR(&CCM) TYPE(*CHAR) LEN(5)                        STRQMQRY   QMQRY(Q01A) OUTPUT(*) QMFORM(*QMQRY) +                           ALWQRYDFN(*YES) SETVAR((CCM &CCM))               ENDPGM                                             ******************** Fine dati ******************************* We save and compile the CL. Now let's try. Type: CALL PGM(Q01ACL) PARM(('103')) I have two records into OTORD00F with  OTCCM0  = ' 103 '. This is the r

#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. This example comes from https://www.ibm.com/support/pages/coding-rpg-iv-beginners-tutorial This example calls the C runtime printf() function to print a message to the standard output instead of to the external message queue.

#IBMiSample It often happens that you have to read all the records of a file. Years ago I would have used the RPG cycle defining the Input/Primary file. Today, with the introduction of embedded SQL, I would do this: First of all I create a file, then I manually write some records. to create a file I write this SQL source: IPFLSQL.SQL -- --  RUNSQLSTM SRCFILE(myLib/MySouceFile) SRCMBR(IPFLSQL) -- --  Generazione tabella CREATE OR REPLACE TABLE myLib /IPFLI00F ( IIFANN CHARACTER(1) NOT NULL WITH DEFAULT, IITEXT CHARACTER(25) NOT NULL WITH DEFAULT ) RCDFMT IPFLI ;  Run IPFLSQL.SQL with RUNSQLSTM SRCFILE(myLib/MySouceFile) SRCMBR(IPFLSQL) to create the file IPFLI00F. Then populate IPFLI00F with some records. No matter what you type, just a few records are enough. Then compile and run  IPFL01.SQLRPGLE       **free       *******************************************************       *  How to read an entire file from the beginning      *       ************************************************

#IBMiSample Compile and Run this SQLRPGLE to create table CLANA00F, index CLANA01L and table OTORD00F, and populate:       **free       *****************************       * create CLANA00F & OTORD00F       *****************************        ctl-opt option(*nodebugio) dftactgrp(*no) actgrp(*new);         // The EXEC SQL is never executed. It is used at compile time.         exec sql Set Option Commit = *None;         // Create File         exec sql           CREATE or REPLACE TABLE myLib/CLANA00F (           CLANN0 CHARACTER(01) NOT NULL WITH DEFAULT,           CLCCL0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,           CLCIB0 CHARACTER(03) NOT NULL WITH DEFAULT,           CLNOM0 CHARACTER(50) NOT NULL WITH DEFAULT           )           RCDFMT CLANA           ;         // Create index         exec sql           CREATE INDEX myLib/CLANA01L ON myLib/CLANA00F (CLCCL0 ASC);         // Fill values         exec sql           INSERT INTO CLANA00F (CLCIB0, CLNOM0)          

#IBMiSample This CLP  sends a SpoolFile per Email as TXT attachment and need 4 parameters: &SPOOLF Spool file name &SUBJCT Subject &BODY00 body &MAIL01 Email (Pay attention at the "Send Email" note at the bottom) Compile this CLP source with: CRTCLPGM PGM(myLib/SNDSPLF) SRCFILE(myLib/mySourceFile) SRCMBR(SNDSPLF) SNDSPLF.CLP                    PGM        PARM(&SPOOLF &SUBJCT &BODY00 &MAIL01) /*****************************************************************************/ /* Send a SpoolFile per Email as TXT attachment                              */ /* &SPOOLF Spool file name                                                   */ /* &SUBJCT Subject                                                           */ /* &BODY00 body                                                              */ /* &MAIL01 Email                                                             */ /* Try with:                                           

#IBMiSample Suppose your *SAVF name is  SFFFDV1 For a PC: To FTP to the IBM i system, you should do the following: 1. Place SFFFDV1 in directory C:\TEMP 2. Open a command prompt window (CMD). 3. Type the following: CD C:\TEMP 4. FTP SYSTEMNAME or FTP xxx.xxx.xxx.xxx where SYSTEMNAME is the IBM i system name and xxx.xxx.xxx.xxx is the IP address. Press the Enter key. 5. Type the user ID and password . 6. To change to binary mode, type the following: BIN Press the Enter key. 7. To change the host and client to naming convention type 1, type the following: QUOTE SITE NAMEFMT 1 Press the Enter key. 8. To create the destination file in the library specified so you do not need to create the SAVF first, type the following: PUT SFFFDV1 /QSYS.LIB/ YOURLIB .LIB/SFFFDV1.SAVF Press the Enter key. 9. Now you have on your IBM i the *SAVF SFFFDV1 in your Library. To extract the SAVF objects, on your IBM i, type RSTLIB SAVLIB(FFDV1) DEV(*SAVF) SAVF( YOURLIB /SFFFDV1) Thanks to the IBM page:  Getting a

                               

Weather report from openweathermap.org   first of all get your own API key on https://openweathermap.org/ , it's free. The Key looks like '35a545c6ce3bda8347f588353aa59fbd' Compile and run the program below to create the WBSWL00F table that will contain the weather data for a location, each row one location. WBSWL00F.SQLRPGLE       **free       *********************************       * create WBSWL00F Location list *       *********************************        ctl-opt        option(*nodebugio) dftactgrp(*no) actgrp(*new);         // The EXEC SQL is never executed. It is used at compile time.         exec sql Set Option Commit = *None;         // Create File         exec sql           create table WBSWL00F             (                WWIDNM numeric (9, 0) generated always as identity,                WWORIN TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,                WWORCR TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE                     AS ROW CHANGE TIMESTAMP NOT N