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

#IBMiSample The Run SQL Statements (RUNSQLSTM) command processes a source file of Structure Query Language (SQL) statements. let's have an example: Create a source member AS52A00F into library VDOTEST1/QSQLSRC. VDOTEST1 is MyLibrary. Use your library name. AS52A00F.SQL

#IBMiSample How to check the correctness of a numeric date field 6 with 0 decimals. This program receives as input 1. a date in the DDMMYY format ( p_data ), for example p_data= 100612, that is June 10, 2012, an Error field ( p_error ). 2. a Flag field accepts date = 0 ( p_zero ). 3. returns a BLANK or 'E' value in the p_error field as a result of the check. CHKDATA.RPGLE       **free        ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no)         ALWNULL(*USRCTL) ;         // Try with  STRDBG and         // CALL CHKDATA PARM(X'0120718F' 'x' 'N') valid date         // CALL CHKDATA PARM(X'0999999F' 'x' 'N') wrong date         // CALL CHKDATA PARM(X'0000000F' 'x' 'Y') no date, but it is allowed         // CALL CHKDATA PARM(X'0000000F' 'x' 'N') no date, it is not allowed         dcl-pi CHKDATA;           p_data packed(6);           p_error char(1);           p_zero  char(1);

#IBMiSample Typically we have a table and we need to perform read, write, update operations. This simple program is an example of how. First of all look at  this post  and compile and populate these files to use this example.

#IBMiSample First of all look at this post and compile and populate these files to use this example. Then compile the two following soures, OT08SF displayfile, OT08SF sqlrpg file. OT08SFV.DSPF                                             CHGINPDFT(CS UL HI)                                             INDARA                                             CA03(03 'F3=EXIT')                                             REF(OTORD00F)       *--------------------------------------------------------------                 R SFL1                      SFL       *--------------------------------------------------------------                   S1OPT          1A  B  6  2                   S1ANN0    R             +3REFFLD(OTANN0)                   S1COR0    R            + 1REFFLD(OTCOR0) EDTCDE(Z)                   S1NOM0    R            + 1REFFLD(CLANA/CLNOM0 CLANA00F)       *--------------------------------------------------------------                 R FMT01                     SFLCTL(SFL1)

#IBMiSample A simple way is to leave the file open by ending programs with RETURN instead of SETON LR. Example: Program A calls Program B. Program B opens a printerfile and write some pages in the spool file. Program B closes with RETURN. The printer file is already open. Program A calls Program B again. Program B opens a printerfile (it is already open) and write some other pages on the same spool file. Program B closes with RETURN. The printer file is already open. Program A calls Program B again. Program B opens a printerfile (it is already open) and write some other pages on the same spool file. Program B closes with RETURN. The printer file is already open. ... and so on. At the end, run RCLACTGRP ACTGRP(QILE). This command will end the QILE activation group, it means the spool file will also be closed. I appreciate all the comments made on this blog.

#IBMiSample Let's try with a very simple csv like this, called file1.csv: Code;Name 3741;Joe 3742;Barth 3810; 3945; First of all we need to save file1.csv into ISF . In my PUB400 (free AS400 at pub400.com) I have this directory: /home/PALDO I copy file1.csv to  /home/PALDO Use standard FTP transfer software (I use WinScp, I think Filezilla will work too) Let's take a look inside my directory I type WRKLNK and I verify the existence of File1.csv It looks as expected: To import this file I need a similar table on DB2. In my example it means that I will create a table with a decimal field and a charachter field . I create the table TEMPCSV (PALDO1 is my Library): CREATE TABLE PALDO1.TEMPCSV ( CLCCL0 DECIMAL(7, 0) DEFAULT 0 , CLNOM0 CHAR(50) DEFAULT ' ' ) RCDFMT FMTXX Here's my new table TEMPCSV : The table  TEMPCSV  is now created and it is empty: We can optionally set up a source file to note lines not imported by the CPYF

