error when CALLing Teradata SP from C#

Database

error when CALLing Teradata SP from C#

Hi,

I have this code which I am using as a test mostly borrowed from existing examples. However, in my case I keep getting the error:

System.Data.Odbc.OdbcException: ERROR [HY000] [Teradata][ODBC Teradata Driver]

[Teradata Database] Invalid session mode for procedure execution.

This is the test SP:


/*
create procedure testdb.TestProc(IN pIn INTEGER, OUT pOut INTEGER)
BEGIN
SET pOut = pIn * 2;
END;
*/


This is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;

static void Main(string[] args) {
string myConnection = "DSN=TDtest;UID=user;PWD=pwd";
OdbcConnection myConn = new OdbcConnection(myConnection);

string myTestQuery = "CALL TDtest.sp_INOUTtest(?,?);";

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

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);
OdbcDataReader r = sampleCMD.ExecuteReader();

while (r.Read())
{
int pOut = r.GetInt32(0);
System.Console.WriteLine("pOut = " + pOut.ToString());
}

r.Close();
myConn.Close();
}

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();

}
}
}

It fails on this line:

OdbcDataReader r = sampleCMD.ExecuteReader();

Any suggestion please ?...

Thank you for your time.

Boris


3 REPLIES

Re: error when CALLing Teradata SP from C#

Hi,

Looks like the session settings issue:

It reminds me of this link before:

http://forums.teradata.com/forum/database/invalid-session-mode-for-procedure-execution

Also, try to change in your code ,the setting to ANSI Session Mode.

Cheers,

Re: error when CALLing Teradata SP from C#

Hi Raja,

Thank you for the feedback.

I got it to work in Teradata Studio, but it will not work in SQL Assistant using the same ODBC settings ...???

Another question please:

How do I get the query to display the result in a Teradata Studio SQL Editor window ?...

i.e.: CALL TDtest.sp_INOUTtest(34,pOut);

Thank you very much for your help.

Boris.

Teradata Employee

Re: error when CALLing Teradata SP from C#

Which session mode was used when SP was compiled? Use same session-modes for compiling and calling the SP.