Host variables are assigned during execution of a data retrieving statement (such as SELECT) or by a FETCH for cursors. The INTO clause identifies the host variables to receive the data.
PP2 converts data from the server to the appropriate client format, provided the types are compatible.
Conversion Rules
- Numeric values might not be returned to character or byte strings. The only exception to this is DATE.
DATE fields can be returned to character fields (not byte) if the length of the field can hold the conversion type requested by the DATE option. See DATE(D|E|I|J|U) -d D|E|I|J|U.
- BYTE and VARBYTE strings can be returned to any valid host variable type.
The byte string is moved one byte at a time for the length of the receiving variable.
No data conversion is performed and the application is responsible for handling the returned data.
- CHAR and VARCHAR strings are returned only to CHAR, VARCHAR or character string type variables.
A CHAR host variable is defined as a one character char type (that is, char var1).
VARCHAR is defined as a structure having a two byte length field and a character array data field.
A character string is defined as an array of two or more elements of char type (that is, char var1[2]), which is terminated by a terminating null character ('\0').
- DATE values can be returned to a numeric type (DECIMAL, NUMERIC, INTEGER, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
DATE in its natural format (such as a DESCRIBE for a dynamic request) is equivalent to INTEGER.
DATE can also be returned to a CHAR, VARCHAR or character string field, provided the length of the receiving field can hold the format requested.
The character variable must be at least length 8 to receive the date in any of the allowable formats. The character string type variable must be at least length 9 (date plus the terminating null character of '\0') to receive the data.
- DECIMAL values can be returned to any numeric type (DECIMAL, NUMERIC, INTEGER, SMALLINT, BYTEINT, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
C does not provide a natural DECIMAL data type. Memory can be allocated, however, to hold the value and the SQLDA set to reflect a DECIMAL type host variable. The application then is responsible for manipulation of the data.
- BYTEINT, SMALLINT and INTEGER values can be returned to any numeric type (DECIMAL, NUMERIC, INTEGER, SMALLINT, BYTEINT, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
- FLOAT values can be returned to any numeric type (DECIMAL, NUMERIC, INTEGER, SMALLINT, BYTEINT, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
The Teradata SQL FLOAT, REAL and DOUBLE PRECISION types are equivalent to a DOUBLE PRECISION FLOAT.
PP2 recognizes both SINGLE and DOUBLE PRECISION FLOAT host variable types.
Fractional portions of the value can be lost in conversion to a type other than DOUBLE PRECISION FLOAT.
Byte String Assignment
Byte data is assigned to a host variable byte by byte. PP2 performs no conversion, leaving the application responsible for processing the value.
Value | Action Taken by Application on Data |
---|---|
Shorter than the receiving field | Filled with nulls |
Longer than the receiving field | Truncated. SQLWARN1 in the SQLCA area is set to W The indicator variable, if present, is set to the length of the returned data. |
The same length | Moved with no exception conditions |
Character Assignment
- Single character
- Varying Character
- Character String
Each type is discussed in the sections that follow.
Single-Character Data
A character host variable is defined as a single-character field with no terminating null character ('\0'). The C definition for such a field is:
char var1;
Do not use brackets when defining a single-character field; their use invalidates the variable for use in a SQL statement.
When data is returned to a single-character field, a maximum of one byte is returned (truncation occurs if the database field is larger, warning flags are set and (if present) the indicator variable is set). No '\0' is present, which is equivalent to a data type of 452 (453 if nullable), with a length of 1.
DATE values might not be returned to this type of host variable because the field is not long enough.
Varying Character Data
The C language does not have a natural varying character data type. However, PP2 allows the definition of a varying character field using the special identifier, VARCHAR.
PP2 converts this designation to a structure composed of two fields.
VARCHAR var1[n];
becomes:
struct { SQLInt16 len; char arr[n]; }var1;
SQLInt16 is a defined constant equivalent to short and n is an integer value > 0 (n reflects the maximum length of the data to be sent or received: Do not add +1 for a terminating null).
This type of field can contain characters or binary data, including '\0' terminating null characters.
Varying character fields do not contain a terminating null character ('\0').
The len field contains the length of the data returned to the arr portion. Let M be the length of the returned data.
Value | Description |
---|---|
M = n |
|
M < n |
|
M > n |
|
Varying character data has a type of 448 (449 if nullable), with a maximum length of n.
DATE values can be returned to this type of field, provided the length is sufficient to hold the format chosen at precompile time. See DATE(D|E|I|J|U) -d D|E|I|J|U.
Character String Data
A character string host variable is defined as an array of characters with a terminating null character ('\0').
The C definition for such a variable is:
char var1[n];
To define a character string variable, n must be a positive integer, value > 1.
At most, n-1 bytes of data are returned to the host variable.
One position is always reserved for the '\0' terminating null character. Let M be the length of the returned data.
Value | TRANSACT Mode | Description |
---|---|---|
M <= n-1 | BTET COMMIT 2PC |
|
ANSI | The array is padded with blanks and ‘\0’ is placed in the M+1 position of the variable. | |
M > n-1 | any valid choice |
|
This variable has a type of 460 (461 if nullable), with a maximum length of n-1.
Actual length is determined by the position of the terminating null character ('\0').
DATE values can be returned to this type of field, providing the format chosen at precompile time fits in n-1 bytes. See DATE(D|E|I|J|U) -d D|E|I|J|U.
Character String Padding
C strings are padded differently in the Teradata-compatible and ANSI-compatible environments, as shown in the next table.
TRANSACT Mode | Padding Character |
---|---|
ANSI | blanks |
Teradata
|
nulls |
For example, consider the following comparison in which the EBCDIC string ABCDE is fetched into a host variable defined as char[7].
TRANSACT Mode | Host Variable Data |
---|---|
ANSI |
X’C1C2C3C4C54000’ |
Teradata
|
X’C1C2C3C4C50000’ |
The ANSI string is padded with blanks, while the non-ANSI string is padded with nulls. The process is similar for ASCII strings.
Character Truncation
Character truncation is handled identically under all compatibility modes. For example, consider the EBCDIC string ABCDE is fetched into a host variable defined as char[4].
The host variable contains X’C1C2C300’, which is the three leftmost characters of the original string plus a terminating null. The process is similar for ASCII strings.
If TRANSACT is set to ANSI, truncation of any nonblank and nonzero character produces a warning SQLCODE (+902) and SQLSTATE (01004).
Numeric Value Assignment
Numeric values are converted as appropriate for the receiving field, provided the value can assume the requested format without losing leading digits.
- DECIMAL
- NUMERIC
- FLOAT
- REAL
- DOUBLE PRECISION
- BYTEINT
- SMALLINT
- INTEGER
The SQLCODE field in the SQLCA is set to -304 if the host variable cannot contain the returned data.
Date Assignment
Date values can be returned into any valid numeric host variable following the same rules as numeric assignment, except when DATEFORM=ANSIDATE is set.
- DECIMAL
- NUMERIC
- FLOAT
- REAL
- DOUBLE PRECISION
- INTEGER
Date values can also be returned into a character string or varying character variable.
The host variable length depends upon the DATE option set or defaulted. See DATE(D|E|I|J|U) -d D|E|I|J|U.
If a database format is requested or defaulted, the receiving variable must be at least 8 bytes long. Surplus bytes are set to blanks. All other formats require at least 10 bytes. Bytes in excess of 10 are set to blanks.
The SQLCODE in the SQLCA is set to -304 if the receiving field is too short.
For ANSI DATE format, the receiving character variable must be at least 10 bytes long. Bytes in excess of 10 are set to blanks.