Cannot get stored procedure call from C# with output parameters to work

Connectivity

Cannot get stored procedure call from C# with output parameters to work

Hi, I'm new to posting to this forum, so please forgive any procedural/protocol issues...

I'm trying to make a call to a stored procedure from a C# program using the ODBC drivers (TTU version 8.2). I found a post at this location http://www.teradataforum.com/teradata/20040329_110933.htm that I'm using as an example. I could not get my modified version to work, so gave up and copied their example exactly (including the creation of the TestProc stored procedure) yet get the same error messages. When the code reaches the line

OdbcDataReader r1 = sampleCMD.ExecuteReader();

I get a dialog pop-up indicating: "The LAN message RequestNo field is invalid".
Followed by the exception error message
{"ERROR [HY000] [NCR][ODBC Teradata Driver] Stored procedure parameter markers are not bound."}

Is it the code, or my system (i.e., some incompatibility with the .NET version I'm using? I had recently migrated the code from Visual STudio 2003 to 2005), though this is brand new functionality I'm coding in. If I could get confirmation that the example code from the forum post should/does actually work, that would be a big step. Or, another example/method for me to try would also be appreciated.

thanks

Kevin Hibray

7 REPLIES
Teradata Employee

Re: Cannot get stored procedure call from C# with output parameters to work

You must enable "Return Output Parameters as ResultSet" DSN Option for that code to work.

Following version returns an output parameter:

string myConnection = "DSN=x;UID=y;PWD=z";
OdbcConnection myConn = new OdbcConnection(myConnection);

string myTestQuery = "call TestProc(?,?)";

OdbcCommand sampleCMD = new OdbcCommand(myTestQuery);
sampleCMD.Connection = myConn;
sampleCMD.CommandType = CommandType.StoredProcedure;

OdbcParameter prm = sampleCMD.Parameters.Add("pIn", OdbcType.Int);
prm.Value = 34;
prm.Direction = ParameterDirection.Input;

OdbcParameter prm2 = sampleCMD.Parameters.Add("pOut", OdbcType.Int);
prm2.Direction = ParameterDirection.InputOutput;


Console.WriteLine(sampleCMD.Parameters[0].ParameterName + " = " + sampleCMD.Parameters[0].Value.ToString());

try
{
myConn.Open();
Console.WriteLine(sampleCMD.CommandText);

Console.WriteLine("pOut = {0}", sampleCMD.Parameters["pOut"].Value);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.Read();
}
finally
{
if (myConn.State == ConnectionState.Open)
myConn.Close();
}

Console.Write("Press any key to end...");
Console.Read();

Also install the latest e-fix for TTU 8.2.

Re: Cannot get stored procedure call from C# with output parameters to work

Hi Shaw,

Thanks for the info. I see now where the error message was coming from. (i.e., I didn't have the output parameter defined - thus the error message stored procedure parameter markers are not bound). I missed that from the example (assumed the example actually worked).

But, I'm kind of back at square one. I had earlier code that was also not working despite having all parameters correctly defined. I took your code example from above and tried to get it to run. I finally went to the step to create a standalone Command line program to eliminate any other coding mistakes and used the code above as the basis for it.

--------------
using System;
using Microsoft.Data.Odbc; // This is legacy. I believe there are newer ODBC providers? connectors? out there?
// Willing to upgrade to newer if it'd help.
using System.Data;

using System.Collections.Generic;
using System.Text;

namespace Test_StoredProc
{
class Test_SP
{
static void Main(string[] args)
{
string myConnection = "DSN=x;UID=y;PWD=z"; //substituted real values for x,y, and z of course.
// The rest of the code is copied directly from your example.
...

}

I have several questions.

1. I'm concerned that the Microsoft.Data.Odbc provider might be out of date? I'm using it because I'm updating legacy code. Is there something else I should try (willing to spend the time to update all the code if necessary).

2. I'm a little confused as to how it works after the "myConn.Open(). Most of the code examples I've seen include an "ExecuteReader() command. Is this missing in your code? I tried putting it in and I still didn't get any results.

3. Also noticed you named the created a new ODBCParameter prm2. Is this strictly necessary? Other code examples show simply adding new parameters to the original "prm" variable. e.g.,

prm = sampleCMD.Parameters.Add("pOut", OdbcType.Int);
prm.Direction = ParameterDirection.InputOutput;

thanks a bunch for all the help.

Kevin

Re: Cannot get stored procedure call from C# with output parameters to work

Just re-read the last post and don't think I made it clear.

The code returns no output for the output parameter (expecting the value 68 and get a "null" instead)

Console.WriteLine("pOut = {0}", sampleCMD.Parameters["pOut"].Value);

Just for reference, here's the stored procedure.

create procedure TestProc(IN pIn INTEGER, OUT pOut INTEGER)
BEGIN

SET pOut = pIn * 2;

END;

Teradata Employee

Re: Cannot get stored procedure call from C# with output parameters to work

Q#1: I'm concerned that the Microsoft.Data.Odbc provider might be out of date? I'm using it because I'm updating legacy code. Is there something else I should try (willing to spend the time to update all the code if necessary).

A#1: Are you using .NET Framework 2.0? if yes, then use System.Data.Odbc.

Q#2: I'm a little confused as to how it works after the "myConn.Open(). Most of the code examples I've seen include an "ExecuteReader() command. Is this missing in your code? I tried putting it in and I still didn't get any results.

A#2: I tried to add Bold to the ExecuteNonQuery Line but the Editor removed it instead. Simply add sampleCMD.ExecuteNonQuery() right before the line that prints the output parameter to the console. You must make sure that the DSN option "Return Output Parameters As ResultSet" is not enabled. Did you check the DSN options? Start ODBC Data Source Administrator, Select Data Source and click Configure, Click Options in ODBC Driver Setup for Teradata Database dialog box.

Q#3: Also noticed you named the created a new ODBCParameter prm2. Is this strictly necessary? Other code examples show simply adding new parameters to the original "prm" variable. e.g.,

A#3: No it is not.

I ran the code; it works and returns 68.

BTW, you can download .NET Data Provider for Teradata from the Download Center (Download Center on www.teradata.com).

Re: Cannot get stored procedure call from C# with output parameters to work

Hi all.

I doing exactly what is described and I'm having the same problem.
Here are my code:

string source = "driver={MySQL ODBC 5.1 Driver};server=localhost;port=3306;database=test;uid=root;pwd=root";
string command;
try
{
OdbcCommand mCommand = new OdbcCommand();
OdbcConnection mConnection = new OdbcConnection(source);

command = "call sp_logon (?, ?, @out_userid)";
OdbcCommand mCommand2 = new OdbcCommand();
mCommand2.Connection=mConnection;
//mCommand2.CommandType = CommandType.StoredProcedure;
// I tried not commenting this line too!
mCommand2.CommandText = command;

mCommand2.Parameters.Add("in_login",OdbcType.VarChar);
mCommand2.Parameters.Add("in_pwd", OdbcType.VarChar);
mCommand2.Parameters.Add("out_userid", OdbcType.Int);

mCommand2.Parameters[0].Value = "user_test";
mCommand2.Parameters[0].Direction = ParameterDirection.Input;
mCommand2.Parameters[1].Value = "pwd_test";
mCommand2.Parameters[1].Direction = ParameterDirection.Input;
mCommand2.Parameters[2].Direction = ParameterDirection.InputOutput; // I tried only with Output too

OdbcDataReader mReader2;
mConnection.Open();
mReader2 = mCommand2.ExecuteReader();
//int count = mCommand2.ExecuteNonQuery(); // I tried it too...

Console.Write("Query result value: " + mCommand2.Parameters[2].Value);

// Close the Reader when done.
mReader2.Close();

// Close the connection when done.
mConnection.Close();
}
catch (Exception e)
{
Console.Write(e.ToString());
Console.Write("exception");
}

If the stored procedure returns the user id and is working in MySQL query browser.

Does any one has an idea?

Thanks.
HC

Re: Cannot get stored procedure call from C# with output parameters to work

command.CommandType = CommandType.StoredProcedure;

check this full source code sample

http://net-informations.com/csprj/data-providers/cs-stored-procedures.htm

algor.

Fan

Re: Cannot get stored procedure call from C# with output parameters to work

When you get below message,

{"ERROR [HY000] [NCR][ODBC Teradata Driver] Stored procedure parameter markers are not bound."}

you need modify odbc infomation:  

in LINUX: /opt/teradata/client/14.0/odbc_64/odbc.ini, setup the OutputAsResultSet=Yes.

in Window: Open ODBC admin, setup OutputAsResultSet=Yes