Host variables in C and C++

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

Restrictions:
  • Only some of the valid C declarations are valid host variable declarations. If the declaration for a variable is not valid, any SQL statement that references the variable might result in the message UNDECLARED HOST VARIABLE.
  • C supports some data types and storage classes with no SQL equivalents, such as register storage class, typedef, and long long.
  • The following locator data types are special SQL data types that do not have C equivalents:
    • Result set locator
    • Table locator
    • LOB locators
    You cannot use them to define column types.
  • Although Db2 allows you to use properly formed L-literals in C application programs, Db2 does not check for all the restrictions that the C compiler imposes on the L-literal. \
  • Do not use L-literals in SQL statements. Use Db2 graphic string constants in SQL statements to work with the L-literal.
Recommendations:
  • Be careful of overflow. For example, suppose that you retrieve an INTEGER column value into a short integer host variable, and the column value is larger than 32767. You get an overflow warning or an error, depending on whether you provide an indicator variable.
  • Be careful of truncation. Ensure that the host variable that you declare can contain the data and a NUL terminator, if needed. Retrieving a floating-point or decimal column value into a long integer host variable removes any fractional part of the value.

Numeric host variables

The following diagram shows the syntax for declaring numeric host variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilefloatdoubleshortintsqlint32longintlong longintdecimal(precision,scale)_Decimal32_Decimal64_Decimal128,variable-name*pointer-name1= expression ; 
Notes:
  • 1 If you use the pointer notation of the host variable, you must use the Db2 coprocessor.
Restrictions:
  • If your C compiler does not have a decimal data type, no exact equivalent exists for the SQL data type DECIMAL. In this case, you can use one of the following variables or techniques to handle decimal values:
    • An integer or floating-point variable, which converts the value. If you use an integer variable, you lose the fractional part of the number. If the decimal number can exceed the maximum value for an integer or if you want to preserve a fractional value, use floating-point variables. Floating-point numbers are approximations of real numbers. Therefore, when you assign a decimal number to a floating-point variable, the result might be different from the original number.
    • A character-string host variable. Use the CHAR function to get a string representation of a decimal number.
    • The DECIMAL function to explicitly convert a value to a decimal data type, as shown in the following example: 
      long duration=10100;  /* 1 year and 1 month */
char result_dt[11];
 
EXEC SQL SELECT START_DATE + DECIMAL(:duration,8,0)
         INTO :result_dt FROM TABLE1;
  • z/OS® 1.10 or above (z/OS V1R10 XL C/C++ ) is required to use the decimal floating-point host data type.
  • The special C only 'complex floating-point' host data type is not a supported type for host variable.
  • The FLOAT precompiler option does not apply to the decimal floating-point host variable types.
  • To use decimal floating-point host variable, you must use the Db2 coprocessor.

For floating-point data types, use the FLOAT SQL processing option to specify whether the host variable is in IEEE binary floating-point or z/Architecture® hexadecimal floating-point format. Db2 does not check if the format of the host variable contents match the format that you specified with the FLOAT SQL processing option. Therefore, you need to ensure that your floating-point host variable contents match the format that you specified with the FLOAT SQL processing option. Db2 converts all floating-point input data to z/Architecture hexadecimal floating-point format before storing it.

Character host variables

You can specify the following forms of character host variables:

  • Single-character form
  • 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 single-character host variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileunsignedchar,variable-name*pointer-name1= expression ; 
Notes:
  • 1 If you use the pointer notation of the host variable, you must use the Db2 coprocessor.

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

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileunsignedchar,variable-name*pointer-name1[length]= expression ; 23
Notes:
  • 1 If you use the pointer notation of the host variable, you must use the Db2 coprocessor.
  • 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 A NUL-terminated character host variable maps to a varying-length character string (except for the NUL).

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

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilestruct1 tag { shortint var-1  ;  unsignedcharvar-22 [length] ;   } ,variable-name*pointer-name3={ expression, expression} ; 
Notes:
  • 1 You can use the struct tag to define other variables, but you cannot use them as host variables in SQL.
  • 2 You cannot use var-1 and var-2 as host variables in an SQL statement.
  • 3 If you use the pointer notation of the host variable, you must use the Db2 coprocessor.

Example

The following example code shows valid and invalid declarations of the VARCHAR structured form: 

