none
How to receive data from SQL database by OdbcCommand classe RRS feed

  • Question

  • I am currently using the ODBC class for c++ to connect and query a SQL database. I am calling a stored procedure within the database like so:

    try{
            myCon->Open();
            OdbcCommand^ myCmd = gcnew OdbcCommand(MySP,myCon); //MySp is my store procedure and myCon is the SQL server connection string
            myCmd->Parameters->Add("xml",OdbcType::Char,200);
            myCmd->Parameters->default[0]->Direction=ParameterDirection::ReturnValue;
            OdbcDataReader^ myReader = myCmd->ExecuteReader();  //Execute the sending

            while (myReader->Read())
            {
                Console::WriteLine();
                for(Int32 i=0;i<myReader->FieldCount;i++)
                {
                    Console::WriteLine("{0}:{1}",(myReader->GetName(i))->ToString(),(myReader->GetValue(i))->ToString());
                }
            }
            myReader->Close();
            Console::WriteLine("OutputParamVal={0};ReturnVal={1}",myCmd->Parameters->default[0]->Value,myCmd->Parameters->default[1]->Value);
        }

    The stored procedure will perform a series of operations on several of the tables within the database. Depending on the data it will sometimes return data in a table format back to my program. At other times it will not. My question is, how do I receive this data when the stored procedure sends it back to my program. I know I am correctly calling the stored procedure as the database shifts around the data within the tables during the times it does not send anything back to my program. My colleague has been able to receive the data returns when calling the same stored procedure, however he is using Visual Basic.

    Additional info:
    The stored procedure does not accept parameters.

    If anyone has any suggestions with how to receive returned data from a stored procedure, I would greatly appreciate your help. If the ODBC class is unable to do what I am requesting, I would be happy to use something else as well.

    Thanks
    Thursday, September 4, 2008 4:21 PM

Answers

  • Stored procs in SQL Server may return integer status codes, but certainly not char(200) as you request. You could eventually make that XML chunk an Output parameter if you really need it.

     

    If you post the body of the procedure (modulo sensitive information), we’ll be able to tell you how to consume its results. Your best bet with consuming rowsets would be through DataReader as you’ve already guessed. If there are multiple rowsets, you can use the NextResult() method.

     

    Zlatko Michailov

    Program Manager, SQL Server Connectivity

    Microsoft Corp.

     

    If you are satisfied by this post, please mark it as “Answer”.

    Thursday, September 4, 2008 6:39 PM