Usage Notes - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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, 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.