When an SQL statement with host variable inputs is sent to the Teradata Database client for processing, Preprocessor2 extracts the input values from the corresponding host input variables and then sends them to the Teradata Database for processing.
Within stored procedures, host input variables are referred to as IN parameters. Another class of stored procedure parameter, INOUT, can be used to pass data into and out of the procedure.
Rules
- When an input main host variable is used in an expression in a WHERE clause, or as part of a SELECT list, the data type of the host variable and the data type of the expression must be drawn from the same domain.
You cannot specify an indicator host variable for input main host variables used in this way.
- When you use an input main host variable to provide data for an INSERT or UPDATE, the data type of the host variable and the data type of the expression must be drawn from the same domain.
Teradata SQL does allow mixing of character and numeric types.
You can specify an indicator host variable for input main host variables used in this way. This data type rule does not apply to an input main host variable if an associated indicator host variable (see ) is specified and the indicator variable shows NULL.
- If an indicator variable is specified for an input main host variable that corresponds to a non-nullable table column, then the indicator variable must always indicate not NULL.
- Exercise care in using CHARACTER host variables as input to VARCHAR fields.
The system strips all trailing blanks from the host value, including a single blank if that is what the host variable contains.
For example, a host variable typed as CHARACTER(3) with a value of A ('A ') loaded into a Teradata Database field typed as VARCHAR(10) results in the value A (‘A’) with the varying length set to 1. The two trailing pad characters are lost.
Similarly, if the host variable has a length of 1 and the value of the field is blank, the VARCHAR field is neither blank nor null, but is a string having length 0.
This feature differs from other systems that preserve all of the pad characters that are passed to a VARCHAR field. To preserve pad characters for a VARCHAR field, define the host variable as a VARCHAR field with length number_of_characters + number_of_pad_characters. For example, a field containing 'A ' should be defined as VARCHAR(3) rather than CHARACTER(3).
Static Request Input Host Variables
Specify input variables used in static SQL requests by referencing the variable name where it is appropriate in the SQL statement.
The following statement is an example of a static request using an input host variable:
EXEC SQL SELECT field1 FROM table1 WHERE field2 = :var1
:var1 represents a properly defined host variable that is used to determine the rows to be selected from the table.
The application can change the value of var1 between SQL statement executions.
Dynamic Request Input Host Variables
- Those executed using an OPEN statement for a dynamic cursor
- Those executed using an EXECUTE statement
Do not use input host variables with EXECUTE IMMEDIATE.
Input host variables in a request are represented by the question mark (?) token, referred to as a parameter marker.
- By use of host variable names
- By use of an input SQLDA to describe the variables
For example, assume that the following statement has been successfully prepared using a dynamic name of S1:
DELETE FROM table1 WHERE field1 = ?
To specify the variable to be substituted for the question mark (?), the application code would contain one of the following statements:
EXEC SQL EXECUTE S1 USING :var1
or
EXEC SQL EXECUTE S1 USING DESCRIPTOR INPUTDA
where INPUTDA is a programmer-defined SQLDA structure.
Preprocessor2 extracts the value from the host variable when the statement is executed and passes it to the Teradata Database in place of the question mark (?) parameter marker token.
Related Topics
- INOUT, see Parameters and Rules for IN, OUT, and INOUT Parameters
- Associated indicator host variable or indicator variables, see Indicator Variables