isc_dsql_batch_execute()

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:

Syntax

 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
status_vector	The address of an array of type ICS_STATUS. The respective ISC_STATUS values for each SQL statement will be returned in this array.
tr_handle	The address of the transaction handle.
stmt_handle	Statement handle previously prepared by isc_dsql_prepare().
dialect	The SQL dialect to use for the statements in the batch update.
number_of_rows	The number of XSQLVARS for this batch update.
insqlda	The address of an XSQLDA data structure describing the input parameters.
batch_vars	An array of pointers to XSQLVAR structures that describe each input parameter.
rows_affected	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.

Description

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
num_rows	The number of rows to be batched.
num_vars_per_row	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.

Examples

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;
XSQLDA ISC_FAR *sqlda;
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)
exit(-1);

// 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);

free(sqlda);

See Also

• Isc_dsql_batch_execute_immed

Advance To:

• Isc_dsql_batch_execute_immed