SQL’s Other Fetch Options
May 13, 2009 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.
|
