Executing a Statement String Within the Context of a Cursor

Go Up to Method 3: Query Statements Without Parameters

To retrieve select-list items from a prepared statement string, the string can be executed within the context of a cursor. All cursor declarations in InterBase are fixed 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 cursor is only needed to process positioned UPDATE and DELETE statements made against the rows retrieved by isc_dsql_fetch() for SELECT statements that specify an optional FOR UPDATE OF clause.

The following descriptions apply to the situations when a cursor is needed. For an example of executing a statement and fetching rows without using a cursor, see isc_dsql_fetch().

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:

Execute the prepared statement with isc_dsql_execute():

isc_dsql_execute(status_vector, &trans, &stmt, 1, NULL);

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 SQL statement string, stmt:
```
isc_dsql_set_cursor_name(status_vector, &amp;stmt,
"dyn_cursor", NULL);
```
Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved.

Fetch one row at a time and process the select-list items (columns) it contains with isc_dsql_fetch(). 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);
}

The process_column() function mentioned in this example processes each returned select-list item. The following skeleton code illustrates how such a function can be set up:

void process_column(XSQLVAR *var)
{
/* test for NULL value */
if ((var->sqltype & 1) && (*(var->sqlind) = -1)) {
/* process the NULL value here */
}
else {
/* process the data instead */
}
. . .
}

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 Without Parameters