Article ID: 240412
Article Last Modified on 7/1/2004
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
This article was previously published under Q240412
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);
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
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))
//fill in parameter
_tcscpy(authors.m_inParam, "Ann");
//execution #1
RETURNHR(hr = authors.Open(&propset))
//data retrieval
while( authors.MoveNext() == S_OK)
{
cout<<authors.m_au_id <<" "<<authors.m_au_fname<<" "<<authors.m_au_lname<<endl;
}
//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.
Note: The following block of code is to add properties to support update, insert and delete on the returned rowset.
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
If you are only going to perform data retrieval and make no modification to the data, then this code block can be commented out along with changing the following line of code:
RETURNHR(hr = authors.Open(&propset))
to
RETURNHR(hr = authors.Open())
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
239440 ATL4Acc.exe: Calling SQL 7 Parameterized Stored Procedures Using ATL OLE DB Consumer Accessors
241639 PRB: Errors Occurred Error When Calling Stored Procedure with more than a SELECT Statement
Additional query words: ATL OLEDB Consumer Templates
Keywords: kbhowto kbprovider kbconsumer kbdatabase kbdtl KB240412
