Example: Creating a Structured UDT with a JSON Type Attribute

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

This example shows how to create a structured user-defined type (UDT) which has a JSON type attribute. The routines in this example are created in the SYSUDTLIB database. Therefore, the user must have the UDTMETHOD privilege on the SYSUDTLIB database.

SQL Definition

This section shows the SQL DDL statements necessary to create the structured UDT.

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;

C Source Files

This section shows the C code for the methods and functions created in the previous section. This is just sample code so there is no meaningful logic in the tosql or Ordering functions. However, based on the examples for the Constructor and fromsql routines, you can enhance the previous routines to perform the necessary functions.

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"}