Host Variables | Teradata Vantage - Host Variables - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™
A host variable is one of the following items that is referenced in an embedded SQL statement:
  • 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. They 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.

A host … Is a host variable …
main variable used to send data to or receive data from the database.
indicator variable that indicates any of the following:
  • A main variable is null on input
  • A main variable is null on output
  • For character or byte data, truncation on output

Host Variable Processing

At runtime, Preprocessor2 extracts values from the specified host input variables and sends them to the database, along 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

A number of rules apply to host variable usage. Several of these rules are independent of the embedded SQL statement in which a host variable is used.

Some statement-independent rules are noted below:
  • 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 executed, 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'

    Upon 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 very strongly recommended.

  • The COLON character is mandatory before an indicator host variable (indvar1 and indvar2) in the following example.

    This usage is always 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 is executed. 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • Host variable names must not begin with a numeric.
  • Host variable names should be unique within a program. This is mandatory 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 that might otherwise be ambiguous.

Teradata SQL requires a preceding COLON character in some situations, but not all.

The best practice is to precede all host variables with a COLON character, even when your session is running in Teradata mode.

Mandatory COLON Character Usage in Teradata Mode

Host variable references in an SQL statement must be preceded by a COLON character under the following conditions 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 could be either a column reference or a host variable reference.

    For example, in a WHERE clause, WHERE column_1 = field_1, field_1 either could 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 could be expected.
  • The reference occurs in a place other than in one of the items in the list.
  • A preceding COLON character is mandatory 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

Host variable references in an SQL statement are optionally preceded by a COLON character when the reference is one of the following 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 all host variables 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 they are supported in static SQL. Instead, dynamic SQL uses the question mark (?) placeholder, or parameter marker, token.

To illustrate the difference, consider the following parallel examples:

The first is a static SQL INSERT using host variables. The second is the same statement, but executed as a dynamic SQL statement using parameter markers instead of host variables.

INSERT INTO parts
VALUES (:part_no, :part_desc)
    
INSERT INTO parts
VALUES (?, ?)

Related Information