|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/28/2008 4:29:09 PM
Posts: 3,
Visits: 11
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:12:49 PM
Posts: 117,
Visits: 231
|
|
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.
Shaw
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/28/2008 4:29:09 PM
Posts: 3,
Visits: 11
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/28/2008 4:29:09 PM
Posts: 3,
Visits: 11
|
|
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;
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:12:49 PM
Posts: 117,
Visits: 231
|
|
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).
Shaw
|
|
|
|