Binding parameter markers in CLI applications with column-wise array input

To process an SQL statement that will be repeated with different values, you can use column-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. Column-wise array input allows arrays of storage locations to be bound to parameter markers. A different array is bound to each parameter.

Before you begin

Before binding parameter markers with column-wise binding, ensure that you have initialized your CLI application.

About this task

For character and binary input data, the application uses the maximum input buffer size argument (BufferLength) of the SQLBindParameter() call to indicate to CLI the location of values in the input array. For other input data types, the length of each element in the array is assumed to be the size of the C data type.

Procedure

To bind parameter markers using column-wise array input:

  1. Specify the size of the arrays (the number rows to be inserted) by calling SQLSetStmtAttr() with the SQL_ATTR_PARAMSET_SIZE statement attribute.
    Note: The maximum value allowed for the SQL_ATTR_PARAMSET_SIZE attribute is 32767.
  2. Initialize and populate an array for each parameter marker to be bound.
    Note: Each array must contain at least SQL_ATTR_PARAMSET_SIZE elements, otherwise, memory access violations may occur.
  3. Optional: Indicate that column-wise binding is to be used by setting the SQL_ATTR_PARAM_BIND_TYPE statement attribute to SQL_BIND_BY_COLUMN (this is the default setting).
  4. Bind each parameter marker to its corresponding array of input values by calling SQLBindParameter() for each parameter marker.