Example Using FNC_GetJSONResultLob - Advanced SQL Engine - Teradata Database

This example uses FNC_GetJSONResultLob to create a JSON instance from a CLOB.

SQL definition:

REPLACE FUNCTION getJSONResult (a1 CLOB AS LOCATOR)
RETURNS JSON CHARACTER SET LATIN
NO SQL
PARAMETER STYLE TD_GENERAL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!getJSONResult!getJSONResult.c!F!getJSONResult';

C function definition, getJSONResult.c:

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

void getJSONResult (LOB_LOCATOR *inLOB,
                    JSON_HANDLE *json_instance,
                    char sqlstate[6])
{
   LOB_RESULT_LOCATOR outLOB;
   BYTE buffer[buffer_size];
   LOB_CONTEXT_ID id;
   FNC_LobLength_t readlen, writelen, actualInputLength;
   int trunc_err = 0;
   int length = 0;
   charset_et charset = 0;
   int numLobs = 0;

   FNC_GetJSONInfo((*json_instance), &length, &charset, &numLobs);

   FNC_LobOpen(*inLOB, &id, 0, 0);
   actualInputLength = FNC_GetLobLength(*inLOB);
   if (actualInputLength > length)
   {
      sprintf(sqlstate, "U0101\0");
      return;
   }

   FNC_GetJSONResultLob(*json_instance,&outLOB);

   readlen=0;
   writelen=0;

   while( FNC_LobRead(id, buffer, buffer_size, &readlen) == 0 && !trunc_err )
   {
      trunc_err = FNC_LobAppend(outLOB, buffer, readlen, &writelen);
      /* check trunc_err and properly report an error
      (performed in the same way as for a standard UDF) */
   }

   FNC_LobClose(id);
   sprintf(sqlstate, "00000\0");
}

Example table, data, and query:

CREATE TABLE clobTable(id INTEGER, c CLOB);

INSERT INTO clobTable(1, '{"CompanyName":"Teradata"}');

SELECT getJSONResult(c).JSONExtractValue('$.CompanyName')

FROM clobTable;

Result:

getJSONResult(c).JSONEXTRACTVALUE('$.CompanyName')
--------------------------------------------------
Teradata