16.20 - FNC_GetExtendedJSONInfo - 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)

Purpose

Retrieves the maximum length, inline length, character set, and storage format of a JSON type instance that is used as an input or output parameter in an external routine. The function also indicates whether or not the JSON data is stored as a LOB. This function is similar to FNC_GetJSONInfo with additional inline length and storage format information being returned.

Syntax

void
FNC_GetExtendedJSONInfo ( JSON_HANDLE       jsonHandle,
                          int             *inlineLength,
                          int             *maxLength,
                          charset_et      *charSet,
                          int             *numLobs,
                          json_storage_et *storageFmt);
JSON_HANDLE jsonHandle
A handle to a JSON type instance that is defined to be a parameter to an external routine.
int *inlineLength
Return parameter that specifies the maximum possible size of the JSON instance that can be stored in a row. The size is specified in bytes.
int *maxLength
Return parameter that specifies the maximum possible length of the JSON instance in bytes.
charset_et *charSet
Return parameter that specifies the character set of the JSON text, either LATIN_CT or UNICODE_CT as defined in sqltypes_td.h. If this is a binary JSON instance, the character set is specified as UNDEF_CT.
int *numLobs
Return parameter that specifies the number of LOBs used to store the JSON data. Valid values are as follows:
  • 0, which indicates that the data is not stored as a LOB
  • 1, which indicates that the data is stored as a LOB
json_storage_et *storageFmt
Return parameter that specifies the storage format for the JSON instance. Valid values are defined in sqltypes_td.h as follows:
typedef enum json_storage_en
{
   JSON_INVALID_EN=-1,
   JSON_TEXT_EN=0,
   JSON_BSON_EN=1,
   JSON_UBJSON_EN=2,
   DATASET_AVRO_EN=3
} json_storage_en;

Example: FNC_GetExtendedJSONInfo

/*
CREATE FUNCTION MyJSONUDF( a1 JSON(100) )
RETURNS VARCHAR(100)
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!MyJSONUDF!MyJSONUDF.c!F!MyJSONUDF';
*/
void MyJSONUDF( JSON_HANDLE   *json_instance,
                VARCHAR_LATIN *result,
                char           sqlstate[6])
{
   int maxLength = 0;
   int inlineLength = 0;
   charset_et charset = 0;
   int numLobs = 0;
   json_storage_et storageFmt = JSON_INVALID_EN;

/* Get the info of the JSON instance. */

   FNC_GetExtendedJSONInfo((*json_instance),&inlineLength, &maxLength, &charset, &numLobs, &storageFmt);
   sprintf(result, "Inline Length: %d, Max Length: %d, CharSet: %d, NumLobs: %d, Storage Format: %d", inlineLength, maxLength, charset, numLobs, storageFmt);
   ...
}

Sample output:

> Inline Length: 100, Max Length: 100, CharSet: 1, NumLobs: 0, Storage Format: 0