HOWTO: ExecuteParameterized Command Multiple Times with ATL OLEDB Consumer Templates

ID: Q240412


The information in this article applies to:
  • Microsoft Visual C++, 32-bit Editions, version 6.0
  • Microsoft OLE DB, versions 1.0, 2.0


SUMMARY

Executing a command multiple times is a very common scenario if the command is a parameterized query. The way to do "Single Prepare and Multiple Execution" by using ATL OLEDB consumer templates is to call CCommand::Prepare to prepare the command, call CCommand::Open to execute the command, and call CCommand::Close before the next round of execution.


MORE INFORMATION

The following sample illustrate the major steps in executing a command multiple times.

To run this sample, copy and paste the code into a .cpp file and build it as a console project. The sample uses the Authors table in the pubs database of Microsoft SQL Server.

Note that you might need to modify the server name and make sure the uid and pwd are correct.


//test.cpp

#include <atldbcli.h>
#include <iostream.h>

#define RETURNHR(hr) if(FAILED((HRESULT)hr)) { AtlTraceErrorRecords((HRESULT)hr); return E_FAIL; }
 
class CAuthors
{
public:
   // Data Elements
   TCHAR m_au_id[11];
   TCHAR m_au_lname[40];
   TCHAR m_au_fname[20];

   //parameter variable
   TCHAR m_inParam[20];

//Parameter Accessor
BEGIN_PARAM_MAP(CAuthors)
	SET_PARAM_TYPE(DBPARAMIO_INPUT)
	COLUMN_ENTRY(1, m_inParam)
END_PARAM_MAP()

//Output Accessor
BEGIN_COLUMN_MAP(CAuthors)
	COLUMN_ENTRY(1, m_au_id)
	COLUMN_ENTRY(2, m_au_lname)
	COLUMN_ENTRY(3, m_au_fname)
END_COLUMN_MAP()
};

int main(void)
{
   CoInitialize(NULL);

   HRESULT hr;
   CDataSource connection;
   CSession session;
   CCommand<CAccessor< CAuthors> > authors;
 
   CDBPropSet	dbinit(DBPROPSET_DBINIT);
	
   dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
   dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));
   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("dank500"));
   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
   dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("pubs"));
   hr = connection.Open(_T("SQLOLEDB"), &dbinit);
	
   RETURNHR(hr = session.Open(connection))

   //create command
   RETURNHR(hr = authors.Create(session, _T("select au_id, au_lname, au_fname "
   	"from dbo.authors where au_fname = ?") ))

   //prepare for execution three times
   RETURNHR(hr = authors.Prepare(3)) 

   CDBPropSet	propset(DBPROPSET_ROWSET);
   propset.AddProperty(DBPROP_IRowsetChange, true);
   propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
	
   //fill in parameter
   _tcscpy(authors.m_inParam, "Ann");
   //execution #1
   RETURNHR(hr = authors.Open(&propset))

   //data retrieval
   RETURNHR(hr = authors.MoveFirst())
   do
   {
     cout<<authors.m_au_id <<" "<<authors.m_au_fname<<" "<<authors.m_au_lname<<endl;
   }while( authors.MoveNext() == S_OK);

   //call close to prepare for next execution
   authors.Close();

   //fill in parameter again
   _tcscpy(authors.m_inParam, "Anne");
   //execution #2
   RETURNHR(hr = authors.Open(&propset))
	
   authors.Close();
   //execution#3
   //...

   //clean up
   session.Close();
   connection.Close();
   return S_OK;
}
 
Note: What CCommand::Close actually does is close the opened rowset and release the created accessors for the output columns. Even though you call CCommand::Close after each execution, the command parameter accessor is still alive, the command object is still valid, and the command is prepared at the server side, which improves the performance for multiple execution.

As an alternative to using consecutive CCommand::Open calls, after CALLING CCcommand::Prepare, you can instead call CCommand::Execute multiple times. CCommand::Execute is an undocumented method but you can find its signature in header file Atldbcli.h. When compared with this, consecutive CCommand::Open calls do the same job but are much easier and cleaner.


REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

Q239440 ATL4Acc.exe: Calling SQL 7 Parameterized Stored Procedures Using ATL OLE DB Consumer Accessors
Q241639 PRB: Errors Occurred Error When Calling Stored Procedure with more than a SELECT Statement

Additional query words: ATL OLEDB Consumer Templates

Keywords : kbDatabase kbMDAC kbOLEDB100 kbOLEDB200 kbProvider kbSQLServ kbVC600 kbATL300 kbGrpVCDB
Version : WINDOWS:1.0,2.0; winnt:6.0
Platform : WINDOWS winnt
Issue type : kbhowto


Last Reviewed: November 24, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.