Use SQL to read lots of rows (records) into a data structure (array) 

 November 29, 2014

By  NickLitten

Us IBM i blokes love reading things from files. Hashtag internet fact. But sometimes we just want to read a certain number of records, or rows in SQL speak. For example, when building a page by page subfile with say 15 lines per page, we might want to be able to read an entire file, but read it in 15 row chunks. In native IO style RPG, we would probably set a counter and increment it by one for each row read, stopping at 15. But using SQL for IO it does all that counting for us  🙂

This means that in most cases we can just wrap the read a DOW loop and fetch until the cows come home i.e. nothing more to fetch.

Lets look at a quick and dirty example reading 15 rows at a time into a data structure with 15 elements.

Using SQL for Single Page Subfiles

 // read the items 
 FETCH from (someIncrediblyInterestingFile)
 For 15 rows 
 into :data_structure
 // now write the sfl1 recs 
 for x1 = 1 to 15; 
   %occur(data_structure) = x1; --> this is for MODS 
   ... do stuff with the data_structure fields


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • HI,
    The way I usually do it is:
    exec sql open mycursor;
    dou nbrrecs < 500; exec sql fetch mycursor for 500 rows into array; nbrrecs = sqlerrd(3); for x1 =1 to nbrrecs; // do stuff with occurrence x1 endfor; enddo; exec sql close mycursor; This way I fixed an sqlrpgle program that fetched one record at a time from running for 52 hours to 26 minutes. Btw: The max number of rows to read is 32767. But there is also a maximum size (bytes) for the array or data structure. This number depends on the release of your os. Cheers, Rene Simons

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Get In Touch

    I’m always looking for awesome input, feedback and critique!


    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"