isc_dsql_fetch()

Retrieves data returned by a previously prepared and executed DSQL statement.

Syntax

 ISC_STATUS isc_dsql_fetch(
 ISC_STATUS *status_vector,
 isc_stmt_handle *stmt_handle,
 unsigned short da_version,
 XSQLDA *xsqlda);

Parameter	Type	Description
`status_vector`	`ISC_STATUS` `*`	Pointer to the error status vector
`stmt_handle`	`isc_stmt_handle *`	Pointer to a statement handle previously allocated with `isc_dsql_allocate_statement()` or `isc_dsql_alloc_statement2`(); the handle returns an error in `status_vector` if it is `NULL`.
`da_version`	`unsigned short`	Specifies that the XSQLDA descriptor, rather than SQLDA, should be used; set this value to 1.
`xsqlda`	`XSQLDA` `*`	Pointer to an optional, previously allocated `XSQLDA` used for results of statement execution.

Description

isc_dsql_fetch() retrieves one row of data into xsqlda each time it is called. It is used in a loop to retrieve and process each row of data for statements that return multiple rows in a cursor.

A cursor is a one-way pointer into the ordered set of rows retrieved by a statement. 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.

It is up to the application to provide the loop construct for fetching the data.

Before calling isc_dsql_fetch(), a statement must be prepared with isc_dsql_prepare(), and executed with isc_dsql_execute() (or isc_dsql_execute2() with a NULL output xsqlda argument). Statement execution produces a result set containing the data returned. Each call to isc_dsql_fetch() retrieves the next available row of data from the result set into xsqlda.

Example

The following program fragment illustrates a sequence of calls that allocates an output XSQLDA, prepares a statement for execution, allocates an XSQLVAR structure in the XSQLDA for each column of data to be retrieved, executes the statement, producing a select list of returned data, then fetches and processes each row in a loop:

#include <ibase.h>
#define LASTLEN 20
#define FIRSTLEN 15
#define EXTLEN 4
typedef struct vary {
short vary_length;
char vary_string[1];
} VARY;
ISC_STATUS status_vector[20], retcode;
long SQLCODE;
XSQLDA *osqlda;
XSQLVAR *ovar;
short flag0, flag1, flag2;
char *str = "SELECT last_name, first_name, phone_ext FROM phone_list
WHERE location = "Monterey" ORDER BY last_name, first_name";
char last_name[LASTLEN + 2];
char first_name[FIRSTLEN + 2];
char phone_ext[EXTLEN + 2];
VARY *vary;
/* Allocate an output XSQLDA osqlda. */
osqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(3);
osqlda->version = SQLDA_CURRENT_VERSION;
osqlda->sqln = 3;
/* Prepare the statement. */
isc_dsql_prepare(status_vector,
&tr_handle, /* Set in previous isc_start_transaction() call. */
&stmt_handle, /* Allocated previously by
 * isc_dsql_allocate_statement()
 * or isc_dsql_alloc_statement2() call. */
0, str, 1, osqlda);
if (status_vector[0] == 1 && status_vector[1]) {
/* Process error. */
isc_print_status(status_vector);
return(1);
}
/* Set up an output XSQLVAR structure to allocate space for each item to be returned. */
osqlda->sqlvar[0].sqldata = last_name;
osqlda->sqlvar[0].sqltype = SQL_VARYING + 1;
osqlda->sqlvar[0].sqlind = &flag0;
osqlda->sqlvar[1].sqldata = first_name;
osqlda->sqlvar[1].sqltype = SQL_VARYING + 1;
osqlda->sqlvar[1].sqlind = &flag1;
osqlda->sqlvar[2].sqldata = phone_ext;
osqlda->sqlvar[2].sqltype = SQL_VARYING + 1;
osqlda->sqlvar[2].sqlind = &flag2;
/* Execute the statement. */
isc_dsql_execute(status_vector,
&tr_handle, /* Set in previous isc_start_transaction() call. */
&stmt_handle, /* Allocated previously by
 * isc_dsql_allocate_statement()
 * or isc_dsql_alloc_statement2() call. */
1, NULL);
if (status_vector[0] == 1 && status_vector[1]) {
/* Process error. */
isc_print_status(status_vector);
return(1);
}

printf("\n%-20s %-15s %-10s\n\n", "LAST NAME", "FIRST NAME", "EXTENSION");
/* Fetch and print the records in the select list one by one. */
while ((retcode = isc_dsql_fetch( status_vector, &stmt_handle,
1, osqlda)) == 0) {
vary = (VARY *)last_name;
printf("%-20.*s ", vary->vary_length, vary->vary_string);
vary = (VARY *)first_name;
printf("%-15.*s ", vary->vary_length, vary->vary_string);
vary = (VARY *)phone_ext;
printf("%-4.*s ", vary->vary_length, vary->vary_string);
}
if (retcode != 100L) {
SQLCODE = isc_sqlcode(status_vector);
isc_print_sqlerror(SQLCODE, status_vector);
return(1);
}

Return value

isc_dsql_fetch() returns the second element of the status vector. Zero indicates success. The value 100 indicates that no more rows remain to be retrieved. Any other nonzero value indicates an error. For InterBase errors, the first element of the status vector is set to 1, and the second element is set to isc_bad_stmt_handle, or another InterBase error code.

To check for an InterBase error, examine the first two elements of the status vector directly. For more information about examining the status vector, see Handling Error Conditions.

Advance To:

isc_dsql_free_statement()