Host-variable arrays

You can use host-variable arrays to pass a data array between Db2 and your application. A host-variable array is a data array that is declared in the host language to be used within an SQL statement.

You can use host-variable arrays for the following actions:

  • Retrieve data into host-variable arrays for your application use by your application
  • Place data into host-variable arrays to insert rows into a table
  • Retrieve data for the source of a merge operation.

Host-variable arrays can be referenced only as a simple reference in the following contexts. In syntax diagrams, host-variable-array designates a reference to a host-variable array.

  • In a FETCH statement for a multiple-row fetch. See FETCH statement.
  • In the FOR n ROWS form of the INSERT statement with a host-variable array for the source data. See INSERT statement.
  • In a MERGE statement with multiple rows of source data. See MERGE statement.
  • In an EXECUTE statement to provide a value for a parameter marker in a dynamic FOR n ROWS form of the INSERT statement or a MERGE statement. See EXECUTE statement.

Start of changeIf an application references a host-variable array in other contexts that are not supported, Db2 returns the SQLCODE -312 error.End of change

Host-variable arrays are defined by statements of the host language, as explained in the following topics:

Tip: Host-variable arrays are not supported for assembler, FORTRAN, or REXX programs. However, you can use SQL descriptor areas (SQLDA) to achieve similar results in any host language. For more information see Defining SQL descriptor areas (SQLDA).

Example

Begin general-use programming interface information.

The following statement uses the main host-variable array, COL1, and the corresponding indicator array, COL1IND. Assume that COL1 has 10 elements. The first element in the array corresponds to the first value, and so on. COL1IND must have at least 10 entries. 

EXEC SQL
  SQL FETCH FIRST ROWSET FROM C1 FOR 5 ROWS
    INTO :COL1 :COL1IND               
END-EXEC.
End general-use programming interface information.