17.10 - C Function Definition - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

/*************************************/
/* The definition of the scratch pad */
/*************************************/
typedef struct
{
    int  custid;
    int  itemid;
} item_t;

typedef struct {
    int         Num_Items;
    int         Cur_Item;
    INTEGER     store_num;
    item_t     *Item_List;
} local_ctx;

/********************************************************************/
/* This copy to SQL_TEXT fields will work to copy ASCII strings to  */
/* SQL_TEXT strings (in this case error_message) for any character  */
/* set mode. In other words if the SQL_TEXT is defined as           */
/* Unicode_Text it will still work to give the proper error message */
/********************************************************************/
static void unicpy(SQL_TEXT *dest,
                   char *src)

{
    while (*src)
        *dest++ = *src++;
}
/********************************************************************/
/* A simple function to scan to the next break in the text based on */
/* the delimiter passed in                                          */
/********************************************************************/
static VARCHAR_LATIN *next(VARCHAR_LATIN find,
                           VARCHAR_LATIN *data)
{
    while (*data != '\0')
    {
        if (*data == find)
           break;
        data++;
    }
   return data;
}

/*********************************************************************/
/* The text data that this function processes is in a very simple    */
/* format:                                                           */
/*                                                                   */
/* <storenum>,<num items>:<customer id>,<item number>, ... ;         */
/* <storenum>,<num items>: ...                                       */
/*********************************************************************/


/*********************************************************************/
/* Do a pre-scan of the text and save the data. Note: This pre-scan  */
/* routine actually extracts all needed data out of the text field   */
/* and saves it in allocated memory via FNC_malloc. With this logic  */
/* when it gets to the TBL_BUILD phase the data will simply be taken */
/* from the saved area. There is no need to look at the original     */
/* string again during the TBL_BUILD phase. This is just one way to  */
/* design it. The alternative is to just do the scanning each time   */
/* TBL_BUILD is called from the text data field that is passed in    */
/* to the table function at all times. It is a choice that the       */
/* designer must make when developing the application.               */
/*********************************************************************/
static  int Prescan(local_ctx      *info,
                    VARCHAR_LATIN  *Text,
                    INTEGER        *frmstore)
{
    INTEGER storenum;
    int  i;
    int num_items = 0;
    VARCHAR_LATIN *Tscan = Text;

/* find the data for the store we are interested in */
while (*Tscan )
    {
        sscanf((char *) Tscan, "%d", &storenum);
        if (*frmstore == storenum)
        {
            /* found the entry of interest - get the information */
            /* on how many items there are                       */
            Tscan = next(',', Tscan)+1;
            sscanf((char *) Tscan, "%d", &num_items);
            break;
        }
        /* find next store */
        Tscan = next(';',Tscan);
        if (*Tscan == '\0')
            break;
        Tscan++;
    }


    /* let's malloc some worst case memory to keep track of the items */
    /* we collect                                                     */
    if (num_items)
    {
        info->Item_List = FNC_malloc(sizeof(item_t)*num_items);
        if (info->Item_List == NULL)
            /* not good - should have been able to get the memory */
            return -1;
    }
    else
    {
        info->Num_Items = 0;
        return 0;
    }
        /* now let's find all the entries for the store that we are */
        /* interested in                                           */
    /* skip to first item */
    Tscan = next(':', Tscan)+1;
    for (i=0; i<num_items; i++)
    {
        sscanf((char *) Tscan,
               "%d,%d",
               &info->Item_List[i].custid,
               &info->Item_List[i].itemid);
        Tscan = next(',', Tscan)+1;
        Tscan = next(',', Tscan)+1;
    }

    info->Num_Items = num_items;
    info->store_num = *frmstore;
    return num_items;
}

/********************************************************************/
/* Extract all of the data now. Actually this routine just takes    */
/* the items that Prescan built and transfers the data out one item */
/* at a time. Notice that it does not build the output column if it */
/* is not being asked for. For this simple example it probably      */
/* makes no difference, but if there is a lot of complexity in the  */
/* application to build some columns then it could when noticing    */
/* that a field is null not go through the computation to build it  */
/* at all. In fact the Prescan function could have been smarter and */
/* not built the list of fields that are not being asked for        */
/********************************************************************/
static int Extract(local_ctx      *info,
                   INTEGER        *custid,
                   INTEGER        *store,
                   INTEGER        *itemid,
                   int            custid_i,
                   int            store_i,
                   int            item_i)
{
    /* check to see if there is something left to extract */
    if (info->Cur_Item == info->Num_Items)
        return 0;

    /* okay let's set the output data only if they want it */
    if (custid_i == 0)
        *custid = info->Item_List[info->Cur_Item].custid;
    if (store_i == 0)
        *store = info->store_num;
    if (item_i == 0)
        *itemid = info->Item_List[info->Cur_Item].itemid;

    /* set up for next item the next time */
    info->Cur_Item++;
    return 1;
}

/***********************************/
/* Do a reset of the context block */
/***********************************/
static void Reset(local_ctx  *info)
{
    info->Num_Items = 0;
    info->Cur_Item  = 0;
    info->Item_List = NULL;
}

