16.20 - Example Using FNC_GetJSONInfo - 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)

SQL definition:

REPLACE FUNCTION getJSONInfo (a1 TD_ANYTYPE)
RETURNS VARCHAR(100)
NO SQL
PARAMETER STYLE TD_GENERAL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!getJSONInfo!getJSONInfo.c!F!getJSONInfo';

C function definition, getJSONInfo.c:

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#include <stdio.h>
#define buffer_size 64000

void getJSONInfo( JSON_HANDLE *json_instance,
                  VARCHAR_LATIN *result,
                  char sqlstate[6])
{
   int length = 0;
   charset_et charset = 0;
   int numLobs = 0;

   /* Get the info of the JSON instance. */
   FNC_GetJSONInfo((*json_instance), &length, &charset, &numLobs);
   sprintf(result, "Length: %d, CharSet: %d, NumLobs: %d\0", length, charset, numLobs);
}

Example table, data, and query:

CREATE TABLE jsonTable(id INTEGER, j JSON(100) CHARACTER SET LATIN);

INSERT INTO jsonTable(1, '{"name":"Cameron"}');

SELECT getJSONInfo(j) FROM jsonTable;

Result:

getJSONInfo(j)
-----------------------------------
Length: 100, CharSet: 1, NumLobs: 0