17.10 - Example: FNC_TblOpGetUDTMetadata and FNC_TblOpGetStructuredAttributeInfo - 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)

The following shows how FNC_TblOpGetUDTMetadata and FNC_TblOpGetStructuredAttributeInfo can be used in a contract function.

int tblopudt_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;
    UDT_BaseInfo_t *udtBaseInfo;
    Stream_Fmt_en   format;
    InputInfo_t *icolinfo;
    int incount, outcount , ocolcount;
    int i,j, totalcols;
    char mycontract[] = "this is my contract... this is my contract... this is my contract...";
    char msg[500];

    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);

       /* Retreive UDT metadata info for each column and write to trace table */
       iCols = icolinfo[i].iCols;
       icolinfo[i].baseColInfo = FNC_malloc(sizeof(UDT_BaseInfo_t) * iCols->num_columns);

       FNC_TblOpGetUDTMetadata(i,ISINPUT,-1,
                               sizeof(UDT_BaseInfo_t) * iCols->num_columns,
                               &(icolinfo[i].baseColInfo[0]));
    
       for(j=0; j < iCols->num_columns; j++)
       {
         UDT_BaseInfo_t* udtbaseinfo;
         int bufferSize = 0;
         attribute_info_eon_t * attrInfo = NULL;
         char udtname[100];
         udtbaseinfo = &(icolinfo[i].baseColInfo[j]);
         int k;
         if(udtbaseinfo->udt_indicator == 2)  // Structured UDT
         {
            bufferSize = udtbaseinfo->struct_num_attributes * sizeof(attribute_info_eon_t);
            attrInfo = FNC_malloc(bufferSize);
            memset(attrInfo,0,bufferSize);
            FNC_TblOpGetStructuredAttributeInfo(udtbaseinfo->udt_name,
                                                bufferSize,attrInfo);          
            FNC_free(attrInfo);
         }
       }

    }
    /* 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;
       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 ARRAY_DT:
              case UDT_DT:
              case MBB_DT:
              case MBR_DT:
                  strcpy(oCols->column_types[ocolcount].udt_type,iCols->column_types[i].udt_type);
                  oCols->column_types[ocolcount].size.length = iCols->column_types[i].size.length;
              break;
              default:
                  oCols->column_types[ocolcount].size.length = iCols->column_types[i].size.length;
              break;
         }
         oCols->column_types[ocolcount].datatype = iCols->column_types[i].datatype;
         oCols->column_types[ocolcount].charset = iCols->column_types[i].charset;
         oCols->column_types[ocolcount].period_et = iCols->column_types[i].period_et;
         oCols->column_types[ocolcount].bytesize = iCols->column_types[i].bytesize;
         oCols->column_types[ocolcount].JSONStorageFormat = iCols->column_types[i].JSONStorageFormat;
         ocolcount++;

      }

    }

    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[i].baseColInfo);
    }

    FNC_free(icolinfo);
    *Result = 1;
}