- A host language variable that is defined directly with statements in that host language.
- A host language SQL-based construct that is generated by Preprocessor2 and indirectly defined from within SQL.
The colon-prefixed variables in the USING request modifier and the variables in stored procedure local variables and parameters perform the same function as embedded SQL host variables.
Purpose of Host Variables
Host variables provide input values to SQL statements or receive output values from SQL requests. Host variables are identified by name in an embedded SQL statement (for example, Value-Var or HostIn-Var).
A host variable within an embedded SQL statement has a 1:1 relationship with a host variable of the same name declared in the host language of an application between the SQL BEGIN DECLARE SECTION and END DECLARE SECTION statements.
Classification of Host Variables
Host variables are classified into main and indicator categories.
| Host Variable Type | Description |
|---|---|
| Main | Used to send data to or receive data from the database. |
| Indicator | Indicates any of the following:
|
Host Variable Processing
At run time, Preprocessor2 extracts values from the specified host input variables and sends those values to the database, with the SQL statement to be processed. The functionality is similar to the Teradata interactive SQL USING clause with associated data, or to the Teradata SQL EXEC statement for a macro with parameters.
When the database returns the results of an SQL data returning statement to the client application program, Preprocessor2 places the corresponding values into the specified host output variables, which are listed in an INTO clause, separated by commas.
A host main variable can also be associated with a host indicator variable.
Rules for Using Host Variables
- All host variables must be preceded by a COLON character.
Specify the COLON character to distinguish a variable from a table column reference. Example 1:
SELECT * FROM table INTO :intofield1 WHERE COL1 = :hostvar1
When run, the value of hostvar1 is substituted into the WHERE clause as though a constant were specified.
Example 2:
SELECT :hostvar1, COL1, COL2 + :hostvar2 INTO :intofield1, :intofield2 INDICATOR :indvar1, :intofield3 INDICATOR :indvar2 FROM table WHERE COL3 = 'ABC'
On execution, the values in hostvar1 and hostvar2 are substituted into the SELECT list as though a constant had been specified.
In Teradata mode, the COLON character preceding the host variables (intofieldn, for example) is optional, but its use is strongly recommended.
- The COLON character is required before an indicator host variable (indvar1 and indvar2) in the following example.
This usage is associated with the INTO clause of data returning statements or the cursor-related FETCH statement.
SELECT column_1, column_2 INTO :intofield1 INDICATOR :indvar1, :intofield2 INDICATOR :indvar2 FROM table WHERE column_3 = 'ABC'
:indvarn indicates whether the associated :intofieldn is null, for character and byte string data, whether any truncation occurred in returning the database data to the field.
:intofieldn contains the value of column_n when the statement runs. If column_n is null, then the value of :intofieldn is indeterminate.
- Pad characters before or after the COLON character are optional. For more information on COLON character usage with host variables with the individual statements, see the following:
- Host variable names must not begin with a numeric.
- Host variable names must be unique within a program. This is required for applications written in C and strongly recommended for applications written in COBOL and PL/I.
- In a WHERE clause, use a host variable to specify the value in a search condition or to replace a literal in the search expression.
Indicator variables are allowed in a WHERE clause.
- You can use a host variable in a SELECT list either as part of an expression or by itself to represent a single column.
- Indicator variables are not allowed in the SELECT list.
- You can use host and indicator variables in the VALUES clause of the INSERT statement or in the SET clause of the UPDATE statement.
- You can use host variables in CHECKPOINT, DATABASE and LOGON statements to complete the command (that is, as SQL strings).
- You can use host variables to identify the application storage areas to receive the output of data returning statements.
COLON Character Usage with Host Variables
The ANSI/ISO SQL standard mandates that all host variables be preceded by a COLON character to distinguish them from SQL column references.
Teradata SQL requires a preceding COLON character in specific situations.
The best practice is to precede every host variable with a COLON character, even when your session is running in Teradata mode.
Required COLON Character Usage in Teradata Mode
- The host variable name is an SQL reserved word.
- The host variable is used as an indicator variable.
- The syntax usage is ambiguous such that the name can be either a column reference or a host variable reference.
For example, in a WHERE clause, WHERE column_1 = field_1, field_1 either can be a column in a table or a host variable.
- The reference is in a DATABASE statement; that is, DATABASE :var1.
- The reference is the object of a SET CHARSET statement.
- The reference is an argument of a Teradata function; for example, ABS(:var_1)
- A COBOL variable name intended for use as an input variable begins with a numeric character (0-9) where a numeric constant can be expected.
- The reference occurs in a place other than in one of the items in the list.
- A preceding COLON character is required for all host variables specified in the SET or WHERE clauses of an UPDATE statement and for all host variables specified in a match_condition, SET, or INSERT clause in a MERGE statement.
Optional COLON Character Usage in Teradata Mode
- In an INTO clause.
- Either the id or password variable in a CONNECT statement.
- In the FOR STATEMENT clause of a DESCRIBE or PREPARE statement.
- In the USING clause of an EXECUTE or OPEN statement.
- In the DESCRIPTOR clause of an EXECUTE, FETCH or OPEN statement.
- The object of a LOGON statement.
- The object of an EXECUTE IMMEDIATE statement.
- In the VALUES clause of an INSERT statement.
- In the TO STATEMENT clause of a POSITION statement.
The best practice is to precede every host variable with a COLON character, even when your session is in Teradata transaction mode.
Host Variables in Dynamic SQL
Dynamic SQL does not support host variables in the same way as static SQL. Instead, dynamic SQL uses the question mark (?) placeholder, or parameter marker, token.
To show the difference, consider the following parallel examples:
The first is a static SQL INSERT using host variables. The second is the same statement, but run as a dynamic SQL statement using parameter markers instead of host variables.
INSERT INTO parts VALUES (:part_no, :part_desc) INSERT INTO parts VALUES (?, ?)