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 <stdio.h>
#include <string.h>

/* definition of the scratch pad */

#define FALSE   0
#define TRUE    1
#define MAX_OUT_PARAMS 4
typedef struct
{
int indicator;
char dtype[5];
void *value;
} column_info;

typedef struct
{
int           done;
VARCHAR_LATIN *Tscan;
column_info cols[MAX_OUT_PARAMS];

} gen_ctx;

/*****************************************************************/
/* A simple function to scan 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;
}

/******************************************************************/
/* Obtain all result column definitions for a table function with */
/* variable output columns.                                       */
/******************************************************************/
static int
CheckColDef(gen_ctx *info,FNC_ColumnDef_t *col_def,
            VARCHAR_LATIN *Text,
            void *col1,void *col2,void *col3,void *col4,
            int col1_i,int col2_i,int col3_i, int col4_i)
{
   int j;
   int i;
   VARCHAR_LATIN *TScan=Text ;
   info->Tscan=TScan;

   // Prepare output datatype string format for sscanf
   for(j=0;j<col_def->num_columns;j++)
   {

      switch(col_def->column_types[j].datatype)
      {
         case DATE_DT:
         case INTEGER_DT:
         case BYTEINT_DT:
         case SMALLINT_DT:
            strcpy(info->cols[j].dtype,"%d");
            break;

         case CHAR_DT:
            strcpy(info->cols[j].dtype,"%c");
            break;

         case VARCHAR_DT:
            strcpy(info->cols[j].dtype,"%s");
            break;
         default:
            return -1;
      }
   }

   info->cols[0].indicator=col1_i;
   info->cols[1].indicator=col2_i;
   info->cols[2].indicator=col3_i;
   info->cols[3].indicator=col4_i;
   info->cols[0].value=col1;
   info->cols[1].value=col2;
   info->cols[2].value=col3;
   info->cols[3].value=col4;

   /* find all output columns that specified in sel stmt */
   for(i=0;i<col_def->num_columns;i++)
   {
      if(info->cols[i].indicator==0)
      {
         sscanf((char *)info->Tscan,info->cols[i].dtype, 
            info->cols[i].value);
         info->Tscan=next(',',info->Tscan)+1;
      }
   }
   return 0;
}
/*******************************************************************/
/* This is a simple demo program to show how to utilize the result */
/* returned from FNC_TBlGetColDef().                               */
/*******************************************************************/
void
get_data (VARCHAR_LATIN *Text,    /* input string */
          void          *col1,    /* output parameters */
          void          *col2,
          void          *col3,
          void          *col4,
          int           *Text_i,  /* input parameter indicator */
          int           *col1_i,  /* output indicators for row ...*/
          int           *col2_i,
          int           *col3_i,
          int           *col4_i,
          char           sqlstate[6],
          SQL_TEXT       fncname[129],
          SQL_TEXT       sfncname[129],
          SQL_TEXT       error_message[257] )
{
   int status;
   FNC_ColumnDef_t *The_Columns;
   FNC_Phase  Phase;
   gen_ctx *LocalGenCtx;

   /* Definition of result parameters returned from FNC_TblGetColDef */
   The_Columns = FNC_TblGetColDef();

   /* this will keep the state of which rows have been sent */
   /* make sure the function is called in the supported context */
   switch (FNC_GetPhase(&Phase))
   {
     /******************************************************/
     /* Process the constant expression case. Only one AMP */
     /* participates for this example.                     */
     /******************************************************/
     case TBL_MODE_CONST:

       /* Depending on the phase, decide what to do. */
       switch(Phase)
       {
         case TBL_PRE_INIT:
           switch (FNC_TblFirstParticipant() )
           {
             case 1:     /* participant */
               return;
             case 0:     /* not participant */
               /* Don't participate. */
               if (FNC_TblOptOut())
               {
                 strcpy(sqlstate, "U0006");  /* Return error. */
                 strcpy((char *) error_message,"Opt-out failed.");
                 return;
               }
               break;
             default:  /* -1 or other error */
               strcpy(sqlstate, "U0007");
               strcpy((char *) error_message,
                      "First Participant Logic did not work.");
               return;
           }

         case TBL_INIT:
           /* Allocate a general scratchpad to retain data */
           /* between iterations.                          */
           LocalGenCtx = FNC_TblAllocCtx(sizeof(gen_ctx));
           LocalGenCtx->done = FALSE;
           break;

         case TBL_BUILD:
           /* Get the scratchpad. */
           LocalGenCtx = FNC_TblGetCtx();

           if (LocalGenCtx->done)
           {
             /* Have no more data; return no data sqlstate. */
             strcpy(sqlstate, "02000");
             strcpy((char *) error_message, "EOF");
             return;
           }

           /* Check and prepare output column datatype */
           status = CheckColDef (LocalGenCtx,The_Columns,Text,
                                 col1,col2,col3,col4,
                                 *col1_i,*col2_i,*col3_i,*col4_i);
           if (status == -1)
           {
             status = FNC_TblAbort();
             /* If I was the first then let's report the error. */
             strcpy(sqlstate, "U0009");
             return;
           }

           LocalGenCtx->done = TRUE;
           break;
         case TBL_END:
         case TBL_ABORT:
           break;
       }

       break;
     /****************************************/
     /* Process the varying expression case. */
     /****************************************/
     case TBL_MODE_VARY:
       switch(Phase)
       {
         case TBL_PRE_INIT:
           LocalGenCtx = FNC_TblAllocCtx(sizeof(gen_ctx));
           if (LocalGenCtx == NULL)
           {
             strcpy (sqlstate, "U006");
             strcpy ((char *) error_message,
                     "Context Allocation failed.");
             return;
           }
           break;
         case TBL_INIT:
           LocalGenCtx = FNC_TblGetCtx();
           LocalGenCtx->done = FALSE;
           break;
         case TBL_BUILD:
           LocalGenCtx = FNC_TblGetCtx();
           if (LocalGenCtx->done)
           {
             /* Have no more data return no data sqlstate */
             strcpy(sqlstate, "02000");
             strcpy((char *) error_message, "EOF");
             return;
           }

           status = CheckColDef (LocalGenCtx,The_Columns,Text,
                                 col1,col2,col3,col4,
                                 *col1_i,*col2_i,*col3_i,*col4_i);
           if (status == -1)
           {
             status = FNC_TblAbort();
             /* if I was the first then let's report the error */
             strcpy(sqlstate, "U0009");
             return;
           }

           LocalGenCtx->done = TRUE;
           break;
         case TBL_END:
         case TBL_ABORT:
           break;
       }
   }
}