16.20 - Example: C Table Operator That Retrieves UDT Metadata - 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)

The is an example of a C table operator that uses FNC_TblOpGetBaseInfo() to retrieve UDT metadata.

/* 
CREATE FUNCTION mift1()
RETURNS TABLE VARYING USING FUNCTION mift1_contract
SPECIFIC mift1
LANGUAGE C
NO SQL
NO EXTERNAL DATA
PARAMETER STYLE  SQLTable
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!mift1!mift1.c!F!mift1'; */
#define SQL_TEXT Latin_Text
#include <stdio.h>
#include <sqltypes_td.h>
#define BufferSize (32*1024)
#define SetError(e, m) strcpy((char *)sqlstate, ( e) ); strcpy((char *)error_message, ( m ))
typedef struct
{
   int colcount;
   FNC_TblOpColumnDef_t *iCols;
   FNC_TblOpHandle_t  *Handle;
   int is_eof;
   int dimension;
}InputInfo_t;
int mift1_contract(
                    INTEGER *Result,
                    int *indicator_Result,
                    char sqlstate[6],
                    SQL_TEXT extname[129],
                    SQL_TEXT specific_name[129],
                    SQL_TEXT error_message[257])
{
    FNC_TblOpColumnDef_t    *oCols;
    FNC_TblOpColumnDef_t    *iCols;
    Stream_Fmt_en   format;
    InputInfo_t *icolinfo; 
    int incount, outcount, ocolcount;
    int i,j, totalcols;
    UDT_BaseInfo_t *baseInfos;
    char mycontract[] = "this is my contract... this is my contract... this is my contract...";

    FNC_TblOpGetStreamCount(&incount, &outcount);
    if(incount == 0)
    {
     SetError("U0003", "mift1 requires number of input streams to be > 0.");
     return -1;
    }

    icolinfo = FNC_malloc (incount * sizeof(InputInfo_t));

    totalcols = 0;
    for(i=0; i < incount; i++)
    {
      icolinfo[i].colcount = FNC_TblOpGetColCount(i, ISINPUT);
      totalcols += icolinfo[i].colcount;

      icolinfo[i].iCols = FNC_malloc(TblOpSIZECOLDEF(icolinfo[i].colcount));
      TblOpINITCOLDEF(icolinfo[i].iCols, icolinfo[i].colcount);
      FNC_TblOpGetColDef(i, ISINPUT, icolinfo[i].iCols);
    } 

    /* Allocate space for columns. */
    oCols = (FNC_TblOpColumnDef_t *)FNC_malloc( TblOpSIZECOLDEF(totalcols) );
    memset(oCols, 0 , TblOpSIZECOLDEF(totalcols) );
    oCols->num_columns = totalcols;
    oCols->length = TblOpSIZECOLDEF(totalcols) - (2 * sizeof(int)) ;
    TblOpINITCOLDEF(oCols, totalcols);
    ocolcount = 0;

    /* Copy input columns to output columns. */
     for(j=0; j < incount; j++)
     {
       iCols = icolinfo[j].iCols;
       baseInfos = (UDT_BaseInfo_t *)FNC_malloc(iCols->num_columns * sizeof(UDT_BaseInfo_t));
       /* Get base info */
       FNC_TblOpGetBaseInfo(iCols, baseInfos);
	   
       for(i=0;i < iCols->num_columns;i++)
       {
         switch (iCols->column_types[i].datatype)
         {
            case DECIMAL1_DT: 
            case DECIMAL4_DT: 
            case DECIMAL8_DT:
               oCols->column_types[ocolcount].size.range.totaldigit
                  = iCols->column_types[i].size.range.totaldigit;
               oCols->column_types[ocolcount].size.range.fracdigit
                  = iCols->column_types[i].size.range.fracdigit;
               break;
            case DECIMAL2_DT:
               oCols->column_types[ocolcount].size.range.totaldigit= 5;
               oCols->column_types[ocolcount].size.range.fracdigit= 
                  iCols->column_types[i].size.range.fracdigit;
               break;
            case TIME_DT: 
            case TIMESTAMP_DT: 
            case PERIOD_DT:
               oCols->column_types[ocolcount].size.precision = 
                  iCols->column_types[i].size.precision;
               break;
            case INTERVAL_YEAR_DT:
            case INTERVAL_YTM_DT:
            case INTERVAL_MONTH_DT:
            case INTERVAL_DAY_DT:
            case INTERVAL_DTH_DT:
            case INTERVAL_DTM_DT:
            case INTERVAL_DTS_DT:
            case INTERVAL_HOUR_DT:
            case INTERVAL_HTM_DT:
            case INTERVAL_HTS_DT:
            case INTERVAL_MINUTE_DT:
            case INTERVAL_MTS_DT:
            case INTERVAL_SECOND_DT:
               oCols->column_types[ocolcount].size.intervalrange =
                  iCols->column_types[i].size.intervalrange;
               break;
            case JSON_DT:
               oCols->column_types[ocolcount].JSONStorageFormat = JSON_TEXT_EN;
               oCols->column_types[ocolcount].charset =
                  iCols->column_types[i].charset;
               oCols->column_types[ocolcount].size.length = 
                  iCols->column_types[i].size.length;
               oCols->column_types[ocolcount].udt_indicator = 5;
               memcpy(oCols->column_types[ocolcount].udt_type, 
                  icols->column_types[i].udt_type, FNC_MAXNAMELEN_EON);
               break;
            case ARRAY_DT:			      
               oCols->column_types[ocolcount].charset = 
                  iCols->column_types[i].charset;
               oCols->column_types[ocolcount].size.length = 
                  iCols->column_types[i].size.length;
               oCols->column_types[ocolcount].udt_indicator = 4;
               memcpy(oCols->column_types[ocolcount].udt_type, 
                  icols->column_types[i].udt_type, FNC_MAXNAMELEN_EON); 
               oCols->column_types[ocolcount].struct_num_attributes= 
                  iCols->column_types[i].struct_num_attributes;
               break;

            default:
               oCols->column_types[ocolcount].size.length =
                  iCols->column_types[i].size.length;
               oCols->column_types[ocolcount].charset = LATIN_CT;
               break;
          }
          oCols->column_types[ocolcount].datatype =
             iCols->column_types[i].datatype;         
          oCols->column_types[ocolcount].period_et =
             iCols->column_types[i].period_et;
          oCols->column_types[ocolcount].bytesize =
             iCols->column_types[i].bytesize;
          ocolcount++;
       }

          FNC_free(baseInfos);
    }

    FNC_TblOpSetContractDef(mycontract, strlen(mycontract)+1);
    /* Define output columns. */
    FNC_TblOpSetOutputColDef(0, oCols);
    format = INDICFMT1;
    FNC_TblOpSetFormat("RECFMT", 0, ISINPUT, &format, sizeof(format));
    FNC_TblOpSetFormat("RECFMT", 0, ISOUTPUT, &format, sizeof(format));

    FNC_free(oCols);
    for(i=0; i < incount; i++)
      FNC_free(icolinfo[i].iCols);

    FNC_free(icolinfo);
    *Result = 1;
}
void mift1()
{
    int i;
    FNC_TblOpColumnDef_t   *iCols, *oCols;
    FNC_TblOpHandle_t *Handle,*OutHandle;
    Stream_Fmt_en   format;
    int incount, outcount,j;
    InputInfo_t *icolinfo;
    int *Result;
    int allStreamsEOF = 0;
    LOB_CONTEXT_ID lobid;
    FNC_LobLength_t actlen;
    LOB_RESULT_LOCATOR lrl_a[32];
    int truncerr;
    BYTE Buffer[BufferSize];
    int foundrow = 0;
    int isfirsttime = 1;

    FNC_TblOpGetStreamCount(&incount, &outcount);
    icolinfo = FNC_malloc (incount * sizeof(InputInfo_t));
    
    for(i=0; i < incount; i++)
    {
      /* Get Column Count */
      icolinfo[i].colcount = FNC_TblOpGetColCount(i, ISINPUT);

      /* Get Column Definitions */
      icolinfo[i].iCols = FNC_malloc(TblOpSIZECOLDEF(icolinfo[i].colcount));
      TblOpINITCOLDEF(icolinfo[i].iCols, icolinfo[i].colcount);
      FNC_TblOpGetColDef(i, ISINPUT, icolinfo[i].iCols);

      /* Get Handles */
      icolinfo[i].Handle = (FNC_TblOpHandle_t *)FNC_TblOpOpen(i,'r',0); 
      icolinfo[i].dimension = FNC_TblOpIsDimension(i,ISINPUT);
      icolinfo[i].is_eof = 0;
    }
    Result = FNC_malloc(incount * SIZEOF_INTEGER);
    OutHandle = (FNC_TblOpHandle_t*)FNC_TblOpOpen(0,'w',0);

    isfirsttime = 1;
    while(1)
    {
       allStreamsEOF = 1; 
       int ocount;
       for(j=0; j < incount; j++)
       {
          if ( icolinfo[j].is_eof == 0)  
             Result[j] = FNC_TblOpRead(icolinfo[j].Handle);

          if( Result[j] == TBLOP_EOF)
             icolinfo[j].is_eof = 1;
          else
          if( Result[j] == TBLOP_SUCCESS)
             icolinfo[j].is_eof = 0;

          allStreamsEOF = allStreamsEOF & icolinfo[j].is_eof;  
       }   

       if(allStreamsEOF)
          break;
       /* We will write the o/p if atleast one non-dimesion o/p found a row */
       foundrow = 0 ;    
       if( isfirsttime == 1 ) 
       {
          for( j=0; j < incount; j++)
          {
             if (icolinfo[j].dimension == 0 && icolinfo[j].is_eof == 0)
             {
                foundrow = 1;
             }
          }
       }
       if ( isfirsttime == 1 && foundrow == 0)
          break;
       isfirsttime = 0;
       ocount = 0;
       for(j=0; j < incount; j++)
       {
          Handle = icolinfo[j].Handle;
          iCols = icolinfo[j].iCols;
          for (i=0; i < iCols->num_columns; i++)
          {
             switch (iCols->column_types[i].datatype)
             {
                case CLOB_REFERENCE_DT:
                case BLOB_REFERENCE_DT:
                case JSON_DT:
                case XML_DT:
                case ST_GEOMETRY_DT:

                   /* This is output code, as we only have 1 outputstream should be 0 */
                   lrl_a[ocount] = FNC_LobCol2Loc(0, ocount);

                   if(icolinfo[j].is_eof == 0)
                   {
                      if (!(TBLOPISNULL(Handle->row->indicators,i)))
                      {
                         FNC_LobOpen_CL(Handle->row->columnptr[i], &lobid, 0, 0);
			  
                         truncerr = 0;
                         while( FNC_LobRead(lobid, Buffer, BufferSize, &actlen) ==
                                0 && !truncerr)
                            truncerr=FNC_LobAppend (lrl_a[ocount], Buffer, actlen, &actlen);
                         FNC_LobClose(lobid);
                      }
                   }
		                 break;
                default:

                   if(icolinfo[j].is_eof == 0)
                   {
                      OutHandle->row->columnptr[ocount] = Handle->row->columnptr[i];
                      OutHandle->row->lengths[ocount] = Handle->row->lengths[i];
                   }
		  
                   break;
             }
             if (icolinfo[j].is_eof == 1 || TBLOPISNULL(Handle->row->indicators,i))
             {
                TBLOPSETNULL(OutHandle->row->indicators,ocount);   
             }
             ocount++;
          }
       }
       FNC_TblOpWrite(OutHandle);  // Writes current output row
    }
    for(i=0; i < incount; i++)
    {
       FNC_free(icolinfo[i].iCols);
       FNC_TblOpClose(icolinfo[i].Handle);     // close all contexts
    }
    FNC_free(icolinfo);
    FNC_free(Result);
    FNC_TblOpClose(OutHandle);  // close all contexts
}