SQL's Other Fetch Options

Published: May 13, 2009

by Ted Holt

I consider fetch a great word, and lament that it is no longer used in daily English, at least not where I live, except maybe when speaking to dogs. Fortunately, fetch is still used heavily in SQL. High-level language programs need it to convert set-at-a-time processing into row-at-a-time processing. Fetch can do more than read a result set from beginning to end. Do you know what else it can do?

From Top to Bottom

For starters, let's look at the most common use of fetch--to read an SQL cursor from beginning to end. The following highly sophisticated demonstration program shows how it's done.

Fqsysprt   o    f  132        printer 

D Customer        ds                  qualified inz 
D   Number                       6p 0 
D   LastName                     8a 
D   Initials                     3a 
D   City                         6a 
D   State                        2a 
D   BalanceDue                   7p 2 

D SqlEof          c                   const('02000') 
D DetailLine      s            132a  

D Print           pr       
D   Data                       132a   value

 /free 
     *inlr = *on; 
     exec sql 
        declare Input cursor for 
           select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
             from qcustcdt 
            where baldue <> 0 
            order by baldue desc; 

     exec sql 
        open Input; 

     dow '1'; 
        exec sql 
           fetch Input into :Customer; 
        if sqlstt >= SqlEof; 
           leave; 
        endif; 
        Print (%editc(Customer.BalanceDue:'J') + ' ' + 
               Customer.LastName + ' ' + 
               Customer.Initials + ' ' + 
               %editc(Customer.Number:'4') + ' ' + 
               %trimr(Customer.City) + ', ' + Customer.State);
     enddo;      
     return;  
 /end-free 

Oqsysprt   e            PrintLine   1 
O                       DetailLine 
 * ==========================================================
P Print           b 
D                 pi 
D   Data                       132a   value 
 /free 
     DetailLine = Data; 
     except PrintLine; 
 /end-free 
P                 e 

Notice the cursor declaration at the top of the free-format calculation specs. I chose to name my cursor Input.

exec sql  
   declare Input cursor for 
      select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
        from qcustcdt 
       where baldue <> 0 
       order by baldue desc; 

Notice the open.

exec sql 
   open Input; 

Notice the fetch.

fetch Input into :Customer; 

This command tells the system to retrieve one row of the result set and place the data into data structure Customer.

This form of fetch is abbreviated. Here is the full fetch command.

fetch next from Input into :Customer;

Also, in order to keep the example succinct, I omitted error handling.

From Bottom to Top

You may also read a cursor backward. The following modifications to the previous example show how.

exec sql  
   declare Input scroll cursor for 
      select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
        from qcustcdt 
       where baldue <> 0 
       order by baldue desc; 

exec sql  
   open Input; 
exec sql 
   fetch after from Input; 

dow '1'; 
   exec sql 
      fetch prior from Input into :Customer; 
   if sqlstt >= SqlEof; 
      leave; 
   endif; 
   Print ( ... etc. ...)
enddo; 

First, notice the cursor declaration. I've added the scroll keyword in order to make the cursor scrollable. That is, the cursor permits random access.

Second, FETCH AFTER positions to the end of the record set. This is similar to using the SETGT in native RPG I/O in order to position to the end of the file. (FETCH BEFORE positions to the beginning of the file, by the way.)

Third, notice the FETCH PRIOR operation. Like RPG's READP opcode, this fetch retrieves the previous row in the return set.

Fetch First and Last

You can retrieve the first and last rows of an SQL query. You'll need a scrollable cursor, of course. Use FETCH FIRST and FETCH LAST, as in the following example.

exec sql 
   declare Input scroll cursor for 
      select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
        from qcustcdt 
       where baldue <> 0 
       order by baldue desc; 

exec sql 
   open Input; 
exec sql 
   fetch last from Input into :Customer; 
   ... do something ...
exec sql 
   fetch first from Input into :Customer; 
   ... do something ...

Read It Again

At times you may need to re-read a fetched record. This feature is helpful when the data may have changed in the database and you want to refresh the data in your host variables. Use FETCH CURRENT to re-read the row that was last fetched. Here's how it's done.

/free 
exec sql 
   declare Input sensitive scroll cursor for 
      select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
        from qcustcdt 
       where baldue <> 0 
       order by baldue desc; 

     exec sql 
   open Input; 

exec sql 
   fetch first from Input into :Customer; 

.... more stuff ....


// re-read
exec sql 
   fetch current from Input into :Customer; 

Take a look at the cursor definition. I've added the word SENSITIVE to indicate that I want the cursor to reflect the latest changes to the database.

I used FETCH FIRST to retrieve the first row from the result set. Later, FETCH CURRENT re-reads the same row from the cursor.

Fetch Relative

Normal practice is to read a cursor sequentially, but FETCH RELATIVE allows a deviation from the practice. The form is FETCH RELATIVE n, where n is the number of rows before (if n is negative) or after (if n is positive) the last fetched row. The following code reads the odd-numbered rows from the result set, by reading the first row, then every second row following.

exec sql 
   declare Input scroll cursor for 
      select CUSNUM, LSTNAM, INIT, CITY, STATE, BALDUE
        from qcustcdt 
       where baldue <> 0 
       order by baldue desc; 

exec sql 
   open Input; 

exec sql 
   fetch first from Input into :Customer; 

dow sqlstt < SqlEof; 
   Print (... etc. ...);
   exec sql 
      fetch relative +2 from Input into :Customer; 
enddo; 

If Only. . .

I hope this brief look at FETCH gives you some fodder for new ideas. I am asked from time to time if SQL allows random positioning over a cursor by key, similar to the way that RPG SETLL and COBOL START allow random positioning over a keyed file. I wish it were so, but it is not. Shucks! That would be handy.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot