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.

You pass arrays to parameter markers with the following method:
  1. 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.
    For LOB data in files, you can use SQLBindFileToParam().
  2. 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.
  3. 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.

INSERT example: Consider an application that performs an array insert, as the right side of Figure 1 illustrates. Suppose that this application enables users 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. This application can then request to prepare the following SQL statement: 
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.

Figure 1. Array insert
Begin figure summary. This figure is a flowchart that depicts the functions that you call to pass multiple values to parameter markers. Detailed description available.

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().

The following example shows an array INSERT statement.
/* ... */
    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 
/* ... */
MERGE example: Consider an application that performs an array merge, as Figure 2 illustrates. This application merges an array of EMPNO, FIRSTNME, MIDINIT, LASTNAME, and SALARY values into the DSN8C10.EMP sample table. For each row of values that is to be merged:
  • 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.
The MERGE statement that accomplishes this is: 
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.

Figure 2. Array merge
Begin figure summary. This figure is a flowchart that shows function calls for executing a MERGE statement with multiple values for some parameter markers. Detailed description available.

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.

The following figure shows code for an array MERGE.
Figure 3. An application that performs an array merge
/* 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