How To ExecuteParameterized Command Multiple Times with ATL OLEDB Consumer Templates (240412)

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