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

FNC_GetInternalValue retrieves both character and binary JSON values. For text-based values, the routine returns the string representation of the JSON instance, either as UNICODE or LATIN text, depending on how the instance was defined.

Before calling FNC_GetInternalValue, the external routine must allocate the buffer pointed to by returnValue. You can call FNC_GetJSONInfo or FNC_GetExtendedJSONInfo to determine the appropriate buffer size, or you can allocate a buffer with the maximum possible length of 64000 bytes.

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

DATASET Data Type

Before calling FNC_GetInternalValue, the external routine must allocate the buffer pointed to by returnValue. You can call FNC_GetDatasetInfo to determine the appropriate buffer size.

Use FNC_GetInternalValue only when the DATASET data is not stored as a LOB. If FNC_GetDatasetInfo returns dataLob = 0, you can use FNC_GetInternalValue; otherwise, you should use FNC_GetDatasetInputLob instead.

For Avro instances, this routine allows a user to access a buffer that contains the UTF-8 encoded schema, null-terminated, followed by the binary-encoded Avro value. This is equivalent to the transform format.

When retrieving a CSV value, the value will not include any optional schema.

Period Types

For Period types, the size of the buffer depends on the element type of the Period data type.

IF the period type is … THEN the buffer must be large enough to hold …
PERIOD(DATE) two DATE values.
PERIOD(TIME) two TIME values.
PERIOD(TIME WITH TIME ZONE) two TIME WITH TIME ZONE values.
PERIOD(TIMESTAMP) two TIMESTAMP values.
PERIOD(TIMESTAMP WITH TIME ZONE) two TIMESTAMP WITH TIME ZONE values.

To guarantee that the value you pass in for the bufSize argument 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.