EXEC SQL BEGIN DECLARE SECTION;

    /* valid declaration of host variable VARCHAR vstring */
    struct VARCHAR {
      short len;
      char s[10];
      } vstring;

    /* invalid declaration of host variable VARCHAR wstring */
    struct VARCHAR wstring;

For NUL-terminated string host variables, use the SQL processing options PADNTSTR and NOPADNTSTR to specify whether the variable should be padded with blanks. The option that you specify determines where the NUL-terminator is placed.

If you assign a string of length n to a NUL-terminated string host variable, the variable has one of the values that is shown in the following table.

Table 1. Value of a NUL-terminated string host variable that is assigned a string of length n
Length of the NUL-terminated string host variable Value of the variable
Less than or equal to n The source string up to a length of n-1 and a NUL at the end of the string. 1

Db2 sets SQLWARN[1] to W and any indicator variable that you provide to the original length of the source string.
Equal to n+1 The source string and a NUL at the end of the string. 1
Greater than n+1 and the source is a fixed-length string
If PADNTSTR is in effect
The source string, blanks to pad the value, and a NUL at the end of the string.
If NOPADNTSTR is in effect
The source string and a NUL at the end of the string.
Greater than n+1 and the source is a varying-length string The source string and a NUL at the end of the string. 1
Note:
  1. In these cases, whether NOPADNTSTR or PADNTSTR is in effect is irrelevant.
Restriction: If you use the Db2 precompiler, you cannot use a host variable that is of the NUL-terminated form in either a PREPARE or DESCRIBE statement. However, if you use the Db2 coprocessor, you can use host variables of the NUL-terminated form in PREPARE, DESCRIBE, and EXECUTE IMMEDIATE statements.

Graphic host variables

You can specify the following forms of graphic host variables:

  • Single-graphic form
  • NUL-terminated graphic form
  • VARGRAPHIC structured form.
  • DBCLOBs
Recommendation: Instead of using the C data type wchar_t to define graphic and vargraphic host variables, 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 one of the following files or libraries:
    • SQL library, sql.h
    • Db2 CLI library, sqlcli.h
    • SQLUDF file in data set DSN1210.SDSNC.H
  • Use the C data type unsigned short.

Using sqldbchar or unsigned short enables you to manipulate DBCS and Unicode UTF-16 data in the same format in which it is stored in Db2. Using sqldbchar also makes applications easier to port to other platforms.

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

The following diagram shows the syntax for declaring single-graphic host variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilesqldbchar,variable-name1*pointer-name= expression ; 2
Notes:
  • 1 You cannot use array notation in variable-name.
  • 2 The single-graphic form declares a fixed-length graphic string of length 1.

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

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilesqldbchar,variable-name*pointer-name1[length2]= expression ; 34
Notes:
  • 1 If you use the pointer notation of the host variable, you must use the Db2 coprocessor.
  • 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 The NUL-terminated graphic form does not accept single-byte characters for the variable.

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

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatilestruct1 tag { shortint var-12  ;  sqldbcharvar-23[length4 ] ;  } ,variable-name*pointer-name5={ expression, expression} ; 
Notes:
  • 1 You can use the struct tag to define other variables, but you cannot use them as host variables in SQL.
  • 2 var-1 must be less than or equal to length.
  • 3 You cannot use var-1 or var-2 as host variables in an SQL statement.
  • 4 length must be a decimal integer constant greater than 1 and not greater than 16352.
  • 5 f you use the pointer notation of the host variable, you must use the Db2 coprocessor.

Example

The following example shows valid and invalid declarations of graphic host variables that use the VARGRAPHIC structured form: 

EXEC SQL BEGIN DECLARE SECTION;
 
   /* valid declaration of host variable structured vgraph */
   struct VARGRAPH {
      short len;
      sqldbchar d[10];
      } vgraph;
 
   /* invalid declaration of host variable structured wgraph */
   struct VARGRAPH wgraph;

Binary host variables

You can specify the following forms of binary host variables:

  • Fixed-length strings
  • Varying-length strings
  • BLOBs

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

The following diagram shows the syntax for declaring binary host variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileSQL TYPE IS BINARY ( length1 ) ,variable-name ; 
Notes:
  • 1 The length must be a value in the range 1–255.

The following diagram shows the syntax for declaring VARBINARY host variables.

Read syntax diagramSkip visual syntax diagramautoexternstaticconstvolatileSQL TYPE ISVARBINARY1BINARY VARYING(length) ,variable-name = {init-len ,"init-data"} ; 
Notes:
  • 1 For VARBINARY host variables, the length must be in the range 1–32704.

The C language does not have variables that correspond to the SQL binary data types BINARY and VARBINARY. To create host variables that can be used with these data types, use the SQL TYPE IS clause. The SQL precompiler replaces this declaration with the C language structure in the output source member.

When you reference a BINARY or VARBINARY host variable in an SQL statement, you must use the variable that you specify in the SQL TYPE declaration. When you reference the host variable in a host language statement, you must use the variable that Db2 generates.

Examples of binary variable declarations

The following table shows examples of variables that Db2 generates when you declare binary host variables.
Table 2. Examples of BINARY and VARBINARY variable declarations for C
Variable declaration that you include in your C program Corresponding variable that Db2 generates in the output source member 
SQL TYPE IS BINARY(10) bin_var;
 
char bin_var[10]
 
SQL TYPE IS VARBINARY(10) vbin_var;
 
struct {
        short length;
        char data[10];
} vbin_var;
Recommendation: Be careful when you use binary host variables with C and C++. The SQL TYPE declaration for BINARY and VARBINARY does not account for the NUL-terminator that C expects, because binary strings are not NUL-terminated strings. Also, the binary host variable might contain zeroes at any point in the string.

Result set locators

The following diagram shows the syntax for declaring result set locators.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISRESULT_SET_LOCATORVARYING ,variable-name*pointer-name= init-value;

Table locators

The following diagram shows the syntax for declaring table locators.

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISTABLE LIKEtable-name AS LOCATOR,variable-name*pointer-name= init-value;

LOB variables, locators, and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variables, 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*pointer-name= init-value1;
Notes:
  • 1 Specify the initial value as a series of expressions. For example, specify ={expression, expression}. For BLOB_FILE, CLOB_FILE, and DBCLOB_FILE, specify ={name_length, data_length, file_option_map, file_name}.

XML data host and file reference variables

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

Read syntax diagramSkip visual syntax diagramautoexternstaticregisterconstvolatileSQL TYPE ISXML AS BLOBXML AS CLOBXML AS DBCLOBXML AS BLOB_FILEXML AS CLOB_FILEXML AS DBCLOB_FILE,variable-name*pointer-name= init-value;1
Notes:
  • 1 Specify the initial value as a series of expressions. For example, specify ={expression, expression}. For BLOB_FILE, CLOB_FILE, and DBCLOB_FILE, specify ={name_length, data_length, file_option_map, file_name}.

ROWID host variables

The following diagram shows the syntax for declaring ROWID host variables.

Read syntax diagramSkip visual syntax diagram autoexternstaticregister constvolatile variable-name*pointer-name SQL TYPE ISROWID;

Constants

The syntax for constants in C and C++ programs differs from the syntax for constants in SQL statements in the following ways:

  • C/C++ uses various forms for numeric literals (possible suffixes are: ll, LL, u, U, f,F,l,L,df,DF, dd, DD, dl, DL,d, D). For example, in C/C++:
    • 4850976 is a decimal literal
    • 0x4bD is a hexadecimal integer literal
    • 03245 is an octal integer literal
    • 3.2E+4 is a double floating-point literal
    • 3.2E+4f is a float floating-point literal
    • 3.2E+4l is a long double floating-point literal
    • 0x4bDP+4 is a double hexadecimal floating-point literal
    • 22.2df is a _Decimal32 decimal floating-point literal
    • 0.00D is a fixed-point decimal literal (z/OS only when LANGLVL(EXTENDED) is specified)
  • Use C/C++ literal form only outside of SQL statements. Within SQL statements, use numeric constants.
  • In C, character constants and string constants can use escape sequences. You cannot use the escape sequences in SQL statements.
  • Apostrophes and quotation marks have different meanings in C and SQL. In C, you can use double quotation marks to delimit string constants, and apostrophes to delimit character constants.
    Example: Use of quotation marks in C
    printf(  "%d lines read. \n", num_lines);
    Example: Use of apostrophes in C
    #define NUL '\0'

    In SQL, you can use double quotation marks to delimit identifiers and apostrophes to delimit string constants.

    Example: quotation marks in SQL
    SELECT "COL#1" FROM TBL1;
    Example: apostrophes in SQL
    SELECT COL1 FROM TBL1 WHERE COL2 = 'BELL';
  • Character data in SQL is distinct from integer data. Character data in C is a subtype of integer data.