/*********************************************************************/
/* Clean up upon error or when done. Needs to free up any memory     */
/* that was allocated or it will return an error message. Note that  */
/* the memory was allocated outside of the general scratch pad. But  */
/* the address must be retained in the scratch pad or you have no    */
/* means of referencing the data or freeing it for subsequent calls. */
/*********************************************************************/
static void Clean_Up(local_ctx *info)
{
    if (info->Item_List)
        FNC_free(info->Item_List);

}
void extract_field(VARCHAR_LATIN *Text, /* field decode */
                   INTEGER *frmStore,   /* data to extract */
                   INTEGER *custid,     /* 1st output column for row */
                   INTEGER *store,      /* 2nd output column */
                   INTEGER *item,
                   int     *Text_i,     /* in parameter indicator */
                   int     *frmstore_i, /* if no store, return no row */
                   int     *custid_i,   /* 1st output indicator for */
                   int     *store_i,    /* row, and so on */
                   int     *item_i,
                   char     sqlstate[6],
                   SQL_TEXT fncname[129],
                   SQL_TEXT sfncname[129],
                   SQL_TEXT error_message[257] )
{
    local_ctx  *state_info;
    FNC_Phase  Phase;
    int        status;

    /* make sure the function is called in the supported context */
    switch (FNC_GetPhase(&Phase))
    {
        /***********************************************************/
        /* Process the constant expression case. Only one AMP will */
        /* participate for this example                            */
        /***********************************************************/
      case TBL_MODE_CONST:

        /* depending on the phase decide what to do */
        switch(Phase)
        {
          case TBL_PRE_INIT:
            /* let the system know that I want to be the participant */
            switch (FNC_TblFirstParticipant() )
            {
              case 1:   /* participant */
                return;

              case 0:  /* not participant */
                /* don't participate */
                if (FNC_TblOptOut())
                {
                    strcpy(sqlstate, "U0006");  /* an error return */
                    unicpy(error_message, "Opt-out failed.");
                    return;
                }
                break;
              default:  /* -1 or other error */
                strcpy(sqlstate, "U0007");
                unicpy(error_message, 
                       "First Participant logic did not work");
                return;
            }

          case TBL_INIT:
            /* get scratch memory to keep track of things */
            state_info = FNC_TblAllocCtx(sizeof(local_ctx));
            Reset(state_info);
            /* Preprocess the Text  */
            status = Prescan(state_info, Text, frmStore );
            if (status == -1)
            {
                Clean_Up(state_info);
                strcpy(sqlstate, "U0008");
                unicpy(error_message, "Text had pre-scan errors.");
                return;
            }

            break;

          case TBL_BUILD:
            state_info = FNC_TblGetCtx();

            status = Extract(state_info,
                             custid,
                             store,
                             item,
                             *custid_i,
                             *store_i,
                             *item_i);
            if (status == 0)
                /* Have no more data, return no data sqlstate. */
                strcpy(sqlstate, "02000");
            else if (status == -1)
            {
                Clean_Up(state_info);
                strcpy(sqlstate, "U0009");
                unicpy(error_message, "Text had extract error.");
                return;
            }
            break;
          case TBL_END:
            /* everyone done */
            state_info = FNC_TblGetCtx();
            Clean_Up(state_info);
            break;
        }
        break;

        /****************************************/
        /* Process the varying expression case. */
        /****************************************/
      case TBL_MODE_VARY:
        switch(Phase)
        {
          case TBL_PRE_INIT:
            /* get scratch memory to use from now on */
            state_info = FNC_TblAllocCtx(sizeof(local_ctx));
            Reset(state_info);
            break;

          case TBL_INIT:
            /* Preprocess the Text  */
            state_info = FNC_TblGetCtx();
            status = Prescan(state_info, Text, frmStore );
            if (status == -1)
            {
                status = FNC_TblAbort();
                if (status == 1)
                {
                    Clean_Up(state_info);
                    strcpy(sqlstate, "U0008");
                    unicpy(error_message, "Text had pre-scan errors");
                    return;
                }
            }
            break;

          case TBL_BUILD:
            state_info = FNC_TblGetCtx();
            status = Extract(state_info,
                             custid,
                             store,
                             item,
                             *custid_i,
                             *store_i,
                             *item_i);
            if (status == 0)
                /* Have no more data; return no data sqlstate. */
                strcpy(sqlstate, "02000");
            else if (status == -1)
            {
                status = FNC_TblAbort();
                /* If I was the first then report the error */
                if (status = 1)
                {
                    Clean_Up(state_info);
                    strcpy(sqlstate, "U0009");
                    unicpy(error_message, "Text had extract error");
                }
                return;
            }
            break;
          case TBL_FINI:
            /* Initialize for the next set of data. */
            state_info = FNC_TblGetCtx();
            Clean_Up(state_info);
            Reset(state_info);
            break;
          case TBL_END:
            /* Everyone done. */
            state_info = FNC_TblGetCtx();
            Clean_Up(state_info);
            break;
          case TBL_ABORT:
            state_info = FNC_TblGetCtx();
            Clean_Up(state_info);
            break;
        }
    }
}