A host variable is a COBOL data item, an RPG field, or a PLI, REXX, C++, or C variable that is referenced in an SQL statement. Host variables are defined by statements of the host language. For more information about how to refer to host structures in C, C++, COBOL, PL/I, and RPG, see Host structures. For more information about host variables in REXX, see the Embedded SQL programming topic.
A host variable in an SQL statement must identify a host variable described in the program according to the rules for declaring host variables.
All host variables used in an SQL statement should be declared in an SQL declare section in all host languages other than Java(TM), REXX, and RPG. Variables do not have to be declared in REXX. In Java and RPG, there is no declare section, and host variables may be declared throughout the program. No variables may be declared outside an SQL declare section with names identical to variables declared inside an SQL declare section. An SQL declare section begins with BEGIN DECLARE SECTION and ends with END DECLARE SECTION.
For further information about using host variables, see the Embedded SQL programming topic.
A variable in the INTO clause of a FETCH, a SELECT INTO, a SET variable, a GET DESCRIPTOR, or a VALUES INTO statement identifies a host variable to which a value from a result column is assigned. A variable in the GET DIAGNOSTICS statement identifies a host variable to which a diagnostic value is assigned. A host variable in a CALL or in an EXECUTE statement 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 variable specifies a value to be passed to the database manager from the application program.
Non-Java variable references: The general form of a variable reference in all languages other than Java is:
>>-:host-identifier--+---------------------------------+------->< | .-INDICATOR-. | '-+-----------+--:host-identifier-'
Each host-identifier must be declared in the source program. The variable designated by the second host-identifier is called an indicator variable and must have a data type of small integer.
The purposes of the indicator variable are to:
For example, if :V1:V2 is used to specify an insert or update value, and if V2 is negative, the value specified is the null value. If V2 is not negative the value specified is the value of V1.
Similarly, if :V1:V2 is specified in a CALL, FETCH, SELECT INTO, or VALUES INTO statement and the value returned is null, V1 is undefined, and V2 is set to a negative value. The negative value is:
If the value returned is not null, that value is assigned to V1 and V2 is set to zero (unless the assignment to V1 requires string truncation, in which case, V2 is set to the original length of the string). If an assignment requires truncation of the seconds part of time, V2 is set to the number of seconds.
If the second host-identifier is omitted, the host variable does not have an indicator variable. The value specified by the host variable :V1 is always the value of V1, and null values cannot be assigned to the variable. Thus, this form should not be used unless the corresponding result column cannot contain null values. If this form is used and the column contains nulls, the database manager will return an error at run-time (SQLSTATE 23502).
An SQL statement that references host variables in C, C++, ILE RPG, and PL/I, must be within the scope of the declaration of those host variables. For host variables referenced in the SELECT statement of a cursor, that rule applies to the OPEN statement rather than to the DECLARE CURSOR statement.
The CCSID of a string host variable is either:
Java variable references: The general form of a host variable reference in Java is:
>>-:--+-------+--+-Java-identifier-------+--------------------->< +-IN----+ '-(--Java-expression--)-' +-OUT---+ '-INOUT-'
In Java, indicator variables are not used. Instead, instances of a Java class can be set to a null value. Variables defined as Java primitive types cannot be set to a null value.
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. For more information on Java variables, see IBM(R) Developer Kit for Java.
Using the PROJECT table, set the host variable PNAME (VARCHAR(26)) to the project name (PROJNAME), the host variable STAFF (DECIMAL(5,2)) to the mean staffing level (PRSTAFF), and the host variable MAJPROJ (CHAR(6)) to the major project (MAJPROJ) for project (PROJNO) 'IF1000'. Columns PRSTAFF and MAJPROJ may contain null values, so provide indicator variables STAFF_IND (SMALLINT) and MAJPROJ_IND (SMALLINT).
SELECT PROJNAME, PRSTAFF, MAJPROJ INTO :PNAME, :STAFF :STAFF_IND, :MAJPROJ :MAJPROJ_IND FROM PROJECT WHERE PROJNO = 'IF1000'
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.