16.20 - Usage Notes - 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)

JSON Data Type

You can use FNC_SetInternalValue to set a character or binary JSON value. When setting the string representation of a JSON type instance, the string is either UNICODE or LATIN text, depending on how the instance was defined.

Before calling FNC_SetInternalValue, the external routine must allocate the buffer pointed to by newValue. newValue must point to a valid memory buffer that contains the new JSON data to be set. The length of the data cannot exceed the maximum inline length reported by FNC_GetExtendedJSONInfo.

Use FNC_SetInternalValue only when the JSON data will not be stored as a LOB. If FNC_GetJSONInfo or FNC_GetExtendedJSONInfo returns numLobs = 0, you can use FNC_SetInternalValue; otherwise, you should use FNC_GetJSONResultLob instead.

DATASET Data Type

Before calling FNC_SetInternalValue, the external routine must allocate the buffer pointed to by newValue. newValue must point to a valid memory buffer that contains the new DATASET data to be set. The length of the data cannot exceed the maximum inline length reported by FNC_GetDatasetInfo.

Use FNC_SetInternalValue only when the DATASET data will not be stored as a LOB. If FNC_GetDatasetInfo returns dataLob = 0, you can use FNC_SetInternalValue; otherwise, you should use FNC_GetDatasetResultLob instead.

For Avro instances, this routine allows a user to write the UTF-8 encoded schema, null-terminated, followed by the binary-encoded Avro value to a buffer and pass it to the database. This is equivalent to the transform format. Failure to write the data in this format results in an error.

A CSV value passed to this function cannot include any optional schema.

Period Types

For Period types, the format of the return data depends on the element type of the Period data type. If the Period type is:

  • PERIOD(DATE), then the format of the newValue data is two consecutive DATE values.
  • PERIOD(TIME), then the format of the newValue data is two consecutive TIME values.
  • PERIOD(TIME WITH TIME ZONE), then the format of the newValue data is two consecutive TIME WITH TIME ZONE values.
  • PERIOD(TIMESTAMP), then the format of the newValue data is two consecutive TIMESTAMP values.
  • PERIOD(TIMESTAMP WITH TIME ZONE), then the format of the newValue data is two consecutive TIMESTAMP WITH TIME ZONE values.

The size of the buffer pointed to by newValue must be large enough to hold two consecutive values of the element type of the Period data type. For example, if the Period type is PERIOD(DATE), the buffer must be large enough to hold two consecutive DATE values.

To guarantee that the value you pass in for the length parameter matches the length of the data type in Teradata Database, use the following macros defined in the sqltypes_td.h header file.

Macro Description
SIZEOF_DATE
SIZEOF_ANSI_Time
SIZEOF_ANSI_Time_WZone
SIZEOF_TimeStamp
SIZEOF_TimeStamp_WZone
Returns the length in bytes of the specified DateTime type.