16.20 - Example Using FNC_GetQueryBandValueU - 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)

This example shows a UDF that accepts a LATIN or UNICODE input name parameter.

SQL Definition

REPLACE FUNCTION GetQBValueExample(
       SearchType SMALLINT,
       QBName     TD_ANYTYPE)
    RETURNS VARCHAR(257) CHARACTER SET UNICODE
    LANGUAGE C
    NO SQL
    PARAMETER STYLE TD_GENERAL
    EXTERNAL NAME 'CS!GetQBValExample!GetQBValExample.c';

C Function Definition

#define SQL_TEXT Latin_Text

#include<string.h>
#include<sqltypes_td.h>

typedef unsigned short int word;
#define EVLLATIN1   1
#define EVLUNICODE  2

extern void
FNC_GetQueryBandU(void      *QBandBuf,
                  int        BufSize,
                  int       *QBandLen);

extern void
FNC_GetQueryBandValueU (
            void            *QBandBuf,
            FNC_QBSearch_et  QB_SearchType,
            void            *QBName,
            void            *QBValue,
            word             QBCharType,
            word            NameCharType);


#define OKState            "00000"
#define InvalidParamState  "22023"
#define InsuffMemoryState  "54001"

/****************************************************/

void GetQBValueExample(
        void     *QBName,                   
        VARCHAR_UNICODE *QBValue,           
        char sqlstate[6])
{
    int QBandLen;
    int minQBLen = 5;
    VARCHAR_UNICODE *QBandBuf;
    word  NameCharType; 
    int   numAnytypeParams;
    anytype_param_info_t inputInfo[1]; 
    int MaxQBLen;
                    
    MaxQBLen = FNC_MAXQUERYBANDSIZE_U;

    /* QBName is TD_ANYTYPE */
    FNC_GetAnyTypeParamInfo(sizeof(anytype_param_info_t),
        &numAnytypeParams, inputInfo);
    if ((numAnytypeParams >= 0) &&
        (inputInfo[0].paramIndex == 1) &&
        ((inputInfo[0].datatype == VARCHAR_DT) ||
         (inputInfo[0].datatype == CHAR_DT))
       )
    {
        if (inputInfo[0].charset == LATIN_CT)
            NameCharType = EVLLATIN1;
        else
            NameCharType = EVLUNICODE;
    }
    else
    {
        strcpy(sqlstate, InvalidParamState);
        return;
    }

    /* allocate buffer for the queryband */             
    QBandBuf = FNC_malloc(MaxQBLen);     
    if (QBandBuf == NULL)
    {
        strcpy(sqlstate, InsuffMemoryState);
        return;
    }
                
    /* Get the queryband */
    FNC_GetQueryBandU(QBandBuf, MaxQBLen, &QBandLen);          
    if (QBandLen >= minQBLen)
    {
        /* Get the value */
        FNC_GetQueryBandValueU(QBandBuf, 0, QBName, QBValue,
            EVLUNICODE, NameCharType);     
    }
    else
    {
        /* Return an empty string for the value */
        memcpy(QBValue, "\0\0", 2);
    }
                   
    FNC_free(QBandBuf);
    strcpy(sqlstate, OKState);  

	return ;
}