OdbcParameter TIMESTAMP in .Net C# problem

Connectivity

OdbcParameter TIMESTAMP in .Net C# problem

I am trying to call Stored Procedure with parameters. Procedure have two TIMESTAMP parameters for example use below one:

REPLACE PROCEDURE tsTestInOut
(
IN pIN TIMESTAMP,
OUT pOUT TIMESTAMP
)
BEGIN
BEGIN
select cast(timestamp '2008-06-24 01:02:33' as timestamp(6)) into pOUT;
END;
END;

One IN and one OUT.

While executing CALL statement CALL tsTestInOut(?,?);  with binded parameters I am getting strange exception:

ERROR [22018] [Teradata][ODBC Teradata Driver][Teradata Database] Conversion between BYTE data and other types is illegal.

This exception occures only if I am calling procedure with TIMESTAMP input parameter. Whenever I have only Output TIMESTAMPS everything works ok.

Code used to show this problem below [C#]:

OdbcConnectionStringBuilder s = new OdbcConnectionStringBuilder();
s.Dsn = "10.10.10.78";
s.Add("Uid", "user");
s.Add("Pwd", "passwd");
OdbcConnection conn = new OdbcConnection(s.ConnectionString);
conn.Open();

if (conn.State == ConnectionState.Open)
{
OdbcCommand command = new OdbcCommand();

command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.CommandText = "CALL tsTestInOut(?,?); ";
command.UpdatedRowSource = UpdateRowSource.OutputParameters;

OdbcParameter param = new OdbcParameter();
param.Direction = ParameterDirection.Input;
param.OdbcType = OdbcType.Timestamp;
param.ParameterName = "pIN";

DateTime dt = new DateTime(2012, 5, 6, 1, 2, 50);
string dts = dt.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
param.Value = System.Text.ASCIIEncoding.ASCII.GetBytes(dts);
param.Size = 26;

command.Parameters.Add(param);

OdbcParameter parOut = new OdbcParameter();
parOut.Direction = ParameterDirection.Output;
parOut.OdbcType = OdbcType.Timestamp;
parOut.ParameterName = "pOUT";
parOut.Size = 26;

command.Parameters.Add(parOut);

command.ExecuteNonQuery();

for (int paramIndex = 0; paramIndex < command.Parameters.Count; paramIndex++)
{
OdbcParameter p = (OdbcParameter)command.Parameters[paramIndex];
byte[] byteTab = (byte[])p.Value;
string stringTab = System.Text.ASCIIEncoding.ASCII.GetString(byteTab);
DateTime dateFromTab = new DateTime();
if (DateTime.TryParse(stringTab, out dateFromTab))
{
//return dateFromTab;
}
}

conn.Close();
}

So, can anyone tell me how to use TIMESTAMP parameters in Teradata on ODBC driver and C#?

I can't find any good examples in the internet or documentation. I have also tryied to use something similar to structures used in C++. Not working.

2 REPLIES
Teradata Employee

Re: OdbcParameter TIMESTAMP in .Net C# problem

Why do you convert the DateTime to String and then use ASCII encoding to Get the Bytes? This explains the error.

If the ".NET Data Provider for ODBC" is anything like the ".NET Data Provider for Teradata" then you simply

OdbcParameter param = new OdbcParameter();             
param.Direction = ParameterDirection.Input;            
param.OdbcType = OdbcType.Timestamp;            
param.ParameterName = "pIN";              
param.Value = new DateTime(2012, 5, 6, 1, 2, 50);
param.Size = 26;

You can download the .NET Data Providre for Teradata from http://downloads.teradata.com/download/connectivity/dot-net-data-provider.

Re: OdbcParameter TIMESTAMP in .Net C# problem

In ODBC this is not a solution. When your parameter type is TIMESTAMP then expected DBTYPE is byte[]. This comes from ODBC specification.

Passing DateTime object in param.Value leads to exception:

Failed to convert parameter value from a DateTime to a Byte[].