Understanding database result sets

This is an introduction to database result sets.

A database result set is a group of rows produced by an SQL statement such as 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.

Figure: Database result set

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.

Figure: FETCH instruction

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 reopened if needed. If the SQL statement is no longer needed, you can free the resources allocated to statement execution with the FREE instruction.

Figure: 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 in a statement in another file.

The language supports sequential cursors and scrollable cursors. Sequential cursors, which are unidirectional, are used to retrieve rows for a REPORT, for example. Scrollable cursors allow you to move backwards or to an absolute or relative position in the result set. Specify whether a cursor is scrollable with the SCROLL option of the DECLARE instruction.

For better code readability, use a FOREACH / END FOREACH loop, to perform the equivalent of an OPEN + FETCH (in WHILE loop) + CLOSE.