Example: Java Table Operator With UDT Input and Output Columns - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

The is an example of a Java table operator that can accept input columns and return output columns of UDT or complex types. The contract function shows how each of the UDT or CDT type can be handled.

/*
CREATE FUNCTION jtblop_udtvals()  
RETURNS TABLE VARYING USING FUNCTION jtblop_udtvals_contract
LANGUAGE JAVA NO SQL  
PARAMETER STYLE SQLTABLE
EXTERNAL NAME 'TBLOPUDT_JAR:jtblop_udtvals.execute';
*/
public class jtblop_udtvals implements TableOperator 
{
   public jtblop_udtvals() {};

public int contract(RuntimeContract contract, ResultSet rsin[], ResultSet rsout[])
throws SQLException 
{
   /* Get number of input streams */
   int incount = contract.getInputInfo().getIncount();
  
   /* Get input columns. */
   MetaData iCols;
   int total_colcount = 0;
   for(int i = 0; i < incount; i++)
   {        
      if (rsin[i] != null)
      {
         iCols = ((TeradataResultSet)rsin[i]).getTeradataMetaData();
         total_colcount += iCols.getColumnCount();        
                    
      }
   }
        
   /* Create an equal number of output columns. */
      
   ColumnDefinition OutCols[] = new ColumnDefinition[total_colcount];

   /* Copy input columns to output columns. */
   int ocount = 0;
   for(int j = 0; j < incount; j++)
   {
      MetaData inCols = ((TeradataResultSet)rsin[j]).getTeradataMetaData();;
      UDTBaseInfo udtbaseinfo[] = contract.getInputInfo().getUDTMetadata(j);
      for (int i=0;i<inCols.getColumnCount();i++) 
      {
         OutCols[ocount]=new ColumnDefinition("O"+ocount, inCols.getTeradataColumnType(i+1));
         OutCols[ocount].setDisplayLength(inCols.getColumnDisplaySize(i+1));      
   
         TeradataType t = TeradataType.get(inCols.getTeradataColumnType(i+1));
         switch (t) 
         {
            case VARCHAR_DT:
            case CHAR_DT: 
               OutCols[ocount].setCharset(inCols.getPrecision(i+1));
               break;
            case BLOB_REFERENCE_DT:
            case CLOB_REFERENCE_DT:
            case VARBYTE_DT: 
            case XML_DT:
            case JSON_DT:     
            case ST_GEOMETRY_DT:
               OutCols[ocount].setCharset(inCols.getPrecision(i+1));
               OutCols[ocount].setDisplayLength(inCols.getColumnDisplaySize(i+1));
               break;
            case DECIMAL1_DT:
            case DECIMAL2_DT:
            case DECIMAL4_DT:
            case DECIMAL8_DT:
            case DECIMAL16_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
            case NUMBER_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
               break;
            case TIME_DT:
            case TIMESTAMP_DT:
            case TIME_WTZ_DT:
            case TIMESTAMP_WTZ_DT:
            case PERIOD_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               break;
            case INTERVAL_SECOND_DT:
            case INTERVAL_DTS_DT:
            case INTERVAL_HTS_DT:
            case INTERVAL_MTS_DT:
            case INTERVAL_DTM_DT:     // DAY TO MINUTE
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
               break;
            case ARRAY_DT:
            case UDT_DT:
            case MBR_DT:
            case MBB_DT:
               OutCols[ocount].setUdtName(inCols.getUdtTypeName(i+1));
          
         }
         OutCols[ocount].setPeriodType(inCols.getPeriodType(i+1));
         ocount++;
      }
   }

   /* Define output columns. */
   contract.setOutputInfo(0, OutCols); 
   contract.complete();
   return 1;          
}

public void execute(RuntimeContract contract, ResultSet rsin[], ResultSet rsout[])
throws SQLException 
{  
   /* Tblop Execute code */  
}  /* execute */
}