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.
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)
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.