From InterBase

Go Up to API Function Reference

The isc_dsql_batch_execute function supports batch updates for prepared SQL statements. This function allows you to batch the data used in a parameterized SQL statement.

Note: A related function, isc_dsql_batch_execute_immed, allows you to execute a group of specific commands. For API information about this related function, see isc_dsql_batch_execute_immed. For an introduction to batch updates, see “Using Batch Updates to Submit Multiple Statements” in the InterBase Operations Guide.

The signature of the isc_dsql_batch_execute function is:


 ISC_STATUS isc_dsql_batch_execute(ISC_STATUS *status_vector,
 isc_tr_handle *tr_handle,
 isc_stmt_handle stmt_handle, ISC_USHORT number_of_rows,
 XSQLDA *insqlda, XSQLVAR[] *batch_vars, *rows_affected);

The following table shows the meaning of each argument.

isc_dsql_batch_execute Parameters
Argument Description


The address of an array of type ICS_STATUS.

The respective ISC_STATUS values for each SQL statement will be returned in this array.


The address of the transaction handle.


Statement handle previously prepared by isc_dsql_prepare().


The SQL dialect to use for the statements in the batch update.


The number of XSQLVARS for this batch update.


The address of an XSQLDA data structure describing the input parameters.


An array of pointers to XSQLVAR structures that describe each input parameter.


A pre-allocated array of type ISC_ULONG, which will be used to store the number of rows affected by the corresponding SQL statement in the sql array.


If a statement fails, the ISC_STATUS will be set, and the corresponding entry in the rows_affected array will be set to -1, or 0xFFFFFFFF.

To facilitate the allocation of the XSQLVAR array, a macro is provided to calculate the size of the array. The new macro, XSQLVAR_LENGTH is defined as follows:

XSQLVAR_LENGTH(num_rows, num_vars_per_row)

The following table explains the meaning of each macro parameter.

XSQLVAR_LENTGH Macro Parameters
Argument Description


The number of rows to be batched.


The number of input parameters to be set per row.

The following examples show how to use the isc_dsql_batch_execute API to execute a parameterized INSERT statement. Whereas the isc_dsql_batch_exec_immed function sends a group of SQL statements, the isc_dsql_batch_execute function sends one parameterized statement with a group of values to use for the parameters.

The first example demonstrates the declaration of variables in preparation for calling the isc_dsql_batch_execute function. The code will call the function using the two dept_no variables declared here.


Example 1

Declaring Variables for the isc_dsql_batch_execute function:

#define NUM_ROWS 2
#define NUM_VARS 2

//An UPDATE statement with 2 parameters..
char *sql1 = “UPDATE department SET budget = ? * budget + budget WHERE dept_no = ?”;

short flag0 = 0, flag1 = 0;
char dept_no[4] = “117”, dept_no1[4] = “119”;
isc_stmt_handle stmt_handle = NULL;
double percent_inc = (double)0.0;
int i;
XSQLVAR *array_sqlvar;
ISC_ULONG rows_affected[NUM_ROWS];

The code in the following example starts a transaction and prepares the parameterized UPDATE statement.

Example 2

Preparing the UPDATE statement

if(isc_start_transaction(status, &trans, 1, &DB, 0, NULL))
ERREXIT(status, 1);

if(isc_dsql_allocate_statement(status, &DB, &stmt_handle))
ERREXIT(status, 1);

sqlda = (XSQLDA ISC_FAR *)malloc(XSQLDA_LENGTH(2));
sqlda->sqln = 2;
sqlda->sqld = 2;
sqlda->version = SQLDA_CURRENT_VERSION;

if(isc_dsql_prepare(status, &trans, &stmt_handle, strlen(sql1), sql1, 3, NULL))
ERREXIT(status, 1);

array_sqlvar = (XSQLVAR *)malloc(XSQLVAR_LENGTH(sqlda->sqld, NUM_ROWS));
if(array_sqlvar == NULL)

// Prepare data for the parameters. The UPDATE statement takes two parameters.
// This example batches two different values for the dept_no parameter.
array_sqlvar[0].sqldata = (char ISC_FAR *)&percent_inc;
array_sqlvar[0].sqltype = SQL_DOUBLE + 1;
array_sqlvar[0].sqllen = sizeof(percent_inc);
array_sqlvar[0].sqlind = &flag0;
flag0 = 0;

array_sqlvar[1].sqldata = dept_no;
array_sqlvar[1].sqltype = SQL_TEXT + 1;
array_sqlvar[1].sqllen = 3;
array_sqlvar[1].sqlind = &flag1;
flag1 = 0;

array_sqlvar[2].sqldata = (char ISC_FAR *)&percent_inc;
array_sqlvar[2].sqltype = SQL_DOUBLE + 1;
array_sqlvar[2].sqllen = sizeof(percent_inc);
array_sqlvar[2].sqlind = &flag0;
flag0 = 0;

array_sqlvar[3].sqldata = dept_no1;
array_sqlvar[3].sqltype = SQL_TEXT + 1;
array_sqlvar[3].sqllen = 3;
array_sqlvar[3].sqlind = &flag1;
flag1 = 0;

Example 3

The next example calls the isc_dsql_batch_execute function and the results are printed.

if(isc_dsql_batch_execute(status, &trans, &stmt_handle, 3, sqlda, NUM_ROWS, array_sqlvar, rows_affected))
ERREXIT(status, 1);

for(i = 0; i < NUM_ROWS; i++)
printf(“After batch return values %d”, rows_affected[i]);

free((void *)array_sqlvar);

if(isc_commit_transaction(status, &trans))
ERREXIT(status, 1);

if(isc_detach_database(status, &DB))
ERREXIT(status, 1);


See Also

Advance To: