Host variables
Host variables are defined directly by statements of the host language or indirectly by SQL extensions. A host-variable in an SQL statement must identify a host variable that is described in the program according to the rules for declaring host variables. Host variables cannot be referenced in dynamic SQL statements; parameter markers must be used instead.
A host variable is either of these items that is referred to in an SQL statement:
- A variable in a host language such as a PL/I variable, C variable, Fortran variable, REXX variable, Java variable, COBOL data item, or Assembler language storage area
- A host language construct that was generated by an SQL precompiler from a variable declared using SQL extensions
Host variables are defined directly by statements of the host language or indirectly by SQL extensions as described in DB2 Application Programming and SQL Guide. Host variables cannot be referenced in dynamic SQL statements; parameter markers must be used instead. For more information about parameter markers, see Variables in dynamic SQL.
A host-variable in an SQL statement must identify a host variable that is described in the program according to the rules for declaring host variables.
In PL/I, C, and COBOL, host variables can be referred to in ways that do not apply to Fortran and Assembler language. This is explained in Host structures in PL/I, C, and COBOL. The following applies to all host languages.
The term host-variable, as used in the syntax diagrams, shows a reference to a host variable. In a SET host variable statement and the INTO clause of a FETCH, SELECT INTO, or VALUES INTO statement, a host variable is an output variable to which a value is assigned by DB2®. In a CALL statement, a host variable can be an output argument that is assigned a value after execution of the procedure, an input argument that provides an input value for the procedure, or both an input and output argument. In all other contexts, a host variable is an input variable which provides a value to DB2.
- Non-Java variable references
- The general form of a host variable reference in all languages other than Java is:
>>-:host-identifier-+--------------------------------+--------->< | .-INDICATOR-. | '-+-----------+-:host-identifier-'
Each host identifier must be declared in the source program, except in a program written in REXX. The first host identifier designates the main variable; the second host identifier designates its indicator variable. The variable designated by the second host identifier must be a small integer. Indicator variables appear in two forms, normal indicator variables and extended indicator variables.
The purposes of normal indicator variable are to:- Specify a non-null value. A 0 (zero), or positive value of the indicator variable specifies that the associated, first host-identifier provides the value of this host variable reference.
- Specify the null value. A negative value of the indicator variable specifies the null value.
In addition, on output, an indicator variable can indicate the following :- A numeric conversion error (such as a divide by 0 or overflow) has occurred. A value of -2 for the indicator variable indicates a null result because of either numeric truncation or arithmetic warnings.
- A character could not be converted. A value of -2 for the indicator variable indicates a null result because of character string conversion warnings.
- No value was returned. A value of -3 for the indicator variable indicates a null result because the current row of the cursor is on a hole that is detected during a multiple row FETCH.
- Report the original length of a truncated string, if the string is not a LOB.
- Report the seconds portion of a time if the time is truncated on assignment to a host variable.
Extended indicator variables are limited to the input of host variables, and can specify the following:- A non-null value. A 0 (zero), or positive value specifies that the associated, first host-identifier provides the value of this host variable reference.
- The null value. A -1, -2, -3, -4, or -6 value specifies the null value.
- The default value. A -5 value specifies that the target column for this host variable is to be set to its default value.
- An unassigned value. A -7 value specifies that the target column for this host variable is to be treated as if it had not been specified in the statement.
Extended indicator variables are only enabled if requested, and all indicator variables are otherwise normal indicator variables. Extended indicator variables are enabled when EXTENDEDINDICATOR(YES) is used, or when the WITH EXTENDED INDICATORS prepare attribute has been specified for the statement. In comparison to normal indicator variables, extended indicator variables have no additional restrictions for where the values for null and non-null can be used. There are no restrictions against using extended indicator variable values in indicator structures with host structures. There are no restrictions that result from the use of extended indicator variable values with host arrays in multiple-row statements. Restrictions on where the extended indicator variable values of default and unassigned are allowed apply uniformly, no matter how they are represented in the host application. The default and unassigned extended indicator variable values can only appear in limited, specified uses. Output indicator variables are never extended indicator variables.
When extended indicator variables are enabled, there are no restrictions against use of 0 (zero), or positive indicator variable values. However, negative indicator variable values outside the range -1 through -7 must not be specified. When extended indicator variables are enabled, the default and unassigned extended indicator values must not appear in contexts in which they are not supported.
When extended indicator variables are enabled, if the value of an extended indicator variable is greater than or equal to zero, the data type of the input host variable must be compatible with the data type of the target column. If the value of an extended indicator variable is less than zero, DB2 does not test for data type compatibility between the input host variable and the target column.
- Java variable references
- The general form of a host variable reference in Java is:
>>-:--+-------+--+-Java-identifier---+--------------------------> +-IN----+ '-(Java-expression)-' +-OUT---+ '-INOUT-' >--+-----------------------------------+----------------------->< | .-INDICATOR-. | '-+-----------+--:--Java-identifier-'
Each Java-identifier must be declared in the source program. The variable designated by the second Java-identifier is called an indicator variable and must be a short.
In Java, indicator variables are not always needed. Instead, instances of a Java class can be set to a null value. Variables defined as Java primitive types can not be set to a null value. When using an extended indicator variable, or when using a Java primitive type in assigning a null value or where the Java primitive type might be assigned null on output, indicator variables must be used.
If IN, OUT, or INOUT is not specified, the default depends on the context in which the variable is used. If the Java variable is used in an INTO clause, OUT is the default. Otherwise, IN is the default.
An SQL statement that refers to host variables must be within the scope of the declaration of those host variables. For host variables referred to in the SELECT statement of a cursor, the OPEN statement, and the DECLARE CURSOR statement have to be in the same scope.
All references to host variables must be preceded by a colon. If an SQL statement references a host variable without a preceding colon, the precompiler issues an error for the missing colon or interprets the host variable as an unqualified column name, which might lead to unintended results. The interpretation of a host variable without a colon as a column name occurs when the host variable is referenced in a context in which a column name can also be referenced.