For some data entry and update applications (especially graphical), users may often insert, delete, or change many cells in a data entry form and then ask for the data to be sent to the database. For these situations of bulk insert, delete, or update, DB2 CLI provides an array input method to save the application from having to call SQLExecute() repeatedly on the same INSERT, DELETE, or UPDATE statement. In addition, there is significant savings in network flows.
There are two ways an application can bind the parameter markers in an SQL statement to arrays:
SQLBindParameter() is still used to bind buffers to parameters, the only difference is that the addresses passed are array addresses, not single-variable addresses. The application must also set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to specify whether column-wise or row-wise binding will be used.
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.
The first step, when using row-wise array insert, is to create a structure that contains two elements for each parameter. The first element for each parameter holds the length/indicator buffer, and the second element holds the value itself. Once the structure is defined the application must allocate an array of these structures. The number of rows in the array corresponds to the number of values that will be used for each parameter.
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];
Figure 11 shows the structure R with three parameters, in an array of n rows. The array can then be populated with the appropriate data.
Once the array is created and populated the application must indicate that row-wise binding is going to be used. It does this by setting the statement attribute SQL_ATTR_PARAM_BIND_TYPE to the length of the structure created. The statement attribute SQL_ATTR_PARAMSET_SIZE must also be set to the number of rows in the array.
Each parameter can now be bound to the appropriate two elements of the structure (in the first row of the array) using SQLBindParameter().
/* 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,
10, 0, R[0].B, 10, &R.Lb);
/* Parameter C */
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
3, 0, R[0].C, 3, &R.Lc);
At this point the application can call SQLExecute() once and all of the updates are sent to the database.
Figure 11. Row-Wise Array Insert
 |
See Retrieving Diagnostic Information for information on errors that can be accessed by the application.
A parameter status array can be populated after the SQLExecute() or SQLExecDirect() call. The array contains information about the processing of each set of parameters. See the statement attribute SQL_ATTR_PARAM_STATUS_PTR, or the corresponding IPD descriptor header field SQL_DESC_ARRAY_STATUS_PTR, for complete details.
The statement attribute SQL_ATTR_PARAMS_PROCESSED, or the corresponding IPD descriptor header field SQL_DESC_ROWS_PROCESSED_PTR, can be used to return the number of sets of parameters that have been processed. See these attributes in the description of SQLSetStmtAttr() or SQLSetDescField().
Once the application has determined what parameters had errors, it can use the statement attribute SQL_ATTR_PARAM_OPERATION_PTR, or the corresponding APD descriptor header field SQL_DESC_ARRAY_STATUS_PTR, (both of which point to an array of values) to control which sets of parameters are ignored in a second call to SQLExecute() or SQLExecDirect(). See these attributes in the description of SQLSetStmtAttr() or SQLSetDescField().
Other Information
In environments where the underlying support allows Compound SQL (DB2 Universal Database, or DRDA environments with DB2 Connect V 2.3 or higher), there is additional savings in network flow. All the data in the array(s) together with the execute request are packaged together as one flow. For DRDA environments, the underlying Compound SQL support is always NOT ATOMIC COMPOUND SQL. This means that execution will continue even if an error is detected with one of the intermediate array elements. When SQLRowCount() is called after an array operation, the row count received is the aggregate number of rows affects by all the elements in the input parameter value array.
When connected to DB2 Universal Database, the application has the option of ATOMIC or NOT ATOMIC COMPOUND SQL. With ATOMIC SQL (the default) either all the elements of the array are processed successfully, or none at all. The application can choose to select the type of COMPOUND SQL used by setting the SQL_ATTR_PARAMOPT_ATOMIC attribute with SQLSetStmtAttr().
| Note: | SQLBindParam() must not be used to bind an array storage location to a parameter marker. In the case of character or binary input data, there is no method to specify the size of each element in the input array. |
For queries with parameter markers on the WHERE clauses, an array of input values will cause multiple sequential result sets to be generated. Each result set can be processed before moving onto the next one by calling SQLMoreResults(). See SQLMoreResults - Determine If There Are More Result Sets for more information and an example.
When an application needs to change parameter bindings it can call SQLBindParameter() a second time. This will change the bound parameter buffer address and the corresponding length/indicator buffer address used. This can only be used with row wise array inserts, but will work whether the application binds parameters individually or using an array.
Instead of multiple calls to SQLBindParameter(), DB2 CLI also supports parameter binding offsets. Rather than re-binding each time, an offset can be used to specify new buffer and length/indicator addresses which will be used in a subsequent call to SQLExecute() or SQLExecDirect().
To make use of parameter binding offsets, an application would follow these steps:
The statement attribute SQL_ATTR_PARAM_BIND_OFFSET_PTR points to the address of an SQLINTEGER buffer where the offset will be stored. This address must remain valid until the cursor is closed.
This extra level of indirection enables the use of a single memory variable to store the offset for multiple sets of parameter buffers on different statement handles. The application need only set this one memory variable and all of the offsets will be changed.
The offset value is always added to the memory location of the originally bound values. This sum must point to a valid memory address.
See the section Parameter Binding Offsets in SQLBindParam() for more information.
This example shows an array INSERT statement, for an example of an array query statement, refer to SQLMoreResults - Determine If There Are More Result Sets.
/* ... */
SQLCHAR * stmt =
"INSERT INTO CUSTOMER ( Cust_Num, First_Name, Last_Name ) "
"VALUES (?, ?, ?)" ;
SQLINTEGER Cust_Num[] = {
10, 20, 30, 40, 50, 60, 70, 80, 90, 100,
110, 120, 130, 140, 150, 160, 170, 180, 190, 200,
210, 220, 230, 240, 250,
} ;
SQLCHAR First_Name[][31] = {
"EVA", "EILEEN", "THEODORE", "VINCENZO", "SEAN",
"DOLORES", "HEATHER", "BRUCE", "ELIZABETH", "MASATOSHI",
"MARILYN", "JAMES", "DAVID", "WILLIAM", "JENNIFER",
"JAMES", "SALVATORE", "DANIEL", "SYBIL", "MARIA",
"ETHEL", "JOHN", "PHILIP", "MAUDE", "BILL",
} ;
SQLCHAR Last_Name[][31] = {
"SPENSER", "LUCCHESI", "O'CONNELL", "QUINTANA", "NICHOLLS",
"ADAMSON", "PIANKA", "YOSHIMURA", "SCOUTTEN", "WALKER",
"BROWN", "JONES", "LUTZ", "JEFFERSON", "MARINO",
"SMITH", "JOHNSON", "PEREZ", "SCHNEIDER", "PARKER",
"SMITH", "SETRIGHT", "MEHTA", "LEE", "GOUNOT",
} ;
/* ... */
/* Prepare the statement */
rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_PARAMSET_SIZE,
( SQLPOINTER ) row_array_size,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_SLONG,
SQL_INTEGER,
0,
0,
Cust_Num,
0,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
2,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
31,
0,
First_Name,
31,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
3,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
31,
0,
Last_Name,
31,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecute( hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "Inserted %ld Rows\n", row_array_size ) ;