17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

This example is based on the following n-D ARRAY definition:

/*Oracle-compatible and Teradata syntax respectively: */
CREATE TYPE myArray AS VARRAY(1:20)(1:20) OF integer;
CREATE TYPE myArray AS integer ARRAY[1:20][1:20];

/*This function returns a range of element values */
void getMultiElement ( ARRAY_HANDLE  *ary_handle,
                       void          *result,
                       char           sqlstate[6])
    long length;
    long bufferSize = sizeof_integer * 4;
    int *resultBuf;
    NullBitVecType    *NullBitVector;
    array_info_t arrayInfo;
    int totalElements;
    long nullVecBufSize;
    bounds_t *arrayRange;
    bounds_t arrayScope[FNC_ARRAYMAXDIMENSIONS];

    /* Call FNC_GetArrayTypeInfo first to find out the number of */
    /* elements in the array. */
    nullVecBufSize = arrayInfo.totalNumElements;

    resultBuf = (int*)FNC_malloc(bufferSize);

    arrayRange =

    /* Allocate a new NullBitVector to pass to FNC_GetArrayElements */
    NullBitVector = (NullBitVecType*)FNC_malloc(nullVecBufSize);

    /* Set all members of NullBitVector to 0 */
    memset(NullBitVector, 0, nullVecBufSize);

    /* Set values of arrayRange to correspond to the range [1:2][2:3] */
    arrayRange[0].lowerBound = 1;
    arrayRange[0].upperBound = 2;
    arrayRange[1].lowerBound = 2;
    arrayRange[1].upperBound = 3;

    /* Get elements within the range [1:2][2:3] of myArray. */
    FNC_GetArrayElements(*ary_handle, &arrayRange, resultBuf,
       bufferSize, NullBitVector, NullVecBufSize, &length);

This example results in an array of 4 integers contained in resultBuf, where the 4 values are:

10 12 20 33

These values may be accessed directly by retrieving the appropriate entry in resultBuf as an integer value.