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

[Yazilim]

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";

  

    try
    {


        TESTHR(pRs.CreateInstance(__uuidof(Recordset)));
        TESTHR(pComm.CreateInstance(__uuidof(Command)));
        TESTHR(pPar.CreateInstance(__uuidof(Parameter)));


        // 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);
        pComm->Parameters->Append(pPar);

        pPar = pComm->CreateParameter("p_lpl_mand_nr", adVarChar, adParamInput, 3, strMandNr);
        pComm->Parameters->Append(pPar);


        pPar = pComm->CreateParameter("p_lpl_werk_nr", adVarChar, adParamInput, 3, strWerkNr);
        pComm->Parameters->Append(pPar);

        pPar = pComm->CreateParameter("p_lpl_lag_nr", adVarChar, adParamInput, 2, strLagNr);
        pComm->Parameters->Append(pPar);


        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,
Yazilim.

[brouse]

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

[MontrealCPlus]

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()
{
try
{
_ParameterPtr param = m_pCommande->CreateParameter(_bstr_t("RETURN_VALUE"), adInteger,adParamReturnValue, sizeof(int));
m_pCommande->Parameters->Append(param);
}
catch(...)
{
SetErrorAdo();
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;
}