16.20 - FNC_SetStructuredAttribute - 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

Set the non-LOB attribute of a structured type that is defined to be a return value to a UDF, UDM, or external stored procedure.

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

Syntax

void
FNC_SetStructuredAttribute ( UDT_HANDLE  udtHandle,
                             char       *attributePath,
                             void       *newValue,
                             int         nullIndicator,
                             int         length )
UDT_HANDLE udtHandle
the handle to a structured UDT that is defined to be the return value to a UDF or UDM, or an INOUT or OUT parameter to an external stored procedure.
char *attributePath
the dot delimited full path to the attribute.
For example, consider a structured UDT called PersonUDT that has an attribute called address that is an AddressUDT type, which in turn has an attribute called zipcode. To set the zipcode value, the full path is “address.zipcode”.
If the full path to the specified attribute includes a preceding null attribute, FNC_SetStructuredAttribute returns normally without setting the value of the specified attribute.
void *newValue
a pointer to a buffer containing the new value for the attribute.
If the specified attribute is a structured UDT, you can use the nullIndicator argument to set the attribute to null, but FNC_SetStructuredAttribute ignores the newValue and length arguments.
int * nullIndicator
whether to set the attribute to null.
If the value of nullIndicator is...
  • -1, then FNC_SetStructuredAttribute sets the attribute to null.

    The newValue argument is ignored.

  • 0, then FNC_SetStructuredAttribute sets the attribute to the value pointed to by newValue.
int length
the size in bytes of the newValue buffer.
If the specified attribute is a structured UDT, you can use the nullIndicator argument to set the attribute to null, but FNC_SetStructuredAttribute ignores the newValue and length arguments.

Usage Notes

You can use FNC_SetStructuredAttribute to write a string representation of a JSON document to a JSON attribute only if the JSON data is equal to or less than 64000 bytes. If the JSON data is larger than 64000 bytes, you must use FNC_GetStructuredResultLobAttribute instead.

To define the buffer pointed to by newValue, use the C data type that maps to the underlying type of the attribute. For example, if the distinct type represents an SQL INTEGER data type, you can define the buffer like this:

INTEGER value;
value = 2048;

For information on the C data types that you can use, see C Data Types.

If the underlying type of the attribute is a character string, and the newValue character string is shorter than the size defined for the type, FNC_SetStructuredAttribute fills to the right with spaces.

Because character data types allow embedded null characters, do not include null termination characters in the value you pass in for the length argument.

To guarantee that the value you pass in for the length argument matches the length of the data type in Teradata Database, use these macros defined in the sqltypes_td.h header file.

Macro Description
SIZEOF_CHARACTER_LATIN(len)
SIZEOF_CHARACTER_KANJISJIS(len)
SIZEOF_CHARACTER_KANJI1(len)
SIZEOF_CHARACTER_UNICODE(len)
Returns the length in bytes of a CHARACTER data type of len characters. For example, the following returns a length of 6 (3 * 2 = 6):
SIZEOF_CHARACTER_UNICODE(3)
SIZEOF_VARCHAR_LATIN(len)
SIZEOF_VARCHAR_KANJISJIS(len)
SIZEOF_VARCHAR_KANJI1(len)
SIZEOF_VARCHAR_UNICODE(len)
Returns the length in bytes of a VARCHAR data type of len characters. For example, the following returns a length of 6 (3 * 2 = 6):
SIZEOF_VARCHAR_UNICODE(3)
SIZEOF_BYTE(len)
SIZEOF_VARBYTE(len)
Returns the length in bytes of the specified BYTE or VARBYTE data type, where len specifies the number of values.
SIZEOF_GRAPHIC(len)
SIZEOF_VARGRAPHIC(len)
Returns the length in bytes of the specified CHARACTER(n) CHARACTER SET GRAPHIC or VARCHAR(n) CHARACTER SET GRAPHIC data type, where len specifies the number of values.
SIZEOF_BYTEINT
SIZEOF_SMALLINT
SIZEOF_INTEGER
SIZEOF_BIGINT
SIZEOF_REAL
SIZEOF_DOUBLE_PRECISION
SIZEOF_FLOAT
SIZEOF_DECIMAL1
SIZEOF_DECIMAL2
SIZEOF_DECIMAL4
SIZEOF_DECIMAL8
SIZEOF_DECIMAL16
SIZEOF_NUMERIC1
SIZEOF_NUMERIC2
SIZEOF_NUMERIC4
SIZEOF_NUMERIC8
SIZEOF_NUMERIC16
SIZEOF_NUMBER
Returns the length in bytes of the specified numeric data type.

For NUMBER, the length returned is 4 + 2 + 17 = 23 bytes since Teradata Database allocates max length (17 bytes) for the mantissa.

SIZEOF_DATE
SIZEOF_ANSI_Time
SIZEOF_ANSI_Time_WZone
SIZEOF_TimeStamp
SIZEOF_TimeStamp_WZone
Returns the length in bytes of the specified DateTime type.
SIZEOF_INTERVAL_YEAR
SIZEOF_IntrvlYtoM
SIZEOF_INTERVAL_MONTH
SIZEOF_INTERVAL_DAY
SIZEOF_IntrvlDtoH
SIZEOF_IntrvlDtoM
SIZEOF_IntrvlDtoS
SIZEOF_HOUR
SIZEOF_IntrvlHtoM
SIZEOF_IntrvlHtoS
SIZEOF_MINUTE
SIZEOF_IntrvlMtoS
SIZEOF_IntrvlSec
Returns the length in bytes of the specified Interval type.

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_SetStructuredAttribute

void setX( UDT_HANDLE *pointUdt,
           INTEGER    *val,
           UDT_HANDLE *resultPoint,
           char        sqlstate[6])
{
    INTEGER x;
    INTEGER newval;
    int nullIndicator;
    int length;

    /* Set the x attribute of the result point. */
    nullIndicator = 0;
    newval = *val;
    FNC_SetStructuredAttribute(*resultPoint, "x", &newval, 
                                nullIndicator, SIZEOF_INTEGER);    ...
}