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