Programming Guide : 5. Working with a Database : How You Can Access a Database with Standard SQL Statements : How You Can Use Cursors to Access the Database
 
Share this page                  
How You Can Use Cursors to Access the Database
Cursors are useful for:
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;
i = 0;
curs_state = CS_CURRENT;
while (curs_state = CS_CURRENT) do
i = i + 1;
fetch cursor1 into :tf[i].name = name,
    :tf[i].quota = quota, :tf[i].sales =
        sales;
curs_state = cursor1.state;
if i > 10 then
    endloop;
endif;
endwhile;
/* 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.
How You Can Use the CursorObject System Class
In OpenROAD, cursors are implemented as objects of the CursorObject system class. Using a cursor consists of the following steps:
1. Declare a CursorObject reference variable.
2. Open the cursor, specifying whether the cursor can update or delete rows that are fetched.
3. Fetch a row and move the values in the row into variables.
4. Optionally update or delete the row on which the cursor is positioned, using the values loaded into the variables.
5. Generally, repeat Steps 3 and 4 until all the rows in the result table have been processed.
6. Close the cursor.
It is only necessary to declare a cursor once in a program. You can open it more than once, if you close it between each opening.
Note:  Opening a DataStream object in QY_CURSOR mode provides the same functions as using a cursor. For a discussion of using DataStream objects, see How You Can Access a Database with DataStream Objects.
CursorObject Attributes
The CursorObject class has three attributes that provide status information:
State
Contains information about the state of the cursor, such as whether it is open or closed
RowCount
Indicates how many rows have been fetched successfully by the cursor since the cursor was opened. When a cursor is opened, RowCount is set to 0. Each subsequent successful fetch statement for that cursor increments RowCount by 1.
When you close the cursor, the RowCount attribute is not reset to 0; it retains whatever value it had before the close statement executed. It is reset only if you reopen the cursor.
DBSession
Identifies the session in which the cursor was opened. After you open a cursor, all subsequent cursor operations for that cursor automatically take place in the session in which the cursor was opened. If the frame is working in a different session, OpenROAD automatically switches sessions for the cursor operation and switches back when the cursor statement completes.
How You Can Declare a Cursor
Before you can open a cursor, you must create the CursorObject object for the new cursor by declaring a global or local reference variable of type CursorObject. The syntax is:
cursor_var_name = CursorObject
For example, the following code line declares a reference variable named cust_cursor that points to a cursor object:
cust_cursor = CursorObject;
How You Can Open and Position a Cursor
You can use the open statement to perform the following operations:
Open a cursor in the database, specifying whether:
The cursor can update or delete rows that are fetched
Updates should be deferred or direct
Associate a select statement with the cursor and position the cursor immediately before the first row in the select statement's result table
Set the cursor's State attribute to CS_OPEN
To access the rows in the result table, use the fetch statement. This statement performs the following tasks:
Advances the cursor one row in the result table
Places the values in the indicated columns into the specified variables
Sets the cursor's State and RowCount attributes
Each execution of a fetch statement fetches one row of the result table. The syntax lets you retrieve the values from every column in the result table or from some subset of the columns.
If you fetch all of the retrieved values in the order specified in the select statement, you do not need to name the columns in the fetch statement.
For example, assume you issued the following open statement to retrieve the values in eight columns from the customer table:
open cust_cursor for select acctno, cphone,
    cname, caddr, ccity, cdistrict, cstatus,
    cacctbal
from customer for direct update of acctno,
    cphone, cname, caddr, ccity, cdistrict,
    cstatus, cacctbal;
To fetch the values from all eight columns in the same order specified in the select statement, use the following statement:
fetch cust_cursor into :acctno, :cphone,
    :cname, :caddr, :ccity, :cdistrict,
    :cstatus, :cacctbal;
Because the variables in the fetch statement match the number, data type, and order of specification of the select statement's expressions, you need not specify column names. The fetch statement puts the retrieved values (one row at time) into the specified variables.
Similarly, if you fetch a subset that begins with the first column returned by the select, continues in the order listed in the select, and stops before all are listed, it is not necessary to specify column names. For example, the following statement fetches the first, second, and third columns (acctno, cphone, cname) returned by the select:
fetch cust_cursor into :acctno, :cphone, :cname;
However, whenever you fetch column values out of order (that is, do not start with the first selected column and continue in the order of the select), you must identify the column. For example, to fetch only the customer's name and status using the example open statement, specify the column names:
fetch cust_cursor into :cname=cname,
    :cstatus=cstatus;
Whether you fetch all or some of the selected column values, the variables that receive the column values and their associated columns must have compatible data types.
For more information about the open and fetch cursor statements, see the Language Reference Guide.
How You Can Update or Delete a Row
After you have fetched a row, you can update values in the row or delete the row if the cursor was opened to allow for updates and deletes. There are versions of the update and a delete 4GL statement specifically for use with cursors.
For example, the following code updates the customer record on which the cursor is currently positioned:
on click update_button =
begin
/* Actually update the customer in the database */
    if menu.get_menu.initial_account > 2 then
        update customer
        set acctno = :customer.acctno
        cphone = :customer.cphone,
        cname = :customer.cname,
        caddr = :customer.caddr,
        ccity = :customer.ccity,
        cstate = :customer.cstate,
        czip = :customer.czip,
        cdistrict = :customer.cdistrict,
        cstatus = :customer.cstatus,
        cacctbal = :customer.cacctbal
        where current of cust_cursor;
...
The following code provides an example of deleting the row on which the cursor is positioned:
on click delete_button =
begin
    if menu.get_menu.initial_account > 2 then
        delete from customer
        where current of cust_cursor;
...
For more information about the cursor version of the update and delete statements, see the Language Reference Guide.
How You Can Close a Cursor
When the program has processed all the required rows in the result table, close the cursor. Closing a cursor:
Sets the cursor's State attribute to CS_CLOSED
Makes changes visible to the rest of the program (if the cursor was a deferred update cursor)
Releases any locks held by the select statement associated with the cursor (if autocommit is on)
For a description of autocommit, see How Running with Autocommit On Works.
If you reopen a closed cursor, the cursor is repositioned at the top of the result table.
The following statement provides an example of closing a cursor:
close cust_cursor;
In addition to being closed explicitly by the close statement, cursors are also closed implicitly when the variable representing them goes out of scope. For example, if the cursor object is referenced by a variable declared locally to a frame and the frame closes without explicitly closing the cursor object, the cursor object is closed automatically.
How You Can Manage Transactions with Cursors
Each cursor must be opened and closed within a single transaction. Moreover, the state of the transaction affects the state of the cursor. For example, a cursor is automatically closed in either of the following cases:
Its transaction is closed by either a commit or rollback.
Its transaction is aborted by an error.
Some errors (for example, deadlock, logfull, and disk full) do not abort an entire transaction but do close any open cursors. If an error occurs while a cursor is open, you should roll back the transaction and begin it again.
If you are running with autocommit on (each database statement is a separate transaction), a cursor is considered one transaction from the time it is opened until it is closed. Therefore, when autocommit is on and a cursor is open, you can issue only cursor statements to access the database. If you issue any other database access statements, the open cursor is closed automatically.
For an explanation of using autocommit, see How Running with Autocommit On Works.
You can use the inquire_sql statement with the transaction parameter to determine the transaction state. For more information about the inquire_sql statement, see Handling Database Errors or the online help.