Selecting Multiple Rows in DSQL

From InterBase

Go Up to Working with Data

In DSQL users are usually permitted to specify queries at run time. To accommodate any type of query the user supplies, DSQL requires the use of extended SQL descriptor areas (XSQLDA) where you prepare and describe a query’s input and output. For queries returning multiple rows, DSQL supports variations of the DECLARE CURSOR, OPEN, and FETCH statements that make use of the XSQLDA.

To retrieve multiple rows into a results table, establish a cursor into the table, and process individual rows in the table. DSQL provides the following sequence of statements:

1. PREPARE establishes the user-defined query specification in the XSQLDA structure used for output.
2. DECLARE CURSOR establishes a name for the cursor and specifies the query to perform.
3. OPEN executes the query, builds the results table, and positions the cursor at the start of the table.
4. FETCH retrieves a single row at a time from the results table for program processing.
5. CLOSE releases system resources when all rows are retrieved.

The following three sections describe how to declare a DSQL cursor, how to open it, and how to fetch rows using the cursor. For more information about ­creating and filling XSQLDA structures, and preparing DSQL queries with ­PREPARE, see Using Dynamic SQL. For more information about closing a cursor, see Closing the Cursor.


Advance To: