When developing UDFs, UDMs, or external stored procedures that are defined with JSON type parameters or return values, you can use the following JSON type interface functions to access or set the values of the JSON type parameters, or get information about a JSON type instance.
| FNC Library Routine | Description |
|---|---|
| FNC_GetInternalValue | Retrieves the string representation of a JSON type instance that is used as an input argument to an external routine. The string returned is UNICODE or LATIN text depending on how the instance was defined. You can use this function only when the JSON data is not stored as a LOB. |
| FNC_GetJSONInfo | Retrieves the maximum length and character set of a JSON type instance that is used as an input argument to an external routine. The function also indicates whether or not the JSON data is stored as a LOB. |
| FNC_GetJSONInputLob | 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. |
| FNC_GetJSONResultLob | Returns a LOB_RESULT_LOCATOR to a LOB associated with a JSON instance. You can use this locator with LOB FNC routines to write data to the JSON instance defined as a return value of an external routine. |
| FNC_SetInternalValue | Sets the string representation of a JSON type instance that is defined as an output parameter or return value to an external routine. The string is UNICODE or LATIN text depending on how the instance was defined. You can use this function only when the JSON data will not be stored as a LOB. |
Use the JSON_HANDLE C data type to pass a JSON type instance as an argument to an external routine. Similarly, use JSON_HANDLE to return a JSON type result from an external routine. JSON_HANDLE is defined in sqltypes_td.h as: typedef int JSON_HANDLE;
You can specify the JSON data type as an attribute of a structured UDT. When passing a structured UDT that includes a JSON attribute to or from an external routine, you can use the following interface functions to access or set the value of the JSON attribute, or get information about a JSON attribute.
| FNC Library Routine | Description |
|---|---|
| FNC_GetStructuredAttribute | Returns a string representation of a JSON attribute. You can use this function only when the data of the JSON attribute is not stored as a LOB. |
| FNC_GetStructuredAttributeInfo_EON | Retrieves the following information about the JSON attribute:
|
| FNC_GetStructuredInputLobAttribute | Returns a LOB_LOCATOR which represents the LOB where the data of a JSON attribute is stored. You can use this LOB_LOCATOR with the LOB FNC routines to read the data from the JSON attribute. |
| FNC_GetStructuredResultLobAttribute | Returns a LOB_RESULT_LOCATOR which represents a LOB where the data of a JSON attribute may be stored. You can use this LOB_RESULT_LOCATOR with LOB FNC routines to write the data to the JSON attribute. |
| FNC_SetStructuredAttribute | Sets the string representation of a JSON document to a JSON attribute. You can use this function only when the JSON data will not be stored as a LOB. |
For details about the JSON type interface functions, see SQL External Routine Programming, B035-1147.