.NET and using Parameters

Connectivity
Fan

.NET and using Parameters

Ok where do I begin? Basically I have my queries for a report stored in an XML file. I pass in the xml and parse it out to get the query that I want to run. I need to use parameters to pass in dates dynamically as this is a reoccuring report. What I cannot get is how do deal with parameters in teradata? Can I use named parameters?

In oracle I use :endDate and I thought in Teradata I used @endDate but this is not working.....

Code:

When I tried to fill the dataadapter I get the following error....

[Teradata Database] [3939] There is a mismatch between the number of parameters specified and the number of parameters required.

Now this is the same exact way as how I put in parameters in an Oracle query...what am I doing wrong or missing?
5 REPLIES
Teradata Employee

Re: .NET and using Parameters


I am not aware much .NET, but I believe you need to you use "?" as parameter marker

Fan

Re: .NET and using Parameters

I tried ?endDate and the error is as so....

[Teradata Database] [3706] Syntax error: expected something between '?' and the word 'endDate'.

I understand that ? is for unnamed parameters but then how do I add them via the add method of

I am going to try just using a ? and then adding the parameters in order, but hopefully someone has an easy way to use named parameters.
Teradata Employee

Re: .NET and using Parameters

.NET Data Provider for Teradata (also ODBC Driver for Teradata) support parameter markers (question mark). They do not support named parameters.

For example:

class Program
{
public static void Main(String[] args)
{
TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder();
conStrBuilder.DataSource = "Foo";
conStrBuilder.UserId = "x";
conStrBuilder.Password = "y";

using (TdConnection cn = new TdConnection(conStrBuilder.ConnectionString))
{
cn.Open();

TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "Select * from DBC.Columns where TableName = ? ";
cmd.Parameters.Add("TableName", TdType.VarChar);
cmd.Parameters["TableName"].Value = "Users";

using (TdDataReader reader = cmd.ExecuteReader())
{
Int32 columnNameOrdinal = reader.GetOrdinal("ColumnName");

while (reader.Read())
{
Console.WriteLine("Column Name is: {0}", reader.GetString(columnNameOrdinal));
}
}
}
}
}

Also see the documentation for TdCommand.Parameters property. It also has a Example.
Fan

Re: .NET and using Parameters

Shaw so to do multiple parameter values I would use multiple ?'s and add them in order?
Teradata Employee

Re: .NET and using Parameters

Yes, add TdParameter objects in order, corresponding to the Question marks in SQL Text, to the TdParameterCollection object (TdCommand.Parameters property).