Host-variable arrays in C and C++

In C and C++ programs, you can specify numeric, character, graphic, binary, LOB, XML, and ROWID host-variable arrays. You can also specify LOB locators and LOB and XML file reference variables.

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

Restrictions:
  • Only some of the valid C declarations are valid host-variable array declarations. If the declaration for a variable array is not valid, any SQL statement that references the variable array might result in the message UNDECLARED HOST VARIABLE ARRAY.
  • For both C and C++, you cannot specify the _packed attribute on the structure declarations for the following arrays that are used in multiple-row INSERT, FETCH, and MERGE statements:
    • varying-length character arrays
    • varying-length graphic arrays
    • LOB arrays
    In addition, the #pragma pack(1) directive cannot be in effect if you plan to use these arrays in multiple-row statements.

Numeric host-variable arrays

The following diagram shows the syntax for declaring numeric host-variable arrays.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileunsignedfloatdoublelongshortintlong longintdecimal(precision,scale)_Decimal32_Decimal64_Decimal128,variable-name[dimension1]={,expression} ; 
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

Example

The following example shows a declaration of a numeric host-variable array:

EXEC SQL BEGIN DECLARE SECTION;
    /* declaration of numeric host-variable array */
    long serial_num[10];
    ...
EXEC SQL END DECLARE SECTION;

Character host-variable arrays

You can specify the following forms of character host-variable arrays:

  • NUL-terminated character form
  • VARCHAR structured form
  • CLOBs

The following diagrams show the syntax for forms other than CLOBs.

The following diagram shows the syntax for declaring NUL-terminated character host-variable arrays.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileunsignedchar ,variable-name[dimension1][length]={,expression} ;23
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.
  • 2 Any string that is assigned to this variable must be NUL-terminated. Any string that is retrieved from this variable is NUL-terminated.
  • 3 The strings in a NUL-terminated character host-variable array map to varying-length character strings (except for the NUL).

The following diagram shows the syntax for declaring varying-length character host-variable arrays that use the VARCHAR structured form.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilestruct1  { shortintvar-12  ; unsignedcharvar-23 [length]  ;  }  ,variable-name[dimension4]={,expression} ;
Notes:
  • 1 You can use the struct tag to define other variables, but you cannot use them as host-variable arrays in SQL.
  • 2 var-1 must be a scalar numeric variable.
  • 3 var-2 must be a scalar CHAR array variable.
  • 4 dimension must be an integer constant in the range 1–32767.

Example

The following example shows valid and invalid declarations of VARCHAR host-variable arrays.

EXEC SQL BEGIN DECLARE SECTION;
    /* valid declaration of VARCHAR host-variable array */
    struct VARCHAR {
      short len;
      char s[18];
      } name[10];

/* invalid declaration of VARCHAR host-variable array */
   struct VARCHAR name[10];

Binary host-variable arrays

The following diagram shows the syntax for declaring binary host-variable arrays.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISBINARYVARBINARY( length) ,variable-name[dimension1] ;
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

Graphic host-variable arrays

You can specify the following forms of graphic host-variable arrays:

  • NUL-terminated graphic form
  • VARGRAPHIC structured form.
Recommendation: Instead of using the C data type wchar_t to define graphic and vargraphic host-variable arrays, use one of the following techniques:
  • Define the sqldbchar data type by using the following typedef statement: 
    typedef unsigned short sqldbchar;
  • Use the sqldbchar data type that is defined in the typedef statement in the header files that are supplied by Db2.
  • Use the C data type unsigned short.

The following diagram shows the syntax for declaring NUL-terminated graphic host-variable arrays.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileunsignedsqldbchar ,variable-name[dimension1][length2]={,expression}  ; 34
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.
  • 2 length must be a decimal integer constant greater than 1 and not greater than 16352.
  • 3 Any string that is assigned to this variable must be NUL-terminated. Any string that is retrieved from this variable is NUL-terminated.
  • 4 Do not assign single-byte characters into a NUL-terminated graphic host-variable array

The following diagram shows the syntax for declaring graphic host-variable arrays that use the VARGRAPHIC structured form.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilestruct1  { shortintvar-12  ; unsignedsqldbcharvar-23 [length4 ] ;  }  ,variable-name[dimension5]={,expression} ;
Notes:
  • 1 You can use the struct tag to define other variables, but you cannot use them as host-variable arrays in SQL.
  • 2 var-1 must be a scalar numeric variable.
  • 3 var-2 must be a scalar char array variable.
  • 4 length must be a decimal integer constant greater than 1 and not greater than 16352.
  • 5 dimension must be an integer constant in the range 1–32767.

Example

The following example shows valid and invalid declarations of graphic host-variable arrays that use the VARGRAPHIC structured form.
EXEC SQL BEGIN DECLARE SECTION;
   /* valid declaration of host-variable array vgraph */
   struct VARGRAPH {
      short len;
      sqldbchar d[10];
      } vgraph[20];
 
   /* invalid declaration of host-variable array vgraph */
   struct VARGRAPH vgraph[20];

LOB, locator, and file reference variable arrays

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host-variable arrays, locators, and file reference variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_LOCATORCLOB_LOCATORDBCLOB_LOCATORBLOB_FILECLOB_FILEDBCLOB_FILE ,variable-name[dimension1]={,expression} ;
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

XML host and file reference variable arrays

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host-variable arrays and file reference variable arrays for XML data types.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE IS XML ASBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_FILECLOB_FILEDBCLOB_FILE ,variable-name[dimension1]={,expression} ;
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

ROWID variable arrays

The following diagram shows the syntax for declaring ROWID variable arrays.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISROWID ,variable-name[dimension1] ;
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.