DB2 Version 10.1 for Linux, UNIX, and Windows

C and C++ host variable arrays

You can use C and C++ host variable arrays for FETCH INTO statements that are non-dynamic, when you set the precompiler option COMPATIBILITY_MODE to ORA.

You can declare a cursor and do a bulk fetch into a variable array until the end of the row is reached. Host variable arrays that are used in the same FETCH INTO statement must have same cardinality. Otherwise, the smallest declared cardinality is used for the array.

In one FETCH INTO statement, the maximum number of records that can be retrieved is the cardinality of the array that is declared. If more rows are available after the first fetch, you can repeat the FETCH INTO statement to obtain the next set of rows.

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;

The total number of rows that are successfully fetched is stored in the sqlca.sqlerrd[2] field.

Restrictions with C or C++ host variable array support

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® for Linux, UNIX, and Windows 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.
  • 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