isc_dsql_fetch()

From InterBase
Jump to: navigation, search

Go Up to API Function Reference


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.

See Also

Advance To: