Using TD_ANYTYPE Parameters with the .NET Data Provider

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Support for a new dynamically allocated parameter type (TD_ANYTYPE) is available in Teradata Database 14.0. This Teradata enhancement supports input and result parameter types that may be defined dynamically at execution time. The use of the TD_ANYTYPE data type definition in functions and external stored procedures will be discussed.

Some of the benefits of TD_ANYTYPE parameters include:

  • Elimination of the overloading limitation within functions and external stored procedures based upon character set and/or precision. Presently, functions/methods/procedures cannot be defined with the same number and type of parameters with varying character sets and/or precisions. For instance the following function would be flagged as a duplicate.
Ascii (param1 INTEGER) RETURNS CHAR(20) CHARACTER SET LATIN
Ascii (param1 INTEGER) RETURNS CHAR(20) CHARACTER SET KANJI

Re-defining the function return type as a TD_ANYTYPE allows various character sets to be returned.

Ascii (param1 INTEGER RETURNS TD_ANYTYPE)

To call the function, the new TD_ANYTYPE RETURNS clause may be used to specify the returned character set. 

// REPLACE FUNCTION ascii( inParam INTEGER ) RETURNS TD_ANYTYPE

public void AnyTypeFunction()
{

    String functionCmdTextReturns = @"SELECT (ascii({0}) RETURNS {1})";

    TdCommand cmd = _cn.CreateCommand();

    Int32 inParam = 2;
    cmd.CommandText = String.Format(functionCmdTextReturns, inParam, "CHAR(20) CHARACTER SET LATIN");
    cmd.CommandType = CommandType.Text;

    TdDataReader rdr = cmd.ExecuteReader();
    rdr.Read();
    String result = rdr.GetString(0);
   
    rdr.Close();
}
  • Another benefit of TD_ANYTYPE parameters is the reduction in the number of functions/methods/procedures.  The TD_ANYTYPE parameter may be substituted for any of the Teradata supported data types, thereby reducing the different definitions.

The .NET Data Provider for Teradata supports 2 methods of executing stored procedures.  The first method generates the CALL statement automatically and the second method enables the programmer to compose the CALL statement manually.

  1. Provider Generated CALL Statement in Stored Procedures

    The provider will generate the RETURNS clause based upon the definition of the parameters. Given this external stored procedure definition for xspAnyType01, the IN and OUT parameters are both defined as TD_ANYTYPE data types.  The TdCommand.CommandType must be set to CommandType.StoredProcedure. The definition of the TD_ANYTYPE OUT parameter return type is not required when there are any IN or INOUT TD_ANYTYPE parameters.  The first defined IN/INOUT parameter data type will serve as the default TD_ANYTYPE OUT parameter data type.
//Procedure xspAnyType01(IN param1 TD_ANYTYPE, OUT result1 TD_ANYTYPE)
public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "xspAnyType01";
    cmd.Parameters.Add(null, TdType.Integer);
    cmd.Parameters[0].Value = 4;
  
    cmd.Parameters.Add(null, TdType.Integer);
    cmd.Parameters[1].Direction = ParameterDirection.Output;
   
    TdDataReader dr = cmd.ExecuteReader();
    dr.Read();
  
    // Going to process data of output parameter
    Int32 result = (Int32)cmd.Parameters[1].Value;
  
    dr.Close();
}

The generated CALL statement will not contain the RETURNS clause, since the desired return type is the same data type as the first IN parameter (Integer).

CALL xspAnyType01(?,?);

If the desired OUT parameter returned data type is different than the IN parameter data type when both parameters are defined as TD_ANYTYPE parameters, then the OUT parameter desired data type must be defined.  The TD_ANYTYPE OUT parameters will default to the same data type of the first IN TD_ANYTYPE parameter. While the CommandType is CommandType.StoredProcedure, the TdParameter.SecondaryTdType must be defined for the OUT parameter.  This new property has been added to support TD_ANYTYPE parameters in Teradata Database release 14.0. The TdParameter.TdType must be set to TdType.AnyType. This enables the provider to support defining the OUT parameter data type. The following example illustrates setting the OUT parameter data type to VARCHAR(20).

//Procedure xspAnyType01(IN param1 TD_ANYTYPE, OUT result1 TD_ANYTYPE)
public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "xspAnyType01";
    cmd.Parameters.Add(null, TdType.Integer);
    cmd.Parameters[0].Value = 4;

    cmd.Parameters.Add(null, TdType.AnyType);
    cmd.Parameters[1].Direction = ParameterDirection.Output;
    cmd.Parameters[1].SecondaryTdType = TdType.VarChar;
    cmd.Parameters[1].Size = 20;
 
    TdDataReader dr = cmd.ExecuteReader();
    dr.Read();

    // Going to process data of output parameter
    String result = (String)cmd.Parameters[1].Value;

    dr.Close();
}

The generated CALL statement will contain the RETURNS clause since the desired OUT parameter data type is different than the first IN parameter data type. The TdParameter.SecondaryTdType indicates the desired OUT parameter data type, and the TdParameter.TdType (TdType.AnyType) indicates to the provider that this stored procedure parameter will be defined to support TD_ANYTYPE.

CALL xspAnyType01(?,? RETURNS VARCHAR(20))

All OUT parameter signatures (of TD_ANYTYPE) that are generated by the data provider must define all applicable properties for the required data type.  For example VARCHAR requires a size.  The generated CALL statement gives the programmer insight into why the size property is required for VARCHAR - CALL xspAnyType01(?, ? RETURNS VARCHAR(20)). Without the size, the VARCHAR type cannot be fully defined. 

Depending upon the desired result data type, there are additional TdParameter properties required to generate the CALL statement in the command. For example TdDecimal(n,m) requires additionally precision and scale, unless the default is sufficient. See the table below for required properties and default values.


Data Type Parameter Information For Generating TD_ANYTPE CALL Statements
Teradata Data TypeSizePrecisionScaleDefault Value
Decimal(n)N/A1 - 380 - 38Decimal (n,0)
Decimal(n,m)N/A1 - 380 - 38Decimal (5,0)
Blob(n)1- 2097088000 (NOTE 1)N/AN/ANONE
Clob(n)1- 2097088000 (NOTE 1)N/AN/ANONE
Char(n)1 - 64000 (NOTE 1)N/AN/ANONE
VarChar(n)1 - 64000 (NOTE 1)N/AN/ANONE
Byte(n)1 - 64000N/AN/ANONE
VarByte(n)1 - 64000N/AN/ANONE
Graphic(n)1 - 32000N/AN/ANONE
VarGraphic(n)1 - 32000N/AN/ANONE
Time(s)N/AN/A0 - 6Time(6)
Time With Time Zone(s)N/AN/A0 - 6Time(6) With Time Zone
Timestamp(s)N/AN/A0 - 6Timestamp(6)
Timestamp With Time Zone(s)N/AN/A0 - 6Timestamp(6) With Time Zone
Interval Day(p)N/A1 - 4N/AInterval Day(2)
Interval Day(p) To HourN/A1 - 4N/AInterval Day(2) To Hour
Interval Day(p) To MinuteN/A1 - 4N/AInterval Day(2) To Minute
Interval Day(p) To Second(s)N/A1 - 40 - 6Interval Day(2) To Second(6)
Interval Hour(p)N/A1 - 4N/AInterval Hour(2)
Interval Hour(p) To MinuteN/A1 - 4N/AInterval Hour(2) To Minute
Interval Hour(p) To Second(s)N/A1 - 40 - 6Interval Hour(2) To Second(6)
Interval Minute(p)N/A1 - 4N/AInterval Minute(2)
Interval Minute(p) To Second(s)N/A1 - 40 - 6Interval Minute(2) To Second(6)
Interval Second(p,s)N/A1 - 40 - 6Interval Second(2,6)
Interval Year(p)N/A1 - 4N/AInterval Year(2)
Interval Month(p)N/A1 - 4N/AInterval Month(2)
Interval Year(p) To MonthN/A1 - 4N/AInterval Year(2) To Month

NOTE 1: Lengths are specified using LATIN Character set

Generated CALL statements do not support the optional RETURNS STYLE clause or the CHARACTER SET clause in character data types. Application defined CALL statements may be used with restrictions.  Please see below for more details.

  1. Application Defined CALL Statements in Stored Procedures

    If the OUT TD_ANYTYPE parameter data type differs from the IN TD_ANYTYPE parameter data type, or if there is no IN TD_ANYTYPE parameter as in this example, the RETURN data type must be specified in the RETURNS clause. The TdCommand.Text must include the returned data type in the RETURNS clause. The external stored procedure xspAnyType02 code is shown below.
#define SQL_TEXT Latin_Text 
#include <sqltypes_td.h>
#include <string.h>

void xspAnyType02(int *input1,
               void    *result1,
               int      *indc_input1,
               int      *indc_result,
               char     sqlstate[6],
               SQL_TEXT extname[129],
               SQL_TEXT specific_name[129],
               SQL_TEXT error_message[257])
{
 /* Procedure definition */
 /* xspAnyType02(IN param1 INTEGER, OUT result1 TD_ANYTYPE) */

 anytype_param_info_t paraminfo[1];
 int numunk;

 FNC_GetAnyTypeParamInfo(1*sizeof(anytype_param_info_t),&numunk,paraminfo);
   
 /* Process the value based upon the data type */
  
 if((paraminfo[0].datatype==DECIMAL4_DT ) &&
     (paraminfo[0].direction ==OUT_PM))
 {
  *(DECIMAL4 *) result1 = 5412389;
 }
  }

If the requested OUT parameter type is DECIMAL(8,3), then the following example illustrates defining the proper return clause and OUT parameter definition. Note the CommandText contains a RETURNS DECIMAL(8,3). From the SQL External Routine Programming manual (Chapter 8 SQL Data Type Mapping), a Decimal(8,3) corresponds to a DECIMAL4 C type. XspAnyType02 detects the TD_ANYTYPE OUT parameter type from the new function - FNC_GetAnyTypeParamInfo.  This new function contained within the Teradata C library provides the developer with the capability of retrieving information regarding all TD_ANYTYPE parameters.

// xspAnyType02(IN param1 INTEGER, OUT result1 TD_ANYTYPE)
public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "CALL xspAnyType02 (?,? RETURNS DECIMAL(8,3))";
    cmd.Parameters.Add(null, TdType.Integer);
    cmd.Parameters[0].Value = 4;

    cmd.Parameters.Add(null, TdType.Decimal);
    cmd.Parameters[1].Direction = ParameterDirection.Output;
    cmd.Parameters[1].Precision = 8;
    cmd.Parameters[1].Scale = 3;
 
    TdDataReader dr = cmd.ExecuteReader();
    dr.Read();

    // Going to process data of output parameter
    TdDecimal result = (TdDecimal)cmd.Parameters[1].ProviderSpecificValue;

    dr.Close();
}

  1. Best Practices for RETURNS STYLE and CHARACTER SET

    Generated CALL statements do not support the optional RETURNS STYLE clause or the CHARACTER SET clause in character data types.  However, they are supported composing the CALL statement text by the application. The restriction holds that only TERADATA session modes are supported, due the limitation of ANSI session modes and the appending of a COMMIT to the request.  Teradata requires the CALL statement to be the last statement in a multi-statement request.

Using the external stored procedure definition for xspAnyType03 (shown in the code comments), the following example illustrates the use of the RETURNS STYLE clause. The RETURNS STYLE <COLUMN EXPRESSION> allows the return type to be specified based upon the type of a column. The column expression may be any valid table or view coumn reference.  As stated previously, both the RETURNS / RETURNS STYLE clauses are not mandatory as long as there is an input parameter of TD_ANYTYPE. The first defined IN/INOUT parameter data type will serve as the default TD_ANYTYPE OUT parameter data type.  If the CHARACTER SET is not specified in the RETURNS clause for character types, then the default session character type setting will be utilized.

// Table Definition SampleTable (col1 VARCHAR(20) CHARACTER SET LATIN, col2 DECIMAL(20,5))
// Procedure Definition xspAnyType03(IN param1 INTEGER, OUT result1 TD_ANYTYPE, OUT result2 TD_ANYTYPE)
public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{
   
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "CALL xspAnyType03 (?,? RETURNS STYLE SampleTable.col2, ? RETURNS STYLE SampleTable.col1)";

    cmd.Parameters.Add(null, TdType.Integer);
    cmd.Parameters[0].Value = 4;

    cmd.Parameters.Add(null, TdType.Decimal);
    cmd.Parameters[1].Direction = ParameterDirection.Output;
    cmd.Parameters[1].Precision = 20;
    cmd.Parameters[1].Scale = 5;
   
    cmd.Parameters.Add(null, TdType.VarChar);
    cmd.Parameters[2].Direction = ParameterDirection.Output;
    cmd.Parameters[2].Size = 20;
 
    TdDataReader dr = cmd.ExecuteReader();

    // Going to process data of output parameters
    TdDecimal decimalResult = (TdDecimal)cmd.Parameters[1].ProviderSpecificValue;
    String stringResult = (String)cmd.Parameters[2].Value;

    dr.Close();
}

For more information on TD_ANYTYPE details, please see "Parameter Data Types" in SQL Data Types and Literals, "CREATE FUNCTION" in SQL Data Definition Language, "CREATE METHOD" in SQL Data Definition Language, "CREATE PROCEDURE (External Form)" in SQL Data Definition Language and SQL External Routine Programming for writing routines that use TD_ANYTYPE parameters.