How To Execute Stored Procedure Using CDynamicParmeterAccesor (198519)

MORE INFORMATION

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