SQL statements can contain variables from the host language, known as host variables. Host variables allow you to pass data from the database to the host language program, to process this data in the program and then to pass it back to the database. Within SQL statements, all host variables must be prefixed with a colon. Host variables can bear the same names as columns.
Host variables must be declared in a DECLARE SECTION before they are used. A DECLARE SECTION in ESQL-COBOL starts with “EXEC SQL BEGIN DECLARE SECTION END-EXEC” and ends with “EXEC SQL END DECLARE SECTION END-EXEC”. An ESQL program may include any number of DECLARE SECTIONs.
DECLARE SECTIONs may only occur in locations where variable declarations are permitted in the host language. If a host variable is used in an executable SQL statement, a reference to the declaration must be permitted at this location in accordance with the syntax of the host language.
When defining host variables, you must ensure that the data types of the host language are compatible with the SQL data types. This compatibility must be ensured not only when passing values to or from the database, but also when using host variables in expressions.
Indicator variables
Indicator variables are host variables which can be used to check whether information is lost when transferring an alphanumeric or a national value to a host variable or whether a column contains a null value. Indicator variables are also used to pass null values to the database.
If an alphanumeric or a national value was passed to a host variable without truncation, the indicator variable contains the value “0”. If the length of the value passed was greater then the length of the host variable, the indicator variable contains the actual length of the value passed.
If NULL is to be passed from the database to a host variable, the indicator variable is assigned the value “-1” and the host variable is unchanged. If NULL is to be passed from a host variable to the database, the relevant indicator variable must be assigned a negative value.
An indicator variable is assigned to a host variable in an SQL statement by specifying the indicator variable immediately following the host variable, with the optional keyword INDICATOR.
Indicator variables can be specified in the following situations:
in the RETURN INTO or VALUES clause of the INSERT statement
in the VALUES clause of the MERGE statement
in the INTO clause of the SELECT or FETCH statement
in the SET clause of UPDATE or MERGE statements
in the USING and INTO clause of the EXECUTE statement
in the USING clause of the OPEN statement
in expressions, e.g. in DELETE or DECLARE CURSOR statements.