All Forums Database
boriscb 22 posts Joined 09/13
24 Mar 2014
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

        

Raja_KT 1246 posts Joined 07/09
24 Mar 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

boriscb 22 posts Joined 09/13
25 Mar 2014

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.
 

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2014

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.