Long data for bulk inserts and updates in CLI applications

You can process long data for bulk inserts and updates by using the SQLBulkOperations() function.
  1. When an application binds the data using SQLBindCol(), the application places an application-defined value, such as the column number, in the *TargetValuePtr buffer for data-at-execution columns. The value can be used later to identify the column.

    The application places the result of the SQL_LEN_DATA_AT_EXEC(length) macro in the *StrLen_or_IndPtr buffer. If the SQL data type of the column is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, data source-specific data type and CLI returns "Y" for the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo(), length is the number of bytes of data to be sent for the parameter; otherwise, it must be a non-negative value and is ignored.

  2. When SQLBulkOperations() is called, if there are data-at-execution columns, the function returns SQL_NEED_DATA and proceeds to the next event in the sequence, described in the next item. (If there are no data-at-execution columns, the process is complete.)
  3. The application calls SQLParamData() to retrieve the address of the *TargetValuePtr buffer for the first data-at-execution column to be processed. SQLParamData() returns SQL_NEED_DATA. The application retrieves the application-defined value from the *TargetValuePtr buffer.
    Note: Although data-at-execution parameters are similar to data-at-execution columns, the value returned by SQLParamData() is different for each.

    Data-at-execution columns are columns in a rowset for which data will be sent with SQLPutData() when a row is updated or inserted with SQLBulkOperations(). They are bound with SQLBindCol(). The value returned by SQLParamData() is the address of the row in the *TargetValuePtr buffer that is being processed.

  4. The application calls SQLPutData() one or more times to send data for the column. More than one call is needed if all the data value cannot be returned in the *TargetValuePtr buffer specified in SQLPutData(); note that multiple calls to SQLPutData() for the same column are allowed only when sending character C data to a column with a character, binary, or data source-specific data type or when sending binary C data to a column with a character, binary, or data source-specific data type.
  5. The application calls SQLParamData() again to signal that all data has been sent for the column.
    • If there are more data-at-execution columns, SQLParamData() returns SQL_NEED_DATA and the address of the TargetValuePtr buffer for the next data-at-execution column to be processed. The application repeats steps 4 and 5 as long as SQLParamData() returns SQL_NEED_DATA.
    • If there are no more data-at-execution columns, the process is complete. If the statement was executed successfully, SQLParamData() returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, SQLParamData() can return any SQLSTATE that can be returned by SQLBulkOperations().

If the operation is canceled, or an error occurs in SQLParamData() or SQLPutData(), after SQLBulkOperations() returns SQL_NEED_DATA, and before data is sent for all data-at-execution columns, the application can call only SQLCancel(), SQLGetDiagField(), SQLGetDiagRec(), SQLGetFunctions(), SQLParamData(), or SQLPutData() for the statement or the connection associated with the statement. If it calls any other function for the statement or the connection associated with the statement, the function returns SQL_ERROR and SQLSTATE HY010 (Function sequence error).

On Db2® for z/OS®, calls to the SQLEndTran() function specifying SQL_ROLLBACK as completion type are allowed when the SQL_ATTR_FORCE_ROLLBACK connection attribute is set, the StreamPutData configuration keyword is set to 1, and autocommit mode is enabled.

If the application calls SQLCancel() while CLI still needs data for data-at-execution columns, CLI cancels the operation. The application can then call SQLBulkOperations() again; canceling does not affect the cursor state or the current cursor position.