Microsoft KB Archive/198519

= How To Execute Stored Procedure Using CDynamicParmeterAccesor =

Article ID: 198519

Article Last Modified on 3/14/2005

-

APPLIES TO

 Microsoft OLE DB 2.7, when used with:  Microsoft Visual C++ 6.0 Enterprise Edition

 Microsoft Visual C++ 6.0 Professional Edition

 Microsoft Visual C++ 6.0 Standard Edition 

-

<div class="notice_section">

This article was previously published under Q198519

<div class="summary_section">

SUMMARY
CDynamicParameterAccessor is an OLEDB Template class designed to execute stored procedures. This class is similar to the CDynamicAccessor class, but it obtains the parameter information to be set by calling the ICommandWithParameters interface.

This article demonstrates how to use this class for executing a SQL Server stored procedure and getting the output parameter values.

<div class="moreinformation_section">

SQL Scripts
Create Table Students ( id int primary key,

Name varchar(20), Age int)

Insert into Students values (1,'John',12) Insert into Students values (2,'Ram',13) Insert into Students values (3,'Keshav',10) Insert into Students values (4,'David',9)

CREATE PROCEDURE sp_getStudent @id int , @outparm int OUTPUT AS  select  * from students where id = @id select @outparm = 99

Visual C++ Code for Executing this Stored Procedure
void ExecProc {     HRESULT hr; CDataSource db; // Change the arguments of Open call as appropriate. CDBPropSet propset(DBPROPSET_DBINIT); propset.AddProperty(DBPROP_INIT_DATASOURCE, L"SQLServer1"); propset.AddProperty(DBPROP_INIT_CATALOG, L"pubs"); propset.AddProperty(DBPROP_AUTH_USERID, L"sa"); propset.AddProperty(DBPROP_AUTH_PASSWORD, L""); hr = db.Open("SQLOLEDB", &propset); if (FAILED(hr)) return;

CSession m_session; m_session.Open(db);

// Create a command with multiple results. This is done to get the // output parameter. CCommand<CDynamicParameterAccessor,CRowset,CMultipleResults> Rs; hr = Rs.Create(m_session,"exec sp_getstudent ?,? OUT"); hr = Rs.Prepare;

void* pDummy;

// Bind the parameters. hr = Rs.BindParameters(&Rs.m_hParameterAccessor,                                Rs.m_spCommand,&pDummy);

long lOutVal;

int nParamValue = 2; char *pData;

// Set the parameter values. Rs.SetParam((ULONG)1,&nParamValue);

// Open the command. hr=Rs.Open(NULL,NULL,0);

// Call this function to bind the output resultset. Rs.Bind; hr = Rs.MoveFirst;

// Get the resultset. while (hr == S_OK) {        pData = (char*)Rs.GetValue(2L); hr = Rs.MoveNext; }

// Now we get the output parameter. hr=Rs.GetNextResult(&lOutVal);

// Get the output parameter value. Rs.GetParam((ULONG)2, &lOutVal);

m_session.Close; db.Close;

}

NOTE: To set a string parameter, you may want do something like the following instead of calling SetParam. You need to get a pointer to the buffer and then copy the new string value into it.

DBTYPE dbtype; StudRs.GetParamType((ULONG)1,&dbtype); char *pParam;

if(dbtype == DBTYPE_STR) {     // This gives the address of the buffer of the parameter pParam = (char *)StudRs.GetParam( (ULONG) 1 ); // copy the parameter value strcpy(pParam,"MacFeather"); }  }

<div class="references_section">