Inserting and Retrieving UDTs with Client Products - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

Inserting and Retrieving UDTs with Client Products

A UDT can only exist on the Teradata Database. Each UDT has an associated “from‑sql routine” and “to‑sql routine”.

  • The “to‑sql routine” constructs a UDT value from a pre‑defined type value. The “to‑sql routine” is automatically invoked when inserting values from a client system into a UDT on the Teradata Database server.
  • The “from‑sql routine” generates a pre‑defined type value from a UDT. The “from‑sql routine” is automatically invoked when a UDT is retrieved from the Teradata Database server to a client system.
  • External Types

    The “from‑sql routine” and the “to‑sql routine” create a mapping between a UDT and a pre‑defined type. This pre‑defined type is called the external type of a UDT. A client application only deals with the external type; it does not deal with UDT value directly.

    External Type Example

    For example, if the following conditions exist:

  • UDT named FULLNAME exists
  • The external type associated with FULLNAME is VARCHAR(46)
  • Then, during an retrieve of FULLNAME values, the Teradata Database server converts the values from FULLNAME values to VARCHAR(46) values by invoking the “from‑sql routine” associated with the FULLNAME UDT.

    Note: The client must expect to receive the data in the same format as it receives VARCHAR(46) values.

    Similarly, when values are provided by the client for insert into a FULLNAME UDT, the client must provide values in the same way it would provide values for a VARCHAR(46) field. The Teradata Database server will convert the values from VARCHAR(46) to FULLNAME values using the “to‑sql routine” associated with the FULLNAME UDT.