C# INSERT ERROR Invalid Operation for DateTime or Interval

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.
Enthusiast

C# INSERT ERROR Invalid Operation for DateTime or Interval

I'm trying to perform an insert and return the key for the newly-inserted record in c# using an interval. The macro is as follows:

 

REPLACE MACRO my_macro(
name varchar(100)
,user_id varchar(40)
,template_id int
,freq varchar(20)
,freq_2 int
,freq_3 byteint
,dur timestamp(0)
,dur_2 timestamp(0)
,dur_3 int
)
AS
(
BT;
INSERT INTO RESEARCH_TABLES_DEV.TV_SUBSCRIPTION
(
id
name 
,user_id 
,template_id 
,freq 
,freq_
,freq_3
,dur 
,dur_2 
,dur_3 
,created_dttm
,updated_dttm
)
SELECT
nvl((sel max(id from **bleep**.mytable),0)+1 id
,:name
,:user_id
,:template_id
,:freq_
,:freq_2
,:freq_3
,:dur
,:dur_2
,:dur_3
,current_timestamp(6)
,current_timestamp(6);
SELECT nvl((sel max(id) from **bleep**.mytable),0) id;
ET;
);

 

The table shcema is :

CREATE MULTISET TABLE **bleep**.mytable ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER NOT NULL,
name VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
user_id VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
template_id INTEGER,
freq VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
freq_2s INTEGER,
freq_3 BYTEINT,
dur TIMESTAMP(0),
dur_2 TIMESTAMP(0),
dur_2 INTEGER,
created_dttm TIMESTAMP(6),
updated_dttm TIMESTAMP(6))
UNIQUE PRIMARY INDEX PI_TV_SUBSCRIPTION (id );

 

and my c#

                using (var con = new TdConnection(xx))
                {
                    con.Open();
                    var strSql = QueryConstants.AddSubscription + "(?,?,?,?,?,?,?,?,?)";
                    var cmd = new TdCommand(strSql,con);
                    cmd.CommandType = CommandType.Text;
                    var name = new TdParameter("name", TdType.VarChar) { Value = sub.Name };
                    cmd.Parameters.Add(name);
                    var userid = new TdParameter("user_id", TdType.VarChar) { Value = sub.UserId.ToString() };
                    cmd.Parameters.Add(userid);
                    var template_id = new TdParameter("template_id", TdType.Integer) { Value = 1 };
                    cmd.Parameters.Add(template_id);
                    var freq_3 = new TdParameter("freq_3", TdType.ByteInt) { Value = sub.ResendEveryWeekdayFrequency?1:0 };
                    cmd.Parameters.Add(freq_3);
                    var startTime = new TdParameter();
                    startTime.ParameterName = "dur";
                    startTime.TdType = TdType.Timestamp;
                    startTime.Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                    cmd.Parameters.Add(startTime);
                    var endTime = new TdParameter();
                    endTime.TdType = TdType.Timestamp;
                    endTime.Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                    endTime.ParameterName = "dur_2";
                    cmd.Parameters.Add(endTime);
                    var freq = new TdParameter("freq, TdType.VarChar);
                    freq.Value = sub.SendFrequency;
                    cmd.Parameters.Add(freq);
                    var freq_2 = new TdParameter("freq_2", 1);
                    freq_2.Value = 1;
                    cmd.Parameters.Add(freq_2);
                    var dur_3 = new TdParameter("dur_3", TdType.Integer);
                    dur_3.Value = 1;
                    cmd.Parameters.Add(dur_3);

                    //cmd.Prepare();
                    var subscription_id = cmd.ExecuteScalar();
                    }
                }

"sub" is just an object with the corresponding data types. When I run it I get the " Invalid Operation for DateTime or Interval" error. I'e followed many diferent exmaples (some from the documentation) on how to setup the datetime variables and nothing has worked. Please help. Thanks. 

1 REPLY
Highlighted
Teradata Employee

Re: C# INSERT ERROR Invalid Operation for DateTime or Interval

Parameter substitution is positional, so you need to add TdParameter members to the TdParameterCollection in the proper order.

 

Note also that your macro currently does not return anything.