Explicit Batch Execution
Explicit batch execution can be considered an alternative to database procedures. In explicit batch execution, a set of CREATE, DELETE, UPDATE, or EXECUTE PROCEDURE queries are chained together in a single SQLExecDirect() statement:
[ Allocate handles and connect. ]
SQLExecDirect(hstmt, "insert into cars( model ) values( 'Hummer' ); " \
"update cars set model = 'Altima' where model = 'Mustang' ); " \
"insert into cars( model ) values( 'Camray' ", SQL_NTS;
Explicit batch execution supports dynamic parameters. The parameters are bound with the column number 1 to N as if the batch were a single statement:
SQLCHAR model[3][21] = { "Hummer", "Mustang ", "Camray " };
int i;
SQLINTEGER orind = SQL_NTS;
[ Allocate handles and connect. ]
SQLExecDirect(hstmt, "insert into cars( model ) values( ? ); " \
"update cars set model = 'Altima' where model = ?; " \
"delete from cars where model = ? ", SQL_NTS;
/*
** Note that the column number increments from 1 to 3.
*/
for ( i = 0; i < 3; i++ )
{
SQLBindParameter( hstmt, /* Statement handle */
i+1, /* Column number 1 */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* This is a string in C */
SQL_VARCHAR, /* Destination column is varchar */
strlen( model[i] ), /* Length of the parameter */
0, /* No scale specifier */
model[i], /* The data itself */
0, /* Maximum length (default 0) */
&orind ); /* Null-terminated string */
}
Limitation of Explicit Batch Queries
Since Ingres and Vector do not support multiple result sets, select queries are not allowed in explicit batches. Likewise, row-returning database procedures are not supported. Database procedures that include BYREF or output parameters are not supported in explicit batch.
Batch Execution using Parameter Arrays
A single statement that uses dynamic parameters can be effectively executed multiple times using parameter arrays. An ODBC application can bind rows of data or columns of data, called row-wise and column-wise binding, respectively. The following example uses column-wise binding:
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)nbrInserts, 0);
SQLExecDirect(hstmt, "insert into cars( model ) values( ? ); ", SQL_NTS;
/*
** Note that the column number is numbered 1 through n.
*/
for ( i = 0; i < 3; i++ )
{
SQLBindParameter( hstmt, /* Statement handle */
1, /* Column number 1 */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* This is a string in C */
SQL_VARCHAR, /* Destination column is varchar */
strlen( model[i] ), /* Length of the parameter */
0, /* No precision specifier */
model[i], /* The data itself */
0, /* Maximum length (default 0) */
&orind ); /* Null-terminated string */
}
We strongly recommend the use of parameter arrays, which can result in dramatically improved performance if the database server is Ingres 10.0 or later or Vector 2.0 or later.