Using arrays to pass parameter values
Db2 ODBC provides an array input method for updating Db2 tables.
In data entry and update applications, users might often insert,
delete, or alter many cells in a data entry form before they send
these changes to the database. For these situations, Db2 ODBC provides an array input method that
eliminates the need for you to call SQLExecute()
repeatedly
on the same INSERT, DELETE, UPDATE, or MERGE statement. In addition,
the use of arrays to pass parameter values can reduce network flows.
- Call
SQLBindParameter()
for each parameter marker that you bind to an input array in memory. Use the following argument values in this function call:- Set the fParamType argument value to SQL_PARAM_INPUT.
- Point the rgbValue argument to the array that contains input data for the parameter marker.
- For character data and binary input data, specify the length, in bytes, of each element in the input array with the input argument cbValueMax. (For other input data types, this argument is ignored.)
- Optionally, point the pcbValue argument to an array that contains the lengths, in bytes, of each value in the input array. Specify each length value in the pcbValue array to be the length of the corresponding value in the rgbValue array.
SQLBindFileToParam()
. - Call
SQLSetStmtAttr()
and specify, in the crow argument, the number of rows that the input array contains. This value indicates the number of different values for each parameter. - Call
SQLExecute()
to send all the parameter values to the database.
When you insert, update, or merge rows with arrays, use SQLRowCount()
to
verify the number of rows you changed.
Queries with parameter markers that are bound to arrays on the
WHERE clause generate multiple sequential result sets. You process
each result set that such a query returns individually. After you
process the initial result set, call SQLMoreResults()
to
retrieve each additional result set.
UPDATE EMPLOYEE SET OVERTIME_WORKED= ? and OVERTIME_PAID= ?
WHERE EMPLOY_ID=?
Because
this statement contains three parameter markers, the application uses
three arrays to store input data. When the user makes changes to n rows,
the application places n values in each array. When the user
decides to send these changes to the database, the application binds
the parameter markers in the prepared SQL statement to the arrays.
The application then calls SQLSetStmtAttr()
with
the crow argument set to n. This
value specifies the number of elements in each array.
The following figure shows the two methods of executing a statement
with m parameters n times. Both methods must call SQLBindParameter()
once
for each parameter.
The left side of the preceding figure illustrates a method of bulk
operations that does not use arrays to pass parameter values. SQLBindParameter()
binds
each parameter marker to a host variable that contains a single value.
Because this method does not perform array inserts, SQLExecute()
is
called repeatedly. Before each SQLExecute()
call,
the application updates the variables that are bound to the input
parameters. This method calls SQLExecute()
to execute
every operation.
For the method that uses arrays,
SQLExecute()
is called only one time for any number of bulk operations.
The array method calls SQLSetStmtAttr()
with the statement attribute
SQL_ATTR_PARAMSET_SIZE
, and then calls SQLExecute()
.
/* ... */
SQLUINTEGER pirow = 0;
SQLCHAR stmt[] =
"INSERT INTO CUSTOMER ( Cust_Num, First_Name, Last_Name) "
"VALUES (?, ?, ?)";
SQLINTEGER Cust_Num[25] = {
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[25][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[25][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);
rc = SQLParamOptions(hstmt, 25, &pirow);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
0, 0, Cust_Num, 0, NULL);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
31, 0, First_Name, 31, NULL);
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
31, 0, Last_Name, 31, NULL);
rc = SQLExecute(hstmt);
printf("Inserted
/* ... */
- If the EMPNO value for the row that is to be merged matches an EMPNO value in the DSN8C10.EMP table, the SALARY value for the existing table row is updated.
- If the EMPNO value for the row that is to be merged does not match an EMPNO value in the DSN8C10.EMP table, the row is inserted into the DSN8C10.EMP table.
MERGE INTO DSN8C10.EMP AS t
USING VALUES
(CAST (? AS CHAR(6)),
CAST (? AS VARCHAR(12)),
CAST (? AS CHAR(1)),
CAST (? AS VARCHAR(15)),
CAST (? AS INTEGER))
AS s (EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY)
ON t.EMPNO = s.EMPNO
WHEN MATCHED THEN UPDATE SET SALARY = s.SALARY
WHEN NOT MATCHED THEN INSERT
(EMPNO, s.FIRSTNME, s.MIDINIT, s.LASTNAME, s.SALARY)
NOT ATOMIC CONTINUE ON SQLEXCEPTION;
Because
this statement contains five parameter markers, the application uses
five arrays to store input data. When you make changes to n rows,
the application places n values in each array. When you decide
to send these changes to the database, the application binds the parameter
markers in the prepared SQL statement to the arrays. The application
then calls SQLSetStmtAttr()
with the crow argument
set to n. This value specifies the number of elements in each
array.
The following figure shows the two methods of executing a MERGE statement with m+p parameters. m is the number of parameters that have one or more values for each parameter. p is the number of parameters in the UPDATE and INSERT parts of the MERGE statement, which have a single value for each parameter.
The left side of the preceding figure illustrates a method of merging you can use when the MERGE statement needs to be executed more than once. The statement is prepared and executed in two separate steps so that the prepared statement can be used if Db2 is set up for prepared statement caching.
The right side of Figure 2 illustrates a method of bulk operations that you can use when the MERGE statement needs to be executed only once. The statement is prepared and executed in a single step.
/* declare and initialize local variables */
SQLUINTEGER cRow = 10;
SQLUINTEGER
piRow = 0;
SQLCHAR sqlStmt[] =
"MERGE INTO DSN8C10.EMP AS t"
" USING VALUES"
" (CAST (? AS CHAR(6)),"
" CAST (? AS VARCHAR(12)),"
" CAST (? AS CHAR(1)),"
" CAST (? AS VARCHAR(15)),"
" CAST (? AS INTEGER))"
" AS s (EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY)"
" ON t.EMPNO = s.EMPNO"
" WHEN MATCHED THEN UPDATE SET SALARY = s.SALARY"
" WHEN NOT MATCHED THEN INSERT"
" (EMPNO, s.FIRSTNME, s.MIDINIT, s.LASTNAME, s.SALARY)"
" NOT ATOMIC CONTINUE ON SQLEXCEPTION";
SQLCHAR empno[10][7];
SQLCHAR firstname[10][13];
SQLCHAR middlename[10][2];
SQLCHAR lastname[10][16];
SQLINTEGER salary[10];
/* set up data for empno, firstname, middlename, lastname and salary */
/* ... */
/* prepare the statement */
rc = SQLPrepare(hstmt, sqlStmt, SQL_NTS);
/* specify the number of rows to be merged */
rc = SQLParamOptions(hstmt, cRow, &piRow);
/* bind the parameters to input arrays */
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
6, 0, empno, 7, NULL);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
12, 0, firstname, 13, NULL);
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
1, 0, middlename, 2, NULL);
rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
15, 0, lastname, 16, NULL);
rc = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
0, 0, salary, 0, NULL);
/* execute the statement */
rc = SQLExecute(hstmt);
/* display the total number of rows either updated or inserted by MERGE */
printf("MERGEd