UDT with JSON Type Attribute Example | JSON Data Type | Teradata Vantage - Example: Creating a Structured UDT with a JSON Type Attribute - Advanced SQL Engine - Teradata Database

Create a Structured UDT with a JSON Attribute

The following statement creates a structured UDT named judt with a JSON attribute named Att1. The maximum length of the JSON attribute is 100000 characters, and the character set of the JSON attribute is UNICODE.

CREATE TYPE judt AS (Att1 JSON(100000) CHARACTER SET UNICODE) NOT FINAL
CONSTRUCTOR METHOD judt (p1 JSON(100000) CHARACTER SET UNICODE)
RETURNS judt
SELF AS RESULT
SPECIFIC judt_cstr
LANGUAGE C
PARAMETER STYLE TD_GENERAL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
NO SQL;

Create the Constructor Method for the UDT

The following statement creates the constructor method used to initialize an instance of the judt UDT.

CREATE CONSTRUCTOR METHOD judt (p1 JSON(100000) CHARACTER SET UNICODE)
FOR judt
EXTERNAL NAME 'CS!judt_cstr!judt_constructor.c!F!judt_cstr';

Create the Transform Functionality for the UDT

The following statements create the tosql and fromsql transform routines.

REPLACE FUNCTION SYSUDTLIB.judt_fromsql
(judt)
RETURNS CLOB AS LOCATOR CHARACTER SET UNICODE
NO SQL
CALLED ON NULL INPUT
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!judt_fromsql!judt_fromsql.c!F!judt_fromsql';

CREATE FUNCTION SYSUDTLIB.judt_tosql
(CLOB AS LOCATOR CHARACTER SET UNICODE)
RETURNS judt
NO SQL
PARAMETER STYLE TD_GENERAL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!judt_tosql!judt_tosql.c!F!judt_tosql';

The following statement associates the tosql and fromsql transform routines with the judt UDT.

CREATE TRANSFORM FOR judt
judt_io (TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.judt_tosql,
FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.judt_fromsql);

Create the Ordering Functionality for the UDT

The following statements create a map ordering routine used to compare the judt values.

CREATE FUNCTION SYSUDTLIB.judt_order
(p1 judt)
RETURNS INTEGER
NO SQL
PARAMETER STYLE SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!judt_order!judt_order.c!F!judt_order';

CREATE ORDERING FOR judt
ORDER FULL BY MAP WITH FUNCTION SYSUDTLIB.judt_order;

Create the Casting Functionality for the UDT

The following statements define the casting behavior to and from judt UDT and CLOB.

CREATE CAST (judt AS CLOB CHARACTER SET UNICODE)
WITH FUNCTION judt_fromsql(judt) AS ASSIGNMENT;

CREATE CAST (CLOB CHARACTER SET UNICODE AS judt)
WITH FUNCTION judt_tosql AS ASSIGNMENT;

judt_constructor.c

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

void judt_cstr( UDT_HANDLE    *inUdt,
                JSON_HANDLE   *file1,
                UDT_HANDLE    *resultUdt,
                char           sqlstate[6])
{
   char trunc_err[6] = "25001";
   int actualInputLength = 0;
   BYTE input1[buffer_size] = {0};
   int inputMaxLength = 0;
   charset_et inputCharSet = 0;
   int inNumLobs= 0;
	
   FNC_GetJSONInfo(*file1,&inputMaxLength,&inputCharSet,&inNumLobs);
   if (inNumLobs == 0)
   {
      FNC_GetInternalValue(*file1,input1,buffer_size, &actualInputLength);
      FNC_SetStructuredAttribute(*resultUdt, "Att1", input1, 0, actualInputLength);
   }
   else
   {
      LOB_LOCATOR inLOB;
      LOB_RESULT_LOCATOR outLob;
      LOB_CONTEXT_ID id;
      FNC_LobLength_t readlen, writelen;
      int trunc_err = 0;

      FNC_GetJSONInputLob(*file1,&inLOB);		
      FNC_GetStructuredResultLobAttribute(*resultUdt, "Att1", &outLob);
		
      FNC_LobOpen(inLOB, &id, 0, 0);
      actualInputLength = FNC_GetLobLength(inLOB);
      while(FNC_LobRead(id, input1, buffer_size, &readlen) == 0 
         && !trunc_err )
      {
         trunc_err = FNC_LobAppend(outLob, input1, readlen, &writelen);
      }
   }
}

judt_fromsql.c

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

void judt_fromsql(UDT_HANDLE           *udt,
                  LOB_RESULT_LOCATOR   *result,
                  int                  *inNull,
                  int                  *outNull,
                  char                  sqlstate[6])
{
   int nullIndicator,length;
   BYTE temp[buffer_size];
	
   if (*inNull != -1)
   {
      FNC_LobLength_t readlen, writelen;
      attribute_info_t attrInfo;
      FNC_GetStructuredAttributeInfo(*udt,0,sizeof(attrInfo), &attrInfo);
      if (attrInfo.lob_length == 0)
      {
         FNC_GetStructuredAttribute(*udt, "Att1", temp, buffer_size, &nullIndicator, &length);
         readlen = length;
         FNC_LobAppend(*result, temp, readlen, &writelen);
      }
      else
      {
         LOB_LOCATOR inLob;
         LOB_CONTEXT_ID id;
         int trunc_err = 0;
         int remBufSize = buffer_size;
         BYTE *input1Ptr = temp;
         readlen = 0;
         FNC_GetStructuredInputLobAttribute(*udt, "Att1", &nullIndicator, &inLob);
         FNC_LobOpen(inLob, &id, 0, 0);
         length = FNC_GetLobLength(inLob);
			
         while(FNC_LobRead(id, temp, buffer_size, &readlen) == 0 && !trunc_err )
         {
            trunc_err = FNC_LobAppend(*result, temp, readlen, &writelen);
         }
			
         FNC_LobClose(id);
      }
      *outNull = 0;
   }
   else *outNull = -1;
}

judt_tosql.c

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void judt_tosql (LOB_LOCATOR   *p1, 
                 UDT_HANDLE    *result,
                 char           sqlstate[6])
{
   /* Using the LOB FNC routines, read from 'p1' and load the data 
into the JSON attribute, depending on its length. See judt_cstr() for 
an example of loading the JSON attribute. */
}

judt_order.c

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#define buffer_size 512

void judt_order (
   UDT_HANDLE   *UDT,
   INTEGER      *result,
   int          *indicator_udt,
   int          *indicator_result,
   char          sqlstate[6],
   SQL_TEXT      extname[129],
   SQL_TEXT      specific_name[129],
   SQL_TEXT      error_message[257])

{
   /* Read out as much data as necessary, using either 
FNC_GetStructuredAttribute or FNC_GetStructuredInputLobAttribute + LOB 
FNC routines, following the example in judt_fromsql. Then use this data 
to make the determination about the value of this instance in terms of 
ordering. */
}

Examples: Using the judt Type

The following shows uses of the newly created judt type.

CREATE TABLE judtTable(id INTEGER, j1 judt);
INSERT INTO judtTable(1, NEW judt(NEW JSON('{"name":"Cameron"}', UNICODE))); 
INSERT INTO judtTable(2, NEW judt('{"name":"Melissa"}'));

SELECT * FROM judtTable ORDER BY 1;

Result:

         id j1
----------- ----------------------------------------------------------
          1 {"name":"Cameron"}
          2 {"name":"Melissa"}