Executing a Query Statement String Within the Context of a Cursor (Embedded SQL Guide)

Go Up to Method 4: Query Statements with Parameters (Embedded SQL Guide)

To retrieve select-list items from a statement string, the string must be executed within the context of a cursor. All cursor declarations in InterBase are fixed, embedded statements inserted into the application before it is compiled. DSQL application developers must anticipate the need for cursors when writing the application and declare them ahead of time.

A looping construct is used to fetch a single row at a time from the cursor and to process each select-list item (column) in that row before the next row is fetched.

To execute a statement string within the context of a cursor and retrieve rows of select-list items, follow these steps:

1. Declare a cursor for the statement string. For example, the following statement declares a cursor, DYN_CURSOR, for the prepared SQL statement string, SQL_STMT:

   EXEC SQL
   DECLARE DYN_CURSOR CURSOR FOR SQL_STMT;
   

2. Open the cursor, specifying the input descriptor:

   EXEC SQL
   OPEN DYN_CURSOR USING SQL DESCRIPTOR in_sqlda;
   

   Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved. For more information about cursors and active sets, see Working with Data.
3. Fetch one row at a time and process the select-list items (columns) it contains. For example, the following loops retrieve one row at a time from DYN_CURSOR and process each item in the retrieved row with an application-specific function (here called process_column()):

   while (SQLCODE == 0)
   {
   EXEC SQL
   FETCH DYN_CURSOR USING SQL DESCRIPTOR out_sqlda;
   if (SQLCODE == 100)
   break;
   for (i = 0; i < out_sqlda->sqld; i++)
   {
   process_column(out_sqlda->sqlvar[i]);
   }
   }
   

4. When all the rows are fetched, close the cursor:

   EXEC SQL
   CLOSE DYN_CURSOR;