Summary:
See also: Transactions, Positioned Updates, Static SQL, Dynamic SQL, SQL Errors.
A Database Result Set is a set of rows generated by an SQL statement producing rows (like SELECT). The result set is maintained by the database server. In a program, you handle a result set with a Database Cursor.
First, you must declare the database cursor with the DECLARE instruction. This instruction sends the SQL statement to the database server for parsing, validation and to generate the execution plan.
The result set is produced after execution of the SQL statement, when the database cursor is associated with the result set by the OPEN instruction. At this point, no data rows are transmitted to the program. You must use the FETCH instruction to retrieve data rows from the database server.
When finished with the result set processing, you must CLOSE the cursor to release the resources allocated for the result set on the database server. The cursor can be re-opened if needed. If the SQL statement is no longer needed, you can free the resources allocated to the statement execution with the FREE instruction.
The scope of reference of a database cursor is local to a module, so a cursor that was declared in one source file cannot be referenced from a statement in another file.
The language supports sequential and scrollable cursors. Sequential cursors, which are unidirectional, are used to retrieve rows to be printed in a report, for example. Scrollable cursors allow you to move backwards or to an absolute or relative position in the result set. You specify whether a cursor is scrollable with the SCROLL option of the DECLARE instruction.
This instruction associates a database cursor with an SQL statement in the current connection.
DECLARE cid [SCROLL] CURSOR [WITH
HOLD] FOR select-statement
DECLARE cid [SCROLL] CURSOR [WITH
HOLD] FOR sid
DECLARE cid [SCROLL] CURSOR [WITH
HOLD] FROM expr
If you use only the DECLARE CURSOR keywords, you create a sequential cursor, which can fetch only the next row in sequence from the result set. The sequential cursor can read through the result set only once each time it is opened. If you are using a sequential cursor for a select cursor, on each execution of the FETCH statement, the database server returns the contents of the current row and locates the next row in the result set.
Example 1: Declaring a cursor with a static SELECT statement.
01MAIN02DATABASE stores03DECLARE c1 CURSOR FOR SELECT * FROM customer04END MAIN
Example 2: Declaring a cursor with a prepared statement.
01MAIN02DEFINE key INTEGER03DEFINE cust RECORD04num INTEGER,05name CHAR(50)06END RECORD07DATABASE stores08PREPARE s109FROM "SELECT customer_num, cust_name FROM customer WHERE customer_num>?"10DECLARE c1 CURSOR FOR s111LET key=10112FOREACH c1 USING key INTO cust.*13DISPLAY cust.*14END FOREACH15END MAIN
Use the DECLARE SCROLL CURSOR keywords to create a scrollable cursor, which can fetch rows of the result set in any sequence. Until the cursor is closed, the database server retains the result set of the cursor in a static data set (for example like Informix, in a temporary table). You can fetch the first, last, or any intermediate rows of the result set as well as fetch rows repeatedly without having to close and reopen the cursor. On a multi-user system, the rows in the tables from which the result set rows were derived might change after the cursor is opened and a copy is made in the static data set. If you use a scroll cursor within a transaction, you can prevent copied rows from changing either by setting the isolation level to Repeatable Read or by locking the entire table in share mode during the transaction. Scrollable cursors cannot be declared FOR UPDATE.
The DECLARE [SCROLL] CURSOR FROM syntax allows you to declare a cursor directly with a string expression, so that you do not have to use the PREPARE instruction. This simplifies the source code and speeds up the execution time for non-Informix databases, because the SQL statement is not parsed twice.
Example 3: Declaring a scrollable cursor with string expression.
01MAIN02DEFINE key INTEGER03DEFINE cust RECORDs04num INTEGER,05name CHAR(50)06END RECORD07DATABASE stores08DECLARE c1 SCROLL CURSOR09FROM "SELECT customer_num, cust_name FROM customer WHERE customer_num>?"10LET key=10111FOREACH c1 USING key INTO cust.*12DISPLAY cust.*13END FOREACH14END MAIN
Informix only: Use the WITH HOLD option to create a hold cursor. A hold cursor allows uninterrupted access to a set of rows across multiple transactions. Ordinarily, all cursors close at the end of a transaction. A hold cursor does not close; it remains open after a transaction ends. A hold cursor can be either a sequential cursor or a scrollable cursor. Hold cursors are only supported by Informix database engines.
You can use the ? question mark place holders with prepared or static SQL statements, in order to provide the parameters at execution time, with the USING clause of the OPEN or FOREACH instructions.
Example 4: Declaring a hold cursor with ? parameter place holders.
01MAIN02DEFINE key INTEGER03DEFINE cust RECORDs04num INTEGER,05name CHAR(50)06END RECORD07DATABASE stores08DECLARE c1 CURSOR WITH HOLD09FOR SELECT customer_num, cust_name FROM customer WHERE customer_num > ?10LET key=10111FOREACH c1 USING key INTO cust.*12BEGIN WORK13UPDATE cust2 SET name=cust.cust_name WHERE num=cust.num14COMMIT WORK15END FOREACH16END MAIN
Executes the SQL statement associated with a database cursor declared in the same connection.
OPEN cid
[ USING paramvar [,...] ]
[ WITH REOPTIMIZATION ]
01MAIN02DATABASE stores03DECLARE c1 CURSOR FOR SELECT * FROM customer04OPEN c105END MAIN
Moves a cursor to a new row in the corresponding result set and retrieves the row values into fetch buffers.
FETCH [ direction ] cid [
INTO fetchvar [,...] ]
where direction is one of:
{
NEXT
| { PREVIOUS | PRIOR }
| CURRENT
| FIRST
| LAST
| ABSOLUTE position
| RELATIVE offset
}
01MAIN02DEFINE cnum INTEGER03DEFINE cname CHAR(20)04DATABASE stores05DECLARE c1 SCROLL CURSOR FOR SELECT customer_num, cust_name FROM customer06OPEN c107FETCH c1 INTO cnum, cname08FETCH LAST c1 INTO cnum, cname09FETCH PREVIOUS c1 INTO cnum, cname10FETCH FIRST c1 INTO cnum, cname11FETCH LAST c112FETCH FIRST c113END MAIN
Closes a database cursor and frees resources allocated on the database server for the result set.
CLOSE cid
01MAIN02DATABASE stores03DECLARE c1 CURSOR FOR SELECT * FROM customer04OPEN c105CLOSE c106OPEN c107CLOSE c108END MAIN
This instruction releases resources allocated to the database cursor with the DECLARE instruction.
FREE cid
01MAIN02DEFINE i, j INTEGER03DATABASE stores04FOR i=1 TO 1005DECLARE c1 CURSOR FOR SELECT * FROM customer06FOR j=1 TO 1007OPEN c108FETCH c109CLOSE c110END FOR11FREE c112END FOR13END MAIN
A FOREACH block applies a series of actions to each row of data that is returned from a database cursor.
FOREACH cid
[ USING paramvar [,...] ]
[
INTO fetchvar [,...] ]
[ WITH REOPTIMIZATION ]
{
statement
| CONTINUE FOREACH
| EXIT FOREACH
}
[...]
END FOREACH
Use the FOREACH instruction to retrieve and process database rows that were selected by a query. This instruction is equivalent to using the OPEN, FETCH, and CLOSE cursor instructions:
You must declare the cursor (by using the DECLARE instruction) before the FOREACH instruction can retrieve the rows. A compile-time error occurs unless the cursor was declared prior to this point in the source module. You can reference a sequential cursor, a scroll cursor, a hold cursor, or an update cursor, but FOREACH only processes rows in sequential order.
The FOREACH statement performs successive fetches until all rows specified by the SELECT statement are retrieved. Then the cursor is automatically closed. It is also closed if a WHENEVER NOT FOUND exception handler within the FOREACH loop detects a NOTFOUND condition (that is, SQL Code = 100).
01MAIN02DEFINE clist ARRAY[200] OF RECORD03cnum INTEGER,04cname CHAR(50)05END RECORD06DEFINE i INTEGER07DATABASE stores08DECLARE c1 CURSOR FOR SELECT customer_num, cust_name FROM customer09LET i=010FOREACH c1 INTO clist[i+1].*11LET i=i+112DISPLAY clist[i].*13END FOREACH14DISPLAY "Number of rows found: ", i15END MAIN