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


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
    Posts
    1

    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:

    Code:
     
        _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:


    Code:
    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    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
    Posts
    1

    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()
    {
    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;
    }
    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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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