16.20 - FNC_GetStructuredResultLobAttribute - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Purpose

Returns the locator of a LOB attribute of a structured type that is defined to be the return value of a UDF or UDM, or an INOUT or OUT parameter to an external stored procedure.

For best performance, use FNC_GetStructuredResultLobAttributeByNdx instead of this routine. This routine is supported for ease of use.

Syntax

void
FNC_GetStructuredResultLobAttribute ( UDT_HANDLE          udtHandle,
                                      char               *attributePath,
                                      LOB_RESULT_LOCATOR *object )
UDT_HANDLE udtHandle
the handle to a structured UDT that is defined to be the return value of a UDF or UDM or an INOUT or OUT parameter to an external stored procedure.
char *attributePath
the dot delimited full path to the LOB attribute.

For example, consider a structured UDT called PersonUDT that has an attribute called passport that is a PassportUDT type, which in turn has a LOB attribute called photo. To get the locator of the photo attribute, the full path is “passport.photo”.

LOB_RESULT_LOCATOR *object
a pointer to the LOB locator for the LOB attribute.

Usage Notes

You can use FNC_GetStructuredResultLobAttribute to obtain the LOB locator for a distinct type attribute that represents a LOB type.

After you obtain the LOB locator for the attribute of the structured type, use the LOB access functions, such as FNC_LobOpen, to append data.

You can use FNC_GetStructuredResultLobAttribute to write data to a JSON attribute. This function 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. You must use FNC_GetStructuredResultLobAttribute if the JSON data to be written is larger than 64000 bytes. If the JSON data is equal to or less than 64000 bytes, you can still use FNC_GetStructuredResultLobAttribute to write the data; however, you may get better performance if you use FNC_SetStructuredAttribute instead.

By default, a structured UDT that an external routine returns has all of its attributes (except attributes that are themselves structured UDTs) set to null. If an external routine does not append any data to a LOB attribute of a structured type, that attribute remains null.

Setting the result indicator argument to -1 for an external routine that uses parameter style SQL discards any data that was appended to a LOB attribute. An external routine that appends data to a LOB attribute cannot reset the LOB attribute back to null if the result indicator argument is not set to -1.

A LOB attribute of a structured type that is defined to be an INOUT parameter to an external stored procedure retains the input version if no data is appended to the LOB attribute.

Restrictions

An external stored procedure that uses CLIv2 to execute SQL must wait for any outstanding CLIv2 requests to complete before calling this function.

Example Using FNC_GetStructuredResultLobAttribute

void document_t_lowerCase( UDT_HANDLE *documentUdt,
                           UDT_HANDLE *resultDocumentUdt,
                           char        sqlstate[6])
{
    LOB_RESULT_LOCATOR resultDocLoc;

    /* Get a LOB_RESULT_LOCATOR for the result doc attribute. */
    FNC_GetStructuredResultLobAttribute(*resultDocumentUdt, "doc", 
                                        &resultDocLoc);