Host Variables

A host variable is a program variable that is referenced in an SQL statement. It is the only kind of variable that you can use in an SQL statement embedded in application programs.

Host variables are necessary for the program to receive data from the database and in most cases for the program to modify data in the database.

How Host Variables Are Used

Host variables are used to:

Receive column values specified in a SELECT or FETCH statement
Supply column values specified in an UPDATE statement, INSERT statement, or other statements containing a search condition
Supply information for dynamically executed statements. For more information, see Chapter 8, Using Dynamic SQL.

Host Variable Example

In this example, DEPT-ID, EMP-LNAME, and EMP-ID are host variables. DEPT-ID and EMP-LNAME receive column values and EMP-ID supplies a column value used in the search condition of the statement:

EXEC SQL
  SELECT DEPT_ID,
         EMP_LNAME
    INTO :DEPT-ID,
         :EMP-LNAME
    FROM EMPLOYEE
    WHERE EMP_ID = :EMP-ID
END-EXEC.

Indicator Variable

An indicator variable is a host variable used to manipulate null values.

CA IDMS sets an indicator variable to -1 if the column value in the associated host variable is null.

An indicator variable should be defined for each column accessed by the program that could contain a null value. If the program retrieves a null value from a column that has no indicator variable, CA IDMS returns an error.

In a host variable array for use in bulk processing, the data type of an indicator variable must be declared with a usage SQLIND.

Null Value

A null value is the absence of a value and is not the same as spaces or numeric zeros, which are actual values. In an SQL-defined database, a column, regardless of data type, can contain a null value unless the column definition specifically disallows them.