Archives of the TeradataForum

Message Posted: Fri, 26 Mar 2004 @ 19:29:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

TD Development Guru's,

Not sure if anyone can help with this - perhaps some suggested trouble-shooting techniques?

We are in the process of developing our first ADW application where we will be inserting retail transaction into the warehouse in real time. We will also be performing some fairly complex queries to retrieve related information for returned merchandise. One of our programmers is making the first attempts at Stored Procedures in TD for us. Below is a C# program to test some basic functionality and he is not getting an output value expected back.

Can anyone help with the problem below; and, is there some place where we can find code examples or programming\development support beyond a professional services engagement - something similar to a MSDN subscription.

Thanks

Steve Rockarts

The code for procedure:

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

SET pOut = pIn;

END;

The C# invoking code:

public void Run()
{
        string myConnection;
        myConnection = "DSN=TD_testDB;UID=user;PWD=pass";

        OdbcConnection myConn = new OdbcConnection(myConnection);

        string myTestQuery = "call testDB.TestProc(?,pOut)";
        OdbcCommand sampleCMD = new OdbcCommand(myTestQuery);
        sampleCMD.CommandType = CommandType.StoredProcedure;
        sampleCMD.Connection = myConn;
        OdbcParameter sampParm = sampleCMD.Parameters.Add("pIn", OdbcType.Int);
        sampleCMD.Parameters["pIn"].Value = 10;

        sampParm = sampleCMD.Parameters.Add("pOut",OdbcType.Int);
        sampParm.Direction = ParameterDirection.Output;

        try
        {
            myConn.Open();

            sampleCMD.ExecuteNonQuery();
            myConn.Close();

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

When I run the procedure the parameter pOut does not get assigned the return value, pOut's value is System.DBnull.

If I change the pOut as INOUT parameter and change the C# code as follows:

(old)   string myTestQuery = "call testDB.TestProc(?,pOut)";
(new)   string myTestQuery = "call testDB.TestProc(?,?)";


(old)   sampParm = sampleCMD.Parameters.Add("pOut",OdbcType.Int);
(old)   sampParm.Direction = ParameterDirection.Output;

(new)   sampParm = sampleCMD.Parameters.Add("pOut",OdbcType.Int);
(new)   sampParm.Direction = ParameterDirection.InputOutput;
(new)   sampleCMD.Parameters["pIn"].Value = 20;

the return value for pOut is 20 (the value from the moment of call, not after execution of procedure).

I don't know what I'm mising.

Thanks.

	<Prev	Next>		<<First	<Prev	Next>	Last>>