Usage Notes - Advanced SQL Engine - Teradata Database

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.

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

SIZEOF_DATE
SIZEOF_ANSI_Time
SIZEOF_ANSI_Time_WZone
SIZEOF_TimeStamp
SIZEOF_TimeStamp_WZone