IBMi (AS400) fans only ‘ how to read a flat file with sql embedded

#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      *

      *******************************************************

       ctl-opt option(*nodebugio:*srcstmt:*nounref);

       // IPFLI00F This file is readed from the beginning

       dcl-ds RecordDsC1;

         rDsIIFANN char( 1);

         rDsIITEXT char(25);

       end-ds;

       exec sql set option Commit = *None;

       exec sql set option SRTSEQ = *LANGIDSHR;

       exec sql set option CLOSQLCSR = *ENDMOD;

       // preapre the data recordset

       exec sql declare C1 cursor for

         select

                IIFANN,

                IITEXT

         from IPFLI00F

         where

             IIFANN <> 'A'

         order by IITEXT

         for read only;

      *******************************************************

      * PGM start                                           *

      *******************************************************

       exec sql close C1;

       exec sql open C1;

       dow sqlCode = 0;

         exec sql fetch C1 into :RecordDsC1;

         if sqlcode = 0;

           // ... do something

         endif;

       enddo;

       exec sql close C1;

       *inlr = *on; 




Type call ipfl01 (maybe under DEBUG) and check the program.

I use this template whenever I need to read an entire file.

Instead of // ... do something try

dsply %subst(rDsIITEXT:1:10);

That's it.

I appreciate all the comments made on this blog.