To process an SQL statement that
will be repeated with different values, you can use row-wise array
input to achieve bulk inserts, deletes, or updates.
This
results in fewer network flows to the server because SQLExecute() does
not have to be called repeatedly on the same SQL statement for each
value. Row-wise array input allows an array of structures to be bound
to parameters.
Before you begin
Before binding parameter markers with row-wise binding, ensure
that you have initialized your CLI application.
Procedure
To bind parameter markers using row-wise array input:
- Initialize and populate an array of structures that contains
two elements for each parameter: the first element contains the length/indicator
buffer, and the second element holds the value itself. The size of
the array corresponds to the number of values to be applied to each
parameter. For example, the following array contains the length and
value for three parameters:
struct { SQLINTEGER La; SQLINTEGER A; /* Information for parameter A */
SQLINTEGER Lb; SQLCHAR B[4]; /* Information for parameter B */
SQLINTEGER Lc; SQLCHAR C[11]; /* Information for parameter C */
} R[n];
- Indicate that row-wise binding is to by used by setting
the SQL_ATTR_PARAM_BIND_TYPE statement attribute to the length of
the struct created in the previous step, using
SQLSetStmtAttr().
- Set the statement attribute SQL_ATTR_PARAMSET_SIZE to the number of rows of the array,
using
SQLSetStmtAttr().
Note: The maximum value allowed for the SQL_ATTR_PARAMSET_SIZE attribute is 32767.
- Bind each parameter to the first row of the array created
in step 1 using
SQLBindParameter().
For example, /* Parameter A */
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 5, 0, &R[0].A, 0, &R.La);
/* Parameter B */
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
3, 0, R[0].B, 3, &R.Lb);
/* Parameter C */
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
10, 0, R[0].C, 10, &R.Lc);