Selecting Multiple Rows in DSQL
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:
PREPARE
establishes the user-defined query specification in theXSQLDA
structure used for output.DECLARE CURSOR
establishes a name for the cursor and specifies the query to perform.OPEN
executes the query, builds the results table, and positions the cursor at the start of the table.FETCH
retrieves a single row at a time from the results table for program processing.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.