Send feedback on this topic.
Teradata.Client.Provider
External Stored Procedures And TD_ANYTYPE Parameters
.NET Data Provider for Teradata > Developer's Guide > Working with the Teradata Database Features > Working with Stored Procedures > External Stored Procedures And TD_ANYTYPE Parameters

External Stored Procedures and TD_ANYTYPE Parameters

Support for a new dynamically defined parameter type (TD_ANYTYPE) is available in Teradata Database 14.0. By defining an external stored procedure with a TD_ANYTYPE parameter, a desired parameter type may be defined at execution time of the external stored procedure. This new data type supports the following types:

The following guidelines are presented for calling external stored procedures from the .NET Data Provider that contain TD_ANYTYPE parameters. These guidelines will be discussed - Defining TD_ANYTYPE Parameter Direction, Defining TD_ANYTYPE IN Parameters, Using CommandType.StoredProcedure Inferred Parameters, Using CommandType.StoredProcedure Explicit Parameters and Using RETURNS STYLE or CHARACTER SET Clauses.

  1. Defining TD_ANYTYPE Parameter Direction

    TD_ANYTYPE parameters must be declared as IN, INOUT or OUT parameters. For example this procedure contains an IN and an OUT TD_ANYTYPE parameter -

    REPLACE PROCEDURE xspAnyType01(IN param1 TD_ANYTYPE, OUT result1 TD_ANYTYPE) ...

  2. Defining TD_ANYTYPE IN Parameters

    The TdParameter.TdType must be set to the TdType that describes the parameter. For example, if an integer value is to be passed to the TD_ANYTYPE parameter, this property must be set to TdType.Integer. If the TdType is set to TD_ANYTYPE, the value of the parameter will be used to determine the TdType.

  3. Using CommandType.StoredProcedure Inferred OUT Parameter Types

    TD_ANYTYPE result parameter data types will by default be defined as the first TD_ANYTYPE input parameter data type (if exists). For example given the following stored procedure definition:

    REPLACE PROCEDURE xspAnyType01(IN param1 TD_ANYTYPE, OUT result1 TD_ANYTYPE) ...

    Since an input TD_ANYTYPE parameter exists, the result TD_ANYTYPE data type will default as the same data type of param1 defined in the application.

    When setting the Command.CommandType to CommandType.StoredProcedure, the CALL statement is generated by the provider. But since the default TD_ANYTYPE result data type definition will be used, no additional TD_ANYTYPE clauses will be generated by the provider to define the result parameter.

    In the example below, the result data type and the input parameter type are both SmallInt. Therefore the default behavior of the Advanced SQL Engine assigns the TD_ANYTYPE result data type to the first input TD_ANYTYPE data type. The generated CALL statement from the code example below is:

    CALL xspAnyType01(?,?);

    No additional TD_ANYTYPE clauses (RETURNS or RETURNS STYLE) are generated from this example (The next guideline describes the TD_ANYTYPE RETURNS clause). All TdParameter properties shown in the example are not specific to TD_ANYTYPE parameter data types.

    C#
    Copy Code 
    public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.CommandText = "xspAnyType01";
   cmd.Parameters.Add(null, TdType.SmallInt);
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   cmd.Parameters[0].Value = 4;

   cmd.Parameters.Add(null, TdType.SmallInt);
   cmd.Parameters[1].Direction = ParameterDirection.Output;
   using (TdDataReader dr = cmd.ExecuteReader())
   {
      // Going to process data of output parameter
      Int16 result = (Int16)cmd.Parameters[1].Value;
   }
}

  4. CommandType.StoredProcedure Explicit OUT Parameter Declaration

    Given the following 2 definitions and again setting the CommandType to CommandType.StoredProcedure to generate the CALL statement automatically:

    REPLACE PROCEDURE xspAnyType01(IN param1 TD_ANYTYPE, OUT result1 TD_ANYTYPE) ...

    REPLACE PROCEDURE xspAnyType02(IN param2 INTEGER, OUT result2 TD_ANYTYPE) ...

    In the first external stored procedure xspAnyType01, if the desired result1 parameter data type is different than the default defined OUT parameter type, the OUT parameter TdParameter.TdType must be defined as TdType.AnyType. The TdParameter.SecondaryTdType must be defined as the desired returned TdType. This new property and new TdType have been added to support defining TD_ANYTYPE parameters in the generated CALL statement by the provider. The example below - ExampleTdAnyTypeExplicitParameters, illustrates the use of the new TdType and the new property.

    C#
    Copy Code 
    public static void ExampleTdAnyTypeExplicitParameters(TdCommand cmd)
{
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.CommandText = "xspAnyType01";
   cmd.Parameters.Add(null, TdType.Integer);
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   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;

   using (TdDataReader dr = cmd.ExecuteReader())
   {
      // Going to process data of output parameter
      String result = (String)cmd.Parameters[1].Value;
   }
}

    In generating the CALL statement the RETURNS clause will be appended to OUT parameter placeholder. The generated CALL statement for the TdAnyTypeExplicitParameters example is shown below:

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

    In the second external stored procedure xspAnyType02, there is no IN/INOUT TD_ANYTYPE parameter. The programmer must define the result2 TdParameter.TdType as TdType.AnyType and must further define the TdParameter.SecondaryTdType as the desired returned TdType. In generating the CALL statement the RETURNS clause will be appended to the placeholder for the OUT parameter. The example below illustrates the required parameter properties for a decimal return data type.

    C#
    Copy Code 
    public static void ExampleTdAnyTypeExplicitDecimalParameters(TdCommand cmd)
{
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.CommandText = "xspAnyType02";
   cmd.Parameters.Add(null, TdType.Integer);
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   cmd.Parameters[0].Value = 4;

   cmd.Parameters.Add(null, TdType.AnyType);
   cmd.Parameters[1].Direction = ParameterDirection.Output;
   cmd.Parameters[1].SecondaryTdType = TdType.Decimal;
   cmd.Parameters[1].Precision = 20;
   cmd.Parameters[1].Scale = 5;

   using (TdDataReader dr = cmd.ExecuteReader())
   {
      // Going to process data of output parameter
      TdDecimal result = (TdDecimal)cmd.Parameters[1].ProviderSpecificValue;
   }
}

    In generating the CALL statement the RETURNS clause will be appended to the placeholder for the OUT parameter. The generated CALL statement for the TdAnyTypeExplicitDecimalParameters example is shown below:

    CALL xspAnyType02(?, ? RETURNS DECIMAL(20,5)).

    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. TdDecimal(n) only requires precision. See the table below for required properties:

    TdType Size Precision Scale Default Value
    Decimal(n,m) N/A 1 - 38 0 - 38 Decimal (5,0)
    Decimal(n) N/A 1 - 38 0 - 38 Decimal (n,0)
    Blob(n) 1- 2097088000 (NOTE 1) N/A N/A NONE
    Clob(n) 1- 2097088000 (NOTE 1) N/A N/A NONE
    Char(n) 1 - 64000 (NOTE 1) N/A N/A NONE
    VarChar(n) 1 - 64000 (NOTE 1) N/A N/A NONE
    Byte(n) 1 - 64000 N/A N/A NONE
    VarByte(n) 1 - 64000 N/A N/A NONE
    Graphic(n) 1 - 32000 N/A N/A NONE
    VarGraphic(n) 1 - 32000 N/A N/A NONE
    Time(s) N/A N/A 0 - 6 Time(6)
    Time(s) With Time Zone N/A N/A 0 - 6 Time(6) With Time Zone
    Timestamp(s) N/A N/A 0 - 6 Timestamp(6)
    Timestamp(s) With Time Zone N/A N/A 0 - 6 Timestamp(6) With Time Zone
    Interval Day(p) N/A 1 - 4 N/A Interval Day(2)
    Interval Day(p) To Hour N/A 1 - 4 N/A Interval Day(2) To Hour
    Interval Day(p) To Minute N/A 1 - 4 N/A Interval Day(2) To Minute
    Interval Day(p) To Second(s) N/A 1 - 4 1- 6 Interval Day(2) To Second(6)
    Interval Hour(p) N/A 1 - 4 N/A Interval Hour(2)
    Interval Hour(p) To Minute N/A 1 - 4 N/A Interval Hour(2) To Minute
    Interval Hour(p) To Second(s) N/A 1 - 4 0 - 6 Interval Hour(2) To Second(6)
    Interval Minute(p) N/A 1 - 4 N/A Interval Minute(2)
    Interval Minute(p) To Second(s) N/A 1 - 4 0 - 6 Interval Minute(2) To Second(6)
    Interval Second(p,s) N/A 1 - 4 0 - 6 Interval Second(2,6)
    Interval Year(p) N/A 1 - 4 N/A Interval Year(2)
    Interval Month(p) N/A 1 - 4 N/A Interval Month(2)
    Interval Year(p) To Month N/A 1 - 4 N/A Interval Year(2) To Month

  5. Using RETURNS STYLE and CHARACTER SET Clauses

    In CommandType.StoredProcedure, the RETURNS CHARACTER SET character set and the RETURNS STYLE column expression is not supported. We do not support this in the generation of the CALL statement. However, by using the TdCommand.CommandType set to  CommandType.Text, both of these new clauses are supported as SQL. The existing restriction of executing stored procedures using TdCommandType.Text while the Session Mode is ANSI still applies, due to the limitation of ANSI session modes and the appending of a COMMIT to the request.

    Using the external stored procedure definition for xspAnyType03 (shown in the ExampleTdAnyTypeReturnsStyle example code comments), the following example illustrates the use of the RETURNS STYLE clause with the CHARACTER SET 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 column reference.

    As stated previously, both the RETURNS and RETURNS STYLE clauses are not mandatory as long as there is an input parameter of TD_ANYTYPE. The first defined input 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, the default session character type setting will be utilized.

    C#
    Copy Code 
    //
// 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 ExampleTdAnyTypeReturnsStyle(TdCommand cmd)
{
   cmd.CommandType = CommandType.Text;

   cmd.CommandText = "CALL xspAnyType03 (?,? RETURNS STYLE SampleTable.col1, RETURNS STYLE SampleTable.col2";
   cmd.Parameters.Add(null, TdType.Integer);
   cmd.Parameters[0].Direction = ParameterDirection.Input;
   cmd.Parameters[0].Value = 4;

   cmd.Parameters.Add(null, TdType.VarChar);
   cmd.Parameters[1].Direction = ParameterDirection.Output;
   cmd.Parameters[1].Size = 20;

   cmd.Parameters.Add(null, TdType.Decimal);
   cmd.Parameters[2].Direction = ParameterDirection.Output;
   cmd.Parameters[2].Precision = 20;
   cmd.Parameters[2].Scale = 5;

   using (TdDataReader dr = cmd.ExecuteReader())
   {
      // Going to process data of output parameter
      String stringResult = (String) cmd.Parameters[1].Value;
      TdDecimal decimalResult = (TdDecimal)cmd.Parameters[2].ProviderSpecificValue;
   }
}

    The RETURNS or RETURNS STYLE clause is not mandatory as long as the procedure also includes a TD_ANYTYPE input parameter. If you do not specify a RETURNS or RETURNS STYLE clause, then the data type of the first TD_ANYTYPE IN or INOUT argument is used to determine the return type of the OUT parameter.

See Also

Stored Procedure Guidelines (see ANSI session mode restriction)

TdParameter.Size

TdParameter.Value

TdParameter.TdType

SQL Data Types and Literals

SQL Data Definition Language

SQL External Routine Programming

 

 

© 2005-2019 Teradata. All rights reserved.