This method involves the binding of parameter marker(s) to array(s) of storage locations via the SQLBindParameter() call. For character and binary input data, the application uses the maximum input buffer size argument (BufferLength) on SQLBindParameter() call to indicate to DB2 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. The statement attribute SQL_ATTR_PARAMSET_SIZE must be set (with a call to SQLSetStmtAttr()) to the size of the array before the execution of the SQL statement.
Suppose for Figure 10 there is an application that allows the user to change values in the OVERTIME_WORKED and OVERTIME_PAID columns of a time sheet data entry form. Also suppose that the primary key of the underlying EMPLOYEE table is EMPLOY_ID. The application can then request to prepare the following SQL statement:
UPDATE EMPLOYEE SET OVERTIME_WORKED= ? and OVERTIME_PAID= ? WHERE EMPLOY_ID=?
When the user has entered all the changes, the application counts that n rows are to change and allocates m=3 arrays to store the changed data and the primary key. Then it calls SQLBindParameter() to bind the three parameter markers to the location of three arrays in memory. Next it sets the statement attribute SQL_ATTR_PARAMSET_SIZE (with a call to SQLSetStmtAttr()) to specify the number of rows to change (the size of the array). Then it calls SQLExecute() once and all the updates are sent to the database. This is the flow shown on the right side of Figure 10.
The basic method is shown on the left side of Figure 10 where SQLBindParameter() is called to bind the three parameter markers to the location of three variables in memory. SQLExecute() is called to send the first set of changes to the database. The variables are updated to reflect values for the next row of changes and again SQLExecute() is called. Note that this method has n-1 extra SQLExecute() calls.
Figure 10. Column-Wise Array Insert
|
See "Retrieving Diagnostic Information" for information on errors that can be accessed by the application.