Server to Host Assignment - Teradata Preprocessor2

Teradata® Preprocessor2 for Embedded SQL Programmer Guide

Product
Teradata Preprocessor2
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-19
dita:mapPath
whb1544831946911.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2446
lifecycle
previous
Product Category
Teradata Tools and Utilities

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.

No host GRAPHIC-type variable declaration for the C language is recognized by PP2.

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

The C preprocessor recognizes three types of character fields into which non-numeric data can be returned:
  • 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
  • The data is moved to arr with no exception conditions
  • len is set to M
M < n
  • M characters of data are moved to arr
  • len is set to M
M > n
  • n characters are moved to arr
  • len is set to n
  • SQLWARN1 in SQLCA is set to W
  • The indicator variable (if present) is set to M

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

  • M characters are moved to the variable
  • '\0' is placed in the M+1 position of the variable
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
  • n-1 characters are moved to the variable
  • position is set to '\0'
  • SQLWARN1 is set to W
  • if the indicator variable is present, it is set to M
  • SQLCODE is set to +902 (ANSI mode only)
  • SQLSTATE is set to ’01004’ (ANSI mode only)

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
  • BTET
  • COMMIT
  • 2PC
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
  • BTET
  • COMMIT
  • 2PC
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.

The following types are valid:
  • 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.

The following types are valid:
  • 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.