Example: Java Table Operator With UDT Input and Output Columns - Advanced SQL Engine - Teradata Database

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 */
}