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 )
Syntax Elements
- 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;
- 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, use FNC_GetJSONInputLob. Otherwise, use FNC_GetInternalValue.
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 search the string 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"