Microsoft KB Archive/240412

= How To ExecuteParameterized Command Multiple Times with ATL OLEDB Consumer Templates =

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 

-

<div class="notice_section">

This article was previously published under Q240412

<div class="summary_section">

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.

<div class="moreinformation_section">

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


 * 1) include <atldbcli.h>
 * 2) include <iostream.h>

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

//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)

<div class="references_section">