How do I iterate over a set of records in RPG(LE) with embedded SQL?

Question

How do I iterate over a set of records in RPG(LE) with embedded SQL?

Malady

Answer 1

Usually I'll create a cursor and fetch each record.

   //***********************************************************************
   // Main - Main Processing Routine
   begsr Main;

     exsr BldSqlStmt;

     if OpenSqlCursor() = SQL_SUCCESS;

       dow FetchNextRow() = SQL_SUCCESS;
         exsr ProcessRow;
       enddo;

       if sqlStt = SQL_NO_MORE_ROWS;
         CloseSqlCursor();
       endif;

     endif;

     CloseSqlCursor();

   endsr;    // Main 

I have added more detail to this answer in a post on my website.

Mouthful

Hi @Mike Wills, I was interested to read more detail about this but the link to your website is broken. – Spoilsport

Answer 2

As Mike said, iterating over a cursor is the best solution. I would add to give slightly better performance, you might might want to fetch into an array to process in blocks rather than one record at a time.

Example:

  EXEC SQL                                                          
  OPEN order_history;                                             

  // Set the length                                                 
  len = %elem(results);                                             

  // Loop through all the results                                   
  dow (SqlState = Sql_Success);                                     
    EXEC SQL                                                        
      FETCH FROM order_history FOR :len ROWS INTO :results;         
    if (SQLER3 <> *zeros);                                                 
      for i = 1 to SQLER3 by 1;        
        // Load the output             
        eval-corr output = results(i); 
        // Do something 
      endfor;                          
    endif;                             
  enddo;                               

HTH, James R. Perkins

Interdental

I like this... I haven't done that before. I'll have to try it on large recordsets. – Mouthful