HOWTO: Execute Stored Procedure Using CDynamicParmeterAccesor

• Microsoft Visual C++, 32-bit Editions, version 6.0

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.

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.
      hr = db.Open("SQLOLEDB","myServer", "UserId", "Password" );
      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");
   }
   } 

REFERENCES

Please see the CDynamicParameterAccessor documentation on MSDN.

Additional query words: kbDSupport

Keywords : kbDatabase kbOLEDB kbProvider kbVC600 kbATL300 kbConsumer kbGrpVCDB
Version : winnt:6.0
Platform : winnt
Issue type : kbhowto