Executing a Query Statement String Within the Context of a Cursor

Go Up to Method 4: Query Statements With Parameters

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. Execute the statement with isc_dsql_execute2():

   isc_dsql_execute2(status_vector, &trans, &stmt, 1, in_xsqlda, out_xsqlda);
   

2. Declare and open a cursor for the statement string with ­isc_dsql_set_cursor_name(). For example, the following statement declares a cursor, dyn_cursor, for the prepared SQL statement string, stmt:

   isc_dsql_set_cursor_name(status_vector, &stmt, "dyn_cursor", NULL);
   

   Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved.
3. Fetch one row at a time with isc_dsql_fetch() 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 called ­process_column():

   while ((fetch_stat = isc_dsql_fetch(status_vector, &stmt, 1, out_sqlda)) == 0)
   {for (i = 0; i < out_sqlda->sqld; i++)
   {process_column(sqlda->sqlvar[i]);
   }
   }
   if (fetch_stat != 100L) {
   /* isc_dsql_fetch returns 100 if no more rows remain to be retrieved */
   SQLCODE = isc_sqlcode(status_vector);
   isc_print_sqlerror(SQLCODE, status_vector);
   return(1);
   }
   

   When all the rows are fetched, close the cursor with ­isc_dsql_free_statement():

   isc_dsql_free_statement(status_vector, &stmt, DSQL_close);
   

Advance To:

• Re-executing a Query Statement String With Parameters