How You Can Use Cursors to Access the Database

• Bringing only part of a result set across the network where low communications bandwidth is an issue

• Performing an unrelated operation in the midst of processing returned rows (which can also be performed using a select loop)

• Performing operations on returned values that span two or more event blocks

• Determining which of several select statements in a fetch loop to execute at runtime, for example:

if frame_mode = MODE_SALES then
   open cursor1 for select salesname as name,
      salesquota as amount
      from sales where region = :region order
      by amount;
elseif frame_mode = MODE_MGRS then
   open cursor1 for select mgrname as name,
      budget as amount from managers
      where business_unit = :bus_unit
      order by name;

else
   open cursor1 for select staffname as name,
      salary as amount
      from staff where staff_code = :staff_code
      ORDER BY Amount;
endif;

i = 0;
curs_state = CS_CURRENT;
while (curs_state = CS_CURRENT) do
   i = i + 1;
   fetch cursor1 into :tf[i].name = name,
      :tf[i].amount = amount;
      curs_state = cursor1.state;
endwhile;
/* number of rows fetched = i-1 */
close cursor1;

• Providing better performance than a select loop when the application plans to limit the number of rows selected

Select loops cache every row that qualifies for the select. Setting DBSessionObject's PreFetchRow attribute when using a read-only cursor lets you limit the number of qualifying rows actually fetched.

For example, suppose you want to fill a tablefield with the top 10 salespeople (by sales) from a particular region. Using a select loop, you would code:

i = 1;
select :tf[i].name = name, :tf[i].quota = quota,
    :tf[i].sales = sales
    from salespeople where region = :region
    order by sales desc
    {
        i = i + 1;
        if i > 10 then
        endloop;
        endif;
    }

This technique is inefficient because all salespeople rows for the specified region are returned to OpenROAD from the database when only 10 are desired. The following example is a more efficient way to code this situation:

CurFrame.DBsession.PrefetchRows = 10;
open cursor1 for select name as name,

quota as quota,
sales as sales
from salespeople where region = :region
order by sales desc for readonly;

curs_state = CS_CURRENT;
while (curs_state = CS_CURRENT) do

/* number of rows fetched = i-1 */
close cursor1;

The PreFetchRows attribute enables you to tune the performance of cursors when you have an approximate idea of the number of rows the cursor will return.