10. Understanding ODBC Connectivity : ODBC Programming : Database Procedure Execution : Explicit Batch Execution
 
Share this page                  
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.