#IBMiSample How to get a primary key field that increments itself and a time field that updates itself? Look at  LGID00 and  LGDTTM fields. Create a table: CREATE TABLE LGCUS00F ( LGID00 INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, LGDTTM TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, LGNAME CHAR(50), LGSURN CHAR(50)) RCDFMT LGCUS Now try to add a record: INSERT INTO LGCUS00F (LGNAME, LGSURN) VALUES ('Aldo', 'Succi') Let's see the result: SELECT * FROM LGCUS00F As you can see, LGID00=1 and LGDTTM=2018-04-01-10.01.49.781922 That's it! I appreciate all the comments made on this blog.

#IBMiSample This looks like magic! The operating system, in a totally automatic way, keeps track of every change in the data. This simple technique allows you to find the value of a field on a certain date and time. use STRSQL Step 1) CREATE TABLE CUSTOMER (CUSTOMER_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1 ) ,CUSTOMER_NAME VARCHAR(10) ,ADDRESS VARCHAR(20)) Step 2) alter table customer add system_start timestamp(12) generated always as row begin not null add system_end timestamp(12) generated always as row end not null add tx_start timestamp(12) generated always as transaction start id implicitly hidden add period system_time (system_start, system_end) Step 3) create table customer_h like customer Step 4) alter table customer add versioning use history table customer_h Step 5) Now use command line: CRTJRNRCV JRNRCV(VDOTEST1/JRNRCVNAME) THRESHOLD(150) TEXT('Journal receiver') Step 6) CRTJRN JRN(VDOTEST1/jrnname) JRNRCV(VDOTEST1/jrnrcv

  #IBMiSample Remember that you need my CLANA00F file to compile the following program. So, f irst of all look at this post and compile and populate these files to use this example. A01ARPCL.CLP              PGM                                                              DCL        VAR(&PPCCL0) TYPE(*DEC)   LEN(7 0)                    DCL        VAR(&PPEXIT) TYPE(*CHAR)  LEN(1)                      DCL        VAR(&OUTPU1) TYPE(*CHAR)  LEN(1)                      DCL        VAR(&OUTPU7) TYPE(*CHAR)  LEN(7)                      DCL        VAR(&OUTPU8) TYPE(*CHAR)  LEN(8)                      CALL       PGM(A01ARP) PARM(&PPEXIT &PPCCL0)                     CHGVAR     VAR(&OUTPU1) VALUE(&PPEXIT)                           CHGVAR     VAR(&OUTPU7) VALUE(&PPCCL0)                           CHGVAR     VAR(&OUTPU8) VALUE(&OUTPU1 *CAT &OUTPU7)              SNDPGMMSG  MSG(&OUTPU8)                                          ENDPGM         

#IBMiSample This is a wery simple way to store passwords in a secure way in a DB2 SQL Server database. First of all create a table with only two fileds, user (UTENTE), and password (PWD). CREATE TABLE A01AWPWD (UTENTE varchar(128), PWD VARCHAR(124) FOR BIT DATA) Establish an encryption key. My encryption key for this example is skjdjuiu%Tr&$*+[be%_tr=\? Fill the table with users and passwords: INSERT INTO A01AWPWD (UTENTE, PWD) VALUES ('User01', ENCRYPT('Passw01', 'skjdjuiu%Tr&$*+[be%_tr=\?'))          INSERT INTO A01AWPWD (UTENTE, PWD) VALUES ('User02', ENCRYPT('Passw02', 'skjdjuiu%Tr&$*+[be%_tr=\?')) INSERT INTO A01AWPWD (UTENTE, PWD) VALUES ('JohnS', ENCRYPT('ert%9ii', 'skjdjuiu%Tr&$*+[be%_tr=\?')) Now we have a table A01AWPWD with ENCRYPTED passwords. Try this: SELECT PWD  FROM A01AWPWD  WHERE UTENTE = 'JohnS' As you can see, the password is ENCRYPTED. To read  (DECRYPT) the  passwords y