When the results of a data returning SQL statement are received from the Teradata Database, Preprocessor2 extracts the values and places them into the corresponding host output variables.
Valid Data Type Combinations
When you use an output main host variable to receive data from a FETCH or SELECT statement, only certain combinations of SELECT list element and host variable data types are allowed.
The valid combinations are shown in the following table. No other combinations are valid.
Most other combinations can be used by forcing the table column to change to a data type that is compatible with the data type host variable.
The data types listed in the Host Variable Data Type column are generic.
| Teradata Database Column Data Type | Host Variable Data Type |
|---|---|
| BYTE(m) VARBYTE(m) |
|
| CHARACTER(m) VARCHAR(m) |
CHARACTER(n) VARCHAR(n) |
| DATE (Teradata) |
|
| DATE (ANSI) TIME TIMESTAMP INTERVAL |
CHARACTER(n) |
|
|
| GRAPHIC(m) VARGRAPHIC(m) |
CHARACTER(n) CHARACTER SET GRAPHIC VARCHAR(n) CHARACTER SET GRAPHIC |
| UDT | Not currently supported. The fromsql transform target data type defined by a CREATE TRANSFORM statement for the UDT. |
Assignment Rules
The following table explains assignment rules for output host variables.
| Data Type | Condition | Description |
|---|---|---|
| BYTE | m < n | m bytes of data are moved to the host variable with (n - m) bytes of x’00’ added. |
| m = n | m bytes of data are moved to the host variable. | |
| m > n | n bytes of data are moved to the host variable; the indicator, if used, is set to m; SQLWARN1 in the SQLCA is set to ‘W.’ m represents the length of the data. n represents the length of the host variable. BYTEINT, SMALLINT and INTEGER have implied lengths of 1, 2 and 4, respectively. DECIMAL can have a length from 1 to 16 bytes. FLOAT can be single (4 bytes) or double (8 bytes). No data conversion is performed when a BYTE field is assigned to a host variable. The application is responsible for processing the value returned. |
|
| VARBYTE | m ≤ n | m bytes of data are moved to the host variable. |
| m > n | n bytes of data are moved to the host variable; the indicator, if used, is set to m; SQLWARN1 in the SQLCA is set to ‘W.’ m represents the current length of the data; n represents the maximum length of the host variable. BYTEINT, SMALLINT and INTEGER have implied lengths of 1, 2 and 4, respectively. DECIMAL can have a length from 1 to 16 bytes. FLOAT can be single (4 bytes) or double (8 bytes). No data conversion is performed when a BYTE field is assigned to a host variable. The application is responsible for processing the returned value. |
|
| CHARACTER | m < n | m bytes of data are moved to the host variable with (n - m) bytes of blanks (x’40’ in EBCDIC, x’20’ in ASCII environments) added. |
| m = n | m bytes of data are moved to the host variable. | |
| m >n | n bytes of data are moved to the host variable; the indicator, if used, is set to m; SQLWARN1 in the SQLCA is set to ‘W.’ m represents the length of the data; n represents the length of the host variable. |
|
| VARCHAR | m ≤ n | m bytes of data are moved to the host variable. |
| m > n | n bytes of data are moved to the host variable; the indicator, if used, is set to m; SQLWARN1 in the SQLCA is set to ‘W.’ m represents the current length of the data; n represents the maximum length of the host variable. |
|
| DATE (Teradata) | into a CHARACTER field: If Teradata Database format is requested, n must be at least 8 bytes. All other formats require n to be at least 10 bytes. Remaining bytes are set to blanks (x’40’ in EBCDIC, x’20’ in ASCII environments). SQLCODE in the SQLCA is set to -304 if the host variable cannot contain the requested date format. | |
| into a numeric field: The value must be representable in the type specified without losing leading digits. SQLCODE in the SQLCA is set to -304 if the host variable cannot contain the data. | ||
| DATE (ANSI) TIME TIMESTAMP INTERVAL |
If Teradata Database format is requested, n must be at least 8 bytes. All other formats require n to be at least 10 bytes. Remaining bytes are set to blanks (x’40’ in EBCDIC, x’20’ in ASCII environments). SQLCODE in the SQLCA is set to -304 if the host variable cannot contain the requested date format. | |
|
The value must be representable in the type specified without losing leading digits. SQLCODE in the SQLCA is set to -304 if the host variable cannot contain the data. |
Related Topics
For more information about the UDT data type, see “CREATE TRANSFORM” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.