To retrieve a single row, omit the code block (enclosed in curly braces) that follows the retrieve statement. If more than one row fulfills the where clause of the query, the DBMS Server returns a single row, though not necessarily the first qualifying row.

To retrieve a set of rows, you must create a retrieve loop. To create a retrieve loop, follow the retrieve statement with a block of code enclosed in curly braces. The code block can contain a mixture of host language and EQUEL statements.

Within the retrieve loop code block, your application must not issue any other statements that access the database–this causes a runtime error. To see how rows and tables can be manipulated and updated while data is being retrieved, see Data Manipulation with Cursors on page 8.

To abort a retrieve loop, use the endretrieve statement. The endretrieve statement must be within the block delimited by curly braces. Do not use a host language goto statement or return statement to exit the loop: exiting a loop using a goto or return causes the next EQUEL statement that accesses the database to fail, and the DBMS Server displays a message indicating that database statements cannot be nested within a retrieve loop.

To find out how many rows have been retrieved, use the inquire_ingres statement with the rowcount parameter. Used within the retrieve loop, rowcount indicates the number of rows retrieved so far. Placed immediately following the loop, it indicates the total number of rows retrieved. After a non-looped retrieve, inquire_ingres(rowcount) returns the number of rows that met the where clause.

The results of the retrieval are assigned to the specified host variables. If no rows are retrieved, the contents of the host variables remains unchanged. You must use numeric variables to receive numeric results and string variables to receive string results. Each result variable can be associated with an indicator variable to detect null data. For more information, see Indicator Variables on page 4.

You can reduce the overhead required by frequently executed retrieve statements by specifying repeat retrieve. The repeat keyword directs the DBMS Server to encode the retrieve and save its execution plan when it is first executed. This encoding can improve performance on subsequent executions of the same retrieve.