16.20 - Java Table Operator Default Transform Behavior and Transform Off Format - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Default Transform Behavior for UDT and CDT Data Values

The input UDT and CDT data values sent to the table operator are in their default transform form. This may correspond to either the SQL transform type of the UDT or another predefined type. The UDT and CDT values are converted to their corresponding transform type by invoking the default transform function defined for the UDT or CDT. The following table shows the transform types for UDT and CDT data types passed to a table operator.

This mode is well suited for a connector-type of operator (T2T or T2M) that does import and export of data values.

Note that some UDTs and CDTs (such as XML, ST_Geometry, DATASET, and JSON) support multiple transform groups. However, for table operators, the CDT values will always be sent using the default transform type. For instance, a JSON data value will always be sent as a CLOB.

SQL UDT or Complex Data Type SQL Data Type Mapping Java Data Type
ARRAY/ VARRAY VARCHAR (the defined transform type)

java.lang.String

BSON BLOB or CLOB
  • java.sql.Blob
  • java.sql.Clob
DATASET – Avro BLOB

java.sql.Blob

DATASET – CSV CLOB

java.sql.Clob

Geospatial – MBB VARCHAR

java.lang.String

Geospatial – MBR VARCHAR

java.lang.String

Geospatial – ST_Geometry CLOB

java.sql.Clob

JSON CLOB

java.sql.Clob

Period VARCHAR

java.lang.String

UDT (Distinct) Predefined data type that the UDT is based on

Primitive Java data type

For example:
  • int
  • short
  • java.lang.String
  • java.sql.Clob
  • java.sql.Blob
UDT (Structured) Predefined data type as defined in CREATE TRANSFORM

Primitive Java data type

For example:
  • int
  • short
  • java.lang.String
  • java.sql.Clob
  • java.sql.Blob
XML CLOB

java.sql.Clob

Setting the Transforms Off Option

If a table operator needs to work on table data and be able to access and retrieve individual elements of a complex type, then the transform type is not suitable. Therefore, an option is provided that allows a table operator to request Period and Array values to be sent in a transforms off, or atomized, form.

The RuntimeContract class setFormat() method can be used to set the format attributes corresponding to PDTTransformsOff and ArrayTransformsOff. These format attributes must be set to TRUE if Period and Array values will be sent and received in the untransformed form.

You cannot enable the Transforms Off option for structured UDTs.

Transforms Off Data Values

When the Transforms Off mode is TRUE, Period values are sent as java.sql.Struct objects and Arrays are sent as java.sql.Array objects in the Input ResultSet sent to the operator. The operator can then use methods of the java.sql.Struct and java.sql.Array classes to manipulate the objects.

Similarly, the operator can construct java.sql.Struct and java.sql.Array objects for the output data values.