C and C++ host variable arrays

In the following example, two host variable arrays are declared; empno and lastname. Each can hold up to 100 elements. Because there is only one FETCH INTO statement, this example retrieves 100 rows, or less.

// Declaring host variables 
EXEC SQL BEGIN DECLARE SECTION;
    char   empno[100][8];
    char   lastname[100][15];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE empcr CURSOR FOR
	SELECT empno, lastname FROM employee;

EXEC SQL OPEN empcr;

EXEC SQL WHENEVER NOT FOUND GOTO end_fetch;

while (1) {
	EXEC SQL FETCH empcr INTO :empno :lastname;  /* bulk fetch       */
	...                                          /* 100 or less rows */
	...
}
end_fetch:
EXEC SQL CLOSE empcr;

In the following example, host variable arrays arr_in1 and arr_in2 are used for an INSERT statement:

// Declaring host variables.
EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 arr_in1[3];
  char arr_in2[3][11];
EXEC SQL END DECLARE SECTION;

...
// Populating the arrays.
for ( i = 0; i < 3; i++)
{
    arr_in1[i] = 100 + i;
    sprintf(arr_in2[i], "hello%d", arr_in1[i]);
}

// The ‘arr_in1’ & ‘arr_in2’ are host variable arrays.
EXEC SQL INSERT into tbl1 values (:arr_in1, :arr_in2);
printf(“sqlca.sqlcode = %d\n”, sqlca.sqlcode ); // 0

// The INSERT operation inserted 3 rows without encounting an error.
printf(“sqlca.sqlerrd[3] = %d\n”, sqlca.sqlerrd[3] ); // 3

// The INSERT operation was successful and 3 rows has been stored in database.
printf(“sqlca.sqlerrd[2] = %d\n”, sqlca.sqlerrd[2] ); // 3

// The tb11 table now contains the following rows:
//C1          C2
//----------- -----------
//        100 hello1    
//        101 hello2
//        102 hello3

In the following example, host variable arrays arr_in1 and arr_in2 are used for an UPDATE statement:

// Declaring host variables 
EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 arr_in1[3];
  sqlint32 arr_in2[2];
EXEC SQL END DECLARE SECTION;

...
// Populating the arrays.
for ( i = 0; i < 3; i++)
{
   arr_in1[i] = 100 + i;
}

arr_in2[0] = 1000;
arr_in2[1] = 1001;

// Table tbl2 consists of following rows before an update statement is issued.
//C1          C2
//----------- -----------
//        100        500
//        101        501
//        102        502

// The ‘arr_in1’ array is declared with cardinality of 3 for use in the 
// SET clause of an UPDATE statement.
// The 'arr_in2' array is declared with cardinality of 2 for use in the 
// WHERE clause of an UPDATE statement.
// The tbl2 table contains 3 rows. 
// The following UPDATE statement will affect only 2 rows as per arr_in2 
// for column c2 and remaining need to be untouched.

// The ‘arr_in1’ array in the following update statement is treated as
// having cardinality of 2.
EXEC SQL UPDATE tbl2 SET c2 = :arr_in2 + c2 where c1 = :arr_in1;
printf(“sqlca.sqlcode = %d\n”, sqlca.sqlcode ); // 0

// As there is no error in update statement, sqlca.sqlerrd[3] 
// contains rows which are updated successfully.
printf(“sqlca.sqlerrd[3] = %d\n”, sqlca.sqlerrd[3] ); // 2

// update successful and 2 rows has been updated in database.
printf(“sqlca.sqlerrd[2] = %d\n”, sqlca.sqlerrd[2] ); // 2

// The tb12 table now contains the following rows:
//C1          C2
//----------- -----------
//        100        1500
//        101        1502
//        102         503

In the following example, host variable arrays arr_in1 and arr_in2 are used for a DELETE statement:

// Declaring host variables 
EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 arr_in1[3];
EXEC SQL END DECLARE SECTION;
...
// Populating the arrays.
for ( i = 0; i < 3; i++)
{
   arr_in1[i] = 101 + i;
}

// Initial data in the tbl2 table:
// C1          C2
// ----------- -----------
//         100         500
//         101         501
//         102         502
//         103         503
//         104         504 
// using array host while executing delete statement in where clause
// The‘arr_in1’ host variable array is used in the WHERE clause of 
// an DELETE statement.

EXEC SQL DELETE FROM tbl2 where c1 = :arr_in1;
printf(“sqlca.sqlcode = %d\n”, sqlca.sqlcode ); // 0

// delete successful attempted rows are 3
printf(“sqlca.sqlerrd[3] = %d\n”, sqlca.sqlerrd[3] ); // 3

// delete successful and 3 rows has been deleted in database.
printf(“sqlca.sqlerrd[2] = %d\n”, sqlca.sqlerrd[2] ); // 3

// The tb12 table now contains the following rows:
// C1          C2
// ----------- -----------
//         100         500
//         104         504

The use of a C or C++ host variable array in embedded SQL applications is subject to the following restrictions:

• Host variables arrays are supported by C or C++ embedded SQL applications that connect to Db2® servers.
• Host variable arrays must be declared in the DECLARE SECTION with exact size of the array elements (cardinality).
• Specific array element cannot be specified in a SQL statement.
• The INSERT, UPDATE, or DELETE operation with host variable arrays is run as an atomic operation on the database server. If any array element causes an SQL_ERROR, current transaction is rolled back.
• Use of host variable arrays are not supported by dynamically prepared INSERT, UPDATE, or DELETE statements.
• Maximum size of array element (cardinality) is 32672.
• The following C and C++ data types are not supported for use with host variable arrays:
  • Another host variable array (nesting)
  • BLOB
  • BLOB file reference
  • BLOB locator variable
  • CLOB
  • CLOB file reference
  • CLOB locator variable
  • User-defined data type
  • XML
• FOR N ROWS clause can be used to specify the cardinality for INSERT and MERGE statement, where N can be an integer or a host variable of type int or short. If array host variables are used, it will take the minimum cardinality value among all the host variables that are used in the SQL.
• Host variable array support is not provided for Db2 for z/OS® and Db2 for i servers.