isc_dsql_execute()

Go Up to API Function Reference

Executes a previously prepared DSQL statement. For more information about creating and populating the XSQLDA, see Understanding the XSQLDA.

Syntax

ISC_STATUS isc_dsql_execute( ISC_STATUS *status_vector, isc_tr_handle *trans_handle, isc_stmt_handle *stmt_handle, unsigned short da_version, XSQLDA *xsqlda);

Parameter	Type	Description
`status_vector`	`ISC_STATUS` `*`	Pointer to the error status vector
`trans_handle`	`isc_tr_handle *`	Pointer to a transaction handle whose value has been set by a previous `isc_start_transaction()` call; `trans_handle` returns an error if `NULL`.
`stmt_handle`	`isc_stmt_handle *`	Pointer to a statement handle previously allocated with `isc_dsql_allocate_statement()` or `isc_dsql_alloc_statement2`(); returns an error in status_vector if `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 a previously allocated `XSQLDA` used for input.

Description

isc_dsql_execute() executes a DSQL statement previously prepared with isc_dsql_prepare(). isc_dsql_execute() can be used to execute two types of statements:

Statements that may return more than one row of data.
Statements that need to be executed more than once.

If a statement to execute has input parameters, then isc_dsql_execute() requires an input XSQLDA to describe those parameters. It does not provide for an output XSQLDA. A call to isc_dsql_execute() that executes a SELECT statement results in the creation of a list containing all the rows of data that are the result of execution of the statement. To access these rows, call isc_dsql_fetch() in a loop. Each call to isc_dsql_fetch() fetches the next row from the select-list.

If the statement to be executed requires input parameter values (that is, if it contains parameter markers), these values must be supplied in the input XSQLDA xsqlda before calling isc_dsql_execute().

Note: To execute a statement repeatedly when it both has input parameters and return values, such as EXECUTE PROCEDURE, use isc_dsql_execute2() which requires both an input and an output XSQLDA.

If you only need to execute a statement once, and it does not return any data, call isc_dsql_execute_immediate() instead of isc_dsql_prepare() and isc_dsql_execute(). To execute a statement with both input and output parameters a single time, use isc_dsql_exec_immed2().

Note: CREATE DATABASE and SET TRANSACTION cannot be executed with isc_dsql_execute() or isc_dsql_execute2(). To execute these statements, use isc_dsql_execute_immediate().

Example

The following program fragment illustrates calls to isc_dsql_execute() and isc_dsql_fetch(). It allocates input and output XSQLDA, prepares a SELECT statement, executes it, and fetches and processes each row one-by-one.

#include <ibase.h>
ISC_STATUS status_vector[20], fetch_stat;
XSQLDA *isqlda, *osqlda;
XSQLVAR *ivar, *ovar;
char *str = "SELECT CITY, POPULATION FROM CITIES WHERE STATE = ?";
char *state = "CA";
/* Allocate an output XSQLDA osqlda. */
osqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(2);
osqlda->version = SQLDA_CURRENT_VERSION;
osqlda->sqln = 2;

/* Prepare the statement, including filling in osqlda with information about
 * the select-list items to be returned by 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);
}

/* Check to see whether or not the output XSQLDA had enough XSQLVARS allocated.
 * If not, correct it -- see isc_dsql_describe(). */

/* Allocate and fill in the input XSQLDA. This example assumes you know how
 * many input parameters there are (1), and all other information necessary
 * to supply a value. If this is not true, you will need to call
 * isc_dsql_describe_bind(). */
isqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(1));
isqlda->version = SQLDA_CURRENT_VERSION;
isqlda->sqln = 1;
isqlda->sqld = 1;
ivar = isqlda->sqlvar[0];
ivar->sqltype = SQL_TEXT;
ivar->sqllen = sizeof(state);
ivar->sqldata = state;

/* 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, isqlda);
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. */
for (i=0, ovar = osqlda->sqlvar; i < osqlda->sqld; i++, ovar++) {
    dtype = (ovar->sqltype & ~1) /* Drop NULL bit for now. */
    switch(dtype) {
      case SQL_TEXT:
        ovar->sqldata = (char *)malloc(sizeof(char) * ovar->sqllen);
        break;
      case SQL_LONG:
        ovar->sqldata = (char *)malloc(sizeof(long));
     /* Process remaining types. */
        . . .
}
ovar->sqltype & 1) {
  /* Assign a variable to hold NULL status. */
  ovar->sqlind = (short *)malloc(sizeof(short));
 }
} /* end of for loop */

/* Fetch and process the rows in the select list one by one. */
while ((fetch_stat = isc_dsql_fetch(status_vector, &stmt_handle,
         1, osqlda)) == 0) {
  for (i=0; i < osqlda->sqld; i++) {
  /* Call a function you’ve written to process each returned
  select-list item. */
  process_column(osqlda->sqlvar[i]);
 }
}

Return value

isc_dsql_execute() returns the second element of the status vector. Zero indicates success. A 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, isc_bad_trans_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_execute2()