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