DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ADO VC++ How to get the output parameter from stored procedure as "table of record"

  1. #1
    Join Date
    Mar 2005

    ADO VC++ How to get the output parameter from stored procedure as "table of record"

    Hello everybody,

    I am looking for the solution for my problem. I have to call from ADO in VC++ the stored procedure which has 3 input parameters and 1 output parameter. This output parameter is a table or records taken from stored procedure, so it is not the simple type parameter. I want to get anyhow this table to my C++ program but it doesn't work, although I think I did everything OK. Could you please help me with it and look what is wrong, because I cannot find any good solution in the internet.
    This is a main part of my C++ function:

        _RecordsetPtr pRs  = NULL;
        _CommandPtr pComm  = NULL;
        _ParameterPtr pPar = NULL;
        strError = "DRXADODatabaseProductStaging::test\n";
        _bstr_t strMandNr, strWerkNr, strLagNr;
       // sample input parameters
        strMandNr = "001";
        strWerkNr = "63";
        strLagNr = "01";
            // set the command
            _bstr_t strSQLCommand("{? = call PA_TEST.FC_GET_LAPL(?, ?, ?, { resultset 1000, p_lpl_coll })}");
            pComm->ActiveConnection = GetConnection();
            pComm->CommandText = strSQLCommand;
            // set the parameters  
            pPar = pComm->CreateParameter("RETURN_VALUE", adVarChar, adParamReturnValue, 255);
            pPar = pComm->CreateParameter("p_lpl_mand_nr", adVarChar, adParamInput, 3, strMandNr);
            pPar = pComm->CreateParameter("p_lpl_werk_nr", adVarChar, adParamInput, 3, strWerkNr);
            pPar = pComm->CreateParameter("p_lpl_lag_nr", adVarChar, adParamInput, 2, strLagNr);
            pRs->CursorType = adOpenStatic;
            pRs->CursorLocation = adUseClient;
            pRs->LockType = adLockReadOnly;
            // call the stored procedure
            pRs = pComm->Execute(NULL, NULL, adCmdText);
          // But after running the command I receive always the exception:
         // "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available"
            // check the return value
            _variant_t vtRet = pComm->Parameters->GetItem("RETURN_VALUE")->Value;
            if (vtRet.vt != VT_NULL)
                strError += (_bstr_t) vtRet;
                return false;
            return true;
        catch (_com_error &ce)
            strError += ManageException(ce);
            GetErrorLog()->Write((const char *) strError);
            return false;

    and this is the stored procedure definition:

    create or replace package PA_TEST is
      -- Public type declarations
      type lapl_rectyp is record (lag_nr            lagerplaetze.lpl_lag_nr%type,
                                  lpl_gang          lagerplaetze.lpl_gang%type,
                                  lpl_saeule        lagerplaetze.lpl_saeule%type,
                                  lpl_ebene         lagerplaetze.lpl_ebene%type);
      type lapl_tabtype is table of lapl_rectyp index by binary_integer;
      -- Public constant declarations
      function fc_get_lapl(p_lpl_mand_nr in varchar2
                         , p_lpl_werk_nr in varchar2
                         , p_lpl_lag_nr in varchar2
                         , p_lpl_coll out lapl_tabtype) 
      return varchar2;
    end PA_TEST;

    Thanks in advance,

  2. #2
    Join Date
    Jan 2004
    Alexandria, VA
    First: Have you tried running the stored procedure in a generic query tool? (You didn't say what DB product you were using).

    Second: A stored procedure can return results in three ways:
    1) An output parameter
    2) The return value for the procedure
    3) A recordset (result of a SELECT statement in the procedure)

    For the first two, the value must be a data type that is intrinsic to the DB system (such as INT, VARCHAR(), or DATETIME, etc.). As far as I know, you cannot use a User Defined Type (or Structure) as a parameter (for input OR output) or a return value.

    If you need to return a resultset (such as from a SELECT statement), you will need to pipe the results into a recordset object. I'm not a C++ programmer, but in VB it would be done like:

    Set objResultSet = objCommand.Execute
    Bob Rouse
    Dimension Data

  3. #3
    Join Date
    Nov 2006

    an example in C++

    I found out by myself how to do it in C++, nowhere in the web is there anything about this, i guess C++ isn't used anymore in the universe.

    this example is a paste from my project, but the variables should be pretty obvious. CcAdoBase is our wrapper for the COM ADO.

    AppendParamReturnInt is called before (or after) all the others AppendParam, then a command15::Execute is made and we retrieve the return from the stored procedure with GetReturnInt. The return values were only 0, -1 or -2 so I guess an Integer was sufficient.

    _Command *m_pCommande;

    BOOL CcAdoBase::AppendParamReturnInt()
    _ParameterPtr param = m_pCommande->CreateParameter(_bstr_t("RETURN_VALUE"), adInteger,adParamReturnValue, sizeof(int));
    return FALSE;

    return TRUE;


    int CcAdoBase::GetReturnInt()
    _ParameterPtr param = m_pCommande->Parameters->GetItem(_bstr_t("RETURN_VALUE"));
    _variant_t value = param->GetValue();
    return value.iVal;
    Last edited by MontrealCPlus; 11-22-2006 at 06:24 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center