16.20 - FNC_GetJSONInputLob - 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

Returns a LOB_LOCATOR for a JSON instance which has its data stored as a LOB. You can use this LOB_LOCATOR with LOB FNC routines to read the data from the JSON instance.

Syntax

void
FNC_GetJSONInputLob ( JSON_HANDLE   jsonHandle,
                      LOB_LOCATOR   *object )
JSON_HANDLE jsonHandle
A handle to a JSON type instance that is defined to be an input argument to an external routine.

JSON_HANDLE is defined in sqltypes_td.h as typedef int JSON_HANDLE;

LOB_LOCATOR *object
A pointer to a LOB_LOCATOR that you can use with the LOB FNC routines to read the LOB data of a JSON instance.

Usage Notes

JSON data for any particular instance may be stored in the base table row or in a LOB subtable. Use FNC_GetJSONInputLob only when the JSON data is stored as a LOB. If FNC_GetJSONInfo or FNC_GetExtendedJSONInfo returns numLobs > 0, you can use FNC_GetJSONInputLob; otherwise, you should use FNC_GetInternalValue instead.

You can use FNC_GetJSONInputLob to retrieve both character and binary JSON data.

Example Using FNC_GetJSONInputLob

This example uses FNC_GetJSONInputLob to retrieve the string representation of a JSON instance, and then search it for a particular name-value pair.

SQL definition:

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

C function definition, getJSONInput.c

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#include <stdio.h>
#define buffer_size 64000
void getJSONInput (JSON_HANDLE *json_instance,
                   VARCHAR_LATIN *result,
                   char sqlstate[6])
{
   char *jsonBuf;
   char *searchString1, *searchString2;
   int maxLength = 0;
   charset_et charset = 0;
   int actualLength = 0;
   int strLength = 0;
   int numLobs = 0;
   /* Get the length of the JSON instance. */
   FNC_GetJSONInfo((*json_instance),&maxLength,&charset,&numLobs);
   if (numLobs != 0)
   {
      LOB_LOCATOR inLOB;
      LOB_CONTEXT_ID id;
      FNC_LobLength_t readlen, actualInputLength;
      int trunc_err = 0;
      BYTE *bufPtr = 0;
      jsonBuf = (char*)FNC_malloc(maxLength);
      FNC_GetJSONInputLob(*json_instance,&inLOB);
      readlen=0;
      actualInputLength = 0;
      bufPtr = jsonBuf;
      FNC_LobOpen(inLOB, &id, 0, 0);
      while( FNC_LobRead(id, bufPtr, buffer_size, &readlen) == 0 && !trunc_err )
      {
         bufPtr += readlen;
         actualInputLength += readlen;
         if (actualInputLength >= maxLength)
         {
            trunc_err = 1;
            actualInputLength = maxLength;
         }
            /* check trunc_err and properly report an error
               (performed in the same way as for a standard UDF) */
      }
      FNC_LobClose(id);
      searchString1 = strstr(jsonBuf, "name");
      searchString2 = strchr(searchString1, ':');
      searchString1 = strchr(searchString2, '}');
      strLength = searchString1 - searchString2;
      strncpy(result, (searchString2+sizeof(char)), strLength-sizeof(char));
      FNC_free(jsonBuf);
   }
} 

Example table, data, and query:

CREATE TABLE jsonTable(id INTEGER, j JSON(100000));
INSERT INTO jsonTable(1, <data large enough to be stored as LOB>);
SELECT getJSONInput(j) FROM jsonTable;

Result:

"Cameron"