15.10 - Server to Host Assignment - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

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 that are to receive the data.

PP2 converts data from the server format to the appropriate format, provided they are compatible types.

Conversion Rules

  • Numeric values are usually not 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” on page 71.

  • BYTE and VARBYTE strings can be returned to any valid host variable types.
  • 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 or VARCHAR type variables.
  • DATE values can be returned to any numeric type (DECIMAL, NUMERIC, ZONED DECIMAL, 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 or VARCHAR field, provided the length of the receiving field can hold the format requested.

    The character variable must be at least length 8 or 10, depending on the DATE PP2 option, to receive the date in any of the allowable formats.

  • DECIMAL values can be returned to any numeric type (DECIMAL, NUMERIC, ZONED DECIMAL, INTEGER, SMALLINT, BYTEINT, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
  • BYTEINT, SMALLINT and INTEGER values can be returned to any numeric type (DECIMAL, NUMERIC, ZONED DECIMAL, 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, ZONED DECIMAL, INTEGER, SMALLINT, BYTEINT, FLOAT, REAL, DOUBLE PRECISION), as long as the value is representable in the receiving type.
  • The Teradata Database FLOAT type is equivalent to a DOUBLE PRECISION FLOAT. PP2 recognizes both SINGLE and DOUBLE PRECISION FLOAT host variable types, but fractional portions of the value might be lost in conversion to a type other than DOUBLE PRECISION FLOAT.

  • GRAPHIC and VARGRAPHIC strings can only be returned to GRAPHIC or VARGRAPHIC type variables.
  • Byte String Assignment

    Byte data is assigned to the host variable byte by byte. PP2 does not perform any conversion, leaving the application responsible for processing the value.

     

    Value

    Description

    Shorter than the receiving field

    The field is NULL (X’00’) filled.

    Longer than the receiving field

  • The data is truncated.
  • SQLWARN1 in the SQLCA is set to W.
  • The indicator variable, if present, is set to the length of the returned data.
  • The same length as the field

    The data is moved with no exception conditions.

    Character String Assignment

    Character string data is assigned to a host variable one character at a time.

     

    Value

    Description

    Shorter than the receiving field

    The field is blank filled.

    Longer than the receiving field

  • The data is truncated.
  • SQLWARN1 in the SQLCA is set to W.
  • The indicator variable, if present, is set to the length of the returned data.
  • SQLCODE is set to +902 (ANSI mode only).
  • SQLSTATE is set to ‘01004’ (ANSI mode only).
  • The same length as the field

    The data is moved with no exception conditions.

    If TRANSACT is set for 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 (DECIMAL, NUMERIC, ZONED DECIMAL, FLOAT, REAL, DOUBLE PRECISION, BYTEINT, SMALLINT, INTEGER), provided the value can assume the requested format without losing leading digits.

    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 (DECIMAL, NUMERIC, ZONED DECIMAL, FLOAT, REAL, DOUBLE PRECISION, INTEGER), following the same rules as a numeric assignment except when DATEFORM=ANSIDATE is set. Date values also can be returned into a character string variable.

    The host variable length depends on the DATE option set or defaulted.

    For details, see Chapter 2: “Connecting to the Database and Invoking PP2.”

     

    Requested Format

    Minimum Requirement for Receiving Variable

    Teradata Database

    8 bytes.

    Surplus bytes are set to blanks.

    Anything else

    10 bytes

    Surplus bytes are set to blanks.

    The SQLCODE in the SQLCA is set to -304 if the receiving field is too short.

    For ANSIDATE format, the receiving character variable must be at least 10 bytes long. Bytes in excess of 10 are set to blanks.