Example Using FNC_GetQueryBandPairsU - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
rin1593638965306.ditamap
dita:ditavalPath
rin1593638965306.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantageā„¢

SQL Definition

REPLACE FUNCTION GetQBPairsExample()
    RETURNS TABLE
    (QBName        VARCHAR(129) CHARACTER SET UNICODE,
     QBValue       VARCHAR(257) CHARACTER SET UNICODE)
    LANGUAGE C
    NO SQL
    PARAMETER STYLE SQL
    EXTERNAL NAME 'CS!GetQBPairsExample!GetQBPairsExample.c';

C Function Definition

#define SQL_TEXT Unicode_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 FNC_QB_Pair_t *
FNC_GetQueryBandPairsU (
            void            *QBandBuf,
            FNC_QBSearch_et  QB_SearchType,
            int             *numPairs,
            word             QBCharType);


#define EndofDataState     "02000"
#define InvalidParamState  "22023"
#define InsuffMemoryState  "54001"

typedef struct {
    int cnt;
    int NumPairs;
    FNC_QB_Pair_t *pairPtr;
} CTX;

/* copy source to destination */
void tdwstrcpy(VARCHAR_UNICODE       *dest, 
               const VARCHAR_UNICODE *src)
{
        while(*src != (VARCHAR_UNICODE)0)
            *dest++ = *src++;
        *dest = (VARCHAR_UNICODE)0;

}

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

void GetQBPairsExample(
        VARCHAR_UNICODE *QBName,    
        VARCHAR_UNICODE *QBValue, 
        int *qbname_i, 
        int *qbvalue_i,
        char sqlstate[6],
        SQL_TEXT fncname[129],
        SQL_TEXT sfncname[129],
        SQL_TEXT error_message[257] )
{
    int   QBandLen;
    int   minQBLen = 5;
    CTX  *ctx; 
    char *QBandBuf;                  
    FNC_Phase_en Phase;  
    int MaxQBLen = FNC_MAXQUERYBANDSIZE_U;
    
    /* Depending on the phase decide what to do. */
    switch (FNC_GetPhase(&Phase))
    {
        /***********************************************************/
        /* Process the constant expression case. Only one AMP will */
        /* participate for this example */
        /***********************************************************/
        case TBL_MODE_CONST:   
        {
            switch(Phase)
            {
            case TBL_PRE_INIT:
                switch (FNC_TblFirstParticipant() )
                {
                case 1: /* participant */
                    return;
                case 0: /* not participant */
                    FNC_TblOptOut();
                    return;
                default: 
                    return;
                }
                break;

            case TBL_INIT: 
                /* allocate context */
                ctx = FNC_TblAllocCtx(sizeof(CTX));
                ctx->cnt = 0;
 	            
                /* allocate buffer for the queryband */
                QBandBuf = FNC_malloc(MaxQBLen);    
                if (QBandBuf == NULL)
                {
                    strcpy(sqlstate, InsuffMemoryState);
                    return;
                }
                
                /* Get the queryband */
                FNC_GetQueryBandU(QBandBuf, MaxQBLen, &QBandLen); 

                ctx->NumPairs = 0;
                if (QBandLen >= minQBLen)
                {
                    /* Get the pairs */
	             ctx->pairPtr = FNC_GetQueryBandPairsU(QBandBuf,  0,
                        &ctx->NumPairs, EVLUNICODE);
                }
		  FNC_free(QBandBuf);

                break;

            case TBL_BUILD:
                /* Read and build result rows */
           
               ctx = FNC_TblGetCtx();

               if (ctx->cnt < ctx->NumPairs)
               {
                    tdwstrcpy(QBName, ctx->pairPtr[ctx->cnt].QBName);
                    tdwstrcpy(QBValue, ctx->pairPtr[ctx->cnt].QBValue);          
                    ctx->cnt++;
                }
                else
                {
                    /* Have an end of file here. Let the system know. */
                    strcpy(sqlstate, EndofDataState);
                }
                
                break;
            
            case TBL_END:
            case TBL_ABORT:

                /* Everyone is done. */
                ctx = FNC_TblGetCtx();
                FNC_free(ctx->pairPtr);
                break;
            
            } /* end switch phase */

        } /* end case TBL_MODE_CONST */
        break;
        /**********************************/
        /* Process the varying expression */
        /**********************************/
        case TBL_MODE_VARY:
        {
            switch(Phase)
            {
            case TBL_PRE_INIT:
                strcpy(sqlstate, InvalidParamState);
                break;
            case TBL_INIT:
            case TBL_BUILD:
            case TBL_FINI:
            case TBL_ABORT:
            case TBL_END:
                break;
            } /* end switch phase */
            
        } /* case TBL_MODE_VARY */
        break;
    }

	return ;
}