DECIMAL OUT parameter lost value after SP CALL when after VARCHAR IN parameter

Connectivity
Enthusiast

DECIMAL OUT parameter lost value after SP CALL when after VARCHAR IN parameter

I have strange problem. Consider below Teradata Stored Procedure:

REPLACE PROCEDURE OutParam
(
IN PIN VARCHAR(79),
OUT POUT DECIMAL(38),
OUT POUT2 DECIMAL(38)
)
OutParam:
BEGIN
SET POUT = 4;
SET POUT2 = 77;
END;

Note that there are 3 parameters where one of OUT DECIMAL parameters is preceded by IN VARCHAR parameter - it's important.

Now I am trying to call this kind of procedure in C# using ODBC driver (ver. 13.10.00.02) in this manner:

//Building connection string
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Driver = "Teradata";
builder.Add("UID", "user");
builder.Add("PWD", "tajnehaslo");
builder.Add("TDMSTPortNumber", 1025);
builder.Add("DBCName", "10.235.176.78");
builder.Add("SessionMode", "ANSI");

//Connecting to Teradata server
OdbcConnection connection = new OdbcConnection(builder.ConnectionString);
onnection.Open();

//Calling OutParam Stored Procedure
using (OdbcCommand cmd = new OdbcCommand("",connection))
{
cmd.CommandText = "CALL OutParam(?,?,?);";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 15;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;

OdbcParameter param1 = new OdbcParameter();
param1.ParameterName = "PIN";
param1.Direction = ParameterDirection.Input;
param1.OdbcType = OdbcType.VarChar;
param1.Value = "a";
cmd.Parameters.Add((param1));

OdbcParameter param2 = new OdbcParameter();
param2.ParameterName = "POUT";
param2.Direction = ParameterDirection.Output;
param2.OdbcType = OdbcType.Decimal;
param2.Precision = 10;
param2.Value = 10;
cmd.Parameters.Add((param2));

OdbcParameter param3 = new OdbcParameter();
param3.ParameterName = "POUT2";
param3.Direction = ParameterDirection.Output;
param3.OdbcType = OdbcType.Decimal;
param3.Precision = 10;
cmd.Parameters.Add((param3));

cmd.ExecuteNonQuery();

//Displaying the returne parameters on console
Console.WriteLine("PIN = {0}", param1.Value.ToString());

if(param2.Value == null)
Console.WriteLine("POUT = null");
else if(DBNull.Value.Equals(param2.Value))
Console.WriteLine("POUT = DBNull");
else Console.WriteLine("POUT = {0}", param2.Value.ToString());

if (param3.Value == null)
Console.WriteLine("POUT2 = null");
else if (DBNull.Value.Equals(param3.Value))
Console.WriteLine("POUT2 = DBNull");
else Console.WriteLine("POUT2 = {0}", param3.Value.ToString());
}

As a result I'am getting

PIN = a
POUT = DBNull
POUT2 = 77

So, it looks like POUT parameter (first one after VARCHAR IN parameter) doesn't returned expected value = 4.

All the time DECIMAL parameter defined after VARCHAR parameter are set to DBNull's.

The only way to resolve this problem is to change order of parameters inserted into cmd.Parameters collection. IN parameters must be defined at the and. When used different order then everything works. Problem occurs only for pair of parameters IN VARCHAR and OUT DECIMAL. When I changed DECIMAL to INTEGER everything is working fine. Probably there is a bug in ODBC driver. I have tested this on multiple ODBC drivers for teradata (13.10.00.02, 13.10.00.05, 13.00.00.??) and bug exists on every !

When I tested this using ODBC setting named Return Outputs Parameters As resultSet=true and using ExecuteReader instead ExecuteNonQuery then I can read proper values from DataReader but all binded OUT parameters have Value set to null (not DBNull). In this situation order of parameters in cmd.Parameters collection is not important. but this is not solution for me.

Am I doing something wrong or is it just bug in ODBC driver?

1 REPLY
Teradata Employee

Re: DECIMAL OUT parameter lost value after SP CALL when after VARCHAR IN parameter

I tried it with the .NET Data Provider for Teradata

            //Building connection string 
            TdConnectionStringBuilder builder = new TdConnectionStringBuilder();
            builder.DataSource = "X";
            builder.UserId = "Y";
            builder.Password = "Z";
           
            //Connecting to Teradata server
            TdConnection connection = new TdConnection(builder.ConnectionString);
            connection.Open();  
           
            //Calling OutParam Stored Procedure
            using (TdCommand cmd = new TdCommand("",connection))
            {              
                cmd.CommandText = "OutParam";               
                cmd.CommandType = CommandType.StoredProcedure;               
                cmd.CommandTimeout = 15;               
                cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;                 
               
                TdParameter param1 = new TdParameter();               
                param1.ParameterName = "PIN";               
                param1.Direction = ParameterDirection.Input;                
                param1.TdType = TdType.VarChar;               
                param1.Value = "a";               
                cmd.Parameters.Add((param1));                 
               
                TdParameter param2 = new TdParameter();               
                param2.ParameterName = "POUT";               
                param2.Direction = ParameterDirection.Output;               
                param2.TdType = TdType.Decimal;               
                param2.Precision = 10;
                param2.Value = 10;               
                cmd.Parameters.Add((param2));                 
               
                TdParameter param3 = new TdParameter();               
                param3.ParameterName = "POUT2";               
                param3.Direction = ParameterDirection.Output;               
                param3.TdType = TdType.Decimal;               
                param3.Precision = 10;               
                cmd.Parameters.Add((param3));                 
               
                cmd.ExecuteNonQuery();  
               
                //Displaying the returne parameters on console               
                Console.WriteLine("PIN = {0}", param1.Value.ToString());                 

                if(param2.Value == null)                  
                    Console.WriteLine("POUT = null");               
                else if(DBNull.Value.Equals(param2.Value))                  
                    Console.WriteLine("POUT = DBNull");               
                else Console.WriteLine("POUT = {0}", param2.Value.ToString());                 
               
                if (param3.Value == null)                  
                    Console.WriteLine("POUT2 = null");               
                else if (DBNull.Value.Equals(param3.Value))                  
                    Console.WriteLine("POUT2 = DBNull");               
                else Console.WriteLine("POUT2 = {0}", param3.Value.ToString());
            }

It works with the .NET Data Provider for Teradata but not "Microsoft .NET Data Provider for ODBC + ODBC Driver".

I suggest that you turn on ODBC TRACE; and see if you can gather additional information.