Using OLE DB Consumer Templates

Using OLE DB Consumer Templates

Using UDL. 1

Connecting To The Database. 2

Retrieving Database Schema. 2

Reading Data. 3

Opening Dataset4

Retrieving Column Info. 4

Retrieving Rowset Count5

CBulkRowset6

Executing SQL Statement6

Updating Rowset6

The CDynamicAccesor class does not handle string columns correctly in Visual C++. 7

Inserting data into rowset7

Using CDynamicAccessor with Unicode Columns Causes Memory Overwrites and Other Failures. 7

Traditional Transaction. 7

Multiple Accessor8

BookMark. 8

BLOB. 9

XML. 9

Error Handlering. 9

OLE DB Resource Pooling. 10

Native Client For SQL Server 2005. 10

Calling SQL Server Stored Procedure. 11

Using UDL

CComBSTR LoadUDL(const CComBSTR& udlPath)

{

CComPtr<IDataInitialize> spDataInitialize;

HRESULT hr = spDataInitialize.CoCreateInstance( __uuidof(MSDAINITIALIZE));

CComHeapPtr<OLECHAR> conString;

if (SUCCEEDED(hr))

{

hr=spDataInitialize->LoadStringFromStorage(udlPath, &conString);

if(hr!=S_OK)

{

cout<<"LoadStringFromStorage failed"<<endl;

return CComBSTR(L"");

}

return CComBSTR(conString);

}

cout<<"CoCreateInstance( __uuidof(MSDAINITIALIZE)) failed"<<endl;

return CComBSTR(L"");

}

Connecting To The Database

CDataConnection con;

HRESULT hr=con.Open(conString);//conString can be generated by LoadUDL function

We can also connect to a database by using CDataSource::Open member function.What’s the difference between CDataConnection and CDataSource? CDataConnection is a useful class for creating clients because it encapsulates necessary objects (data source and session) and some of the work you need to do when connecting to a data source.

//Popup a data link property dialog to connect database
CDataSource source;
HRESULT hr=source.Open();
CComBSTR conStr;
source.GetInitializationString(&conStr,true);

Retrieving Database Schema

CComBSTR GetDatabaseProviderName(const CDataSource& ds)

{

CComVariant var;

HRESULT hr = ds.GetProperty( DBPROPSET_DATASOURCEINFO, DBPROP_PROVIDERNAME, &var );

if(hr==S_OK)

{

return var.bstrVal;

}

return CComBSTR(L"");

}

HRESULT GetTableNames(const CDataConnection& con,vector<CComBSTR>& names)

{

if(!names.empty())

{

names.clear();

}

CTables ts;

HRESULT hr=ts.Open(con.m_session,NULL,NULL,NULL,_T("TABLE"));

if( FAILED(hr) )

{

cout<<"Unable to open the TABLES schema to get the list of available tables"<<endl;;

return hr;

}

hr = ts.MoveFirst();

while( SUCCEEDED( hr ) && hr != DB_S_ENDOFROWSET )

{

names.push_back(ts.m_szName);

hr = ts.MoveNext();

}

if(names.empty())

{

return S_FALSE;

}

else

{

return S_OK;

}

}

void GetPrimaryKeys(CDataConnection& con,vector<CString>& pkeys)

{

CPrimaryKeys key;

HRESULT hr=key.Open(con.m_session,NULL,NULL,_T("pet"));

while(S_OK==key.MoveNext())

{

pkeys.push_back(key.m_szColumnName);

}

}

Reading Data

HRESULT OpenByDynamicAccessor(const CDataConnection& con,const CComBSTR& sql)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

return rs.Open(con.m_session, sql, &pset );

}

Opening Dataset

HRESULT OpenByDynamicAccessor(CDataConnection& con,const CComBSTR& sql)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

//If the table has blob column,you must call this method

rs.SetBlobHandling( DBBLOBHANDLING_SKIP );

return rs.Open(con.m_session, sql, &pset );

}

Retrieving Column Info

Remark-----

Either DBPROP_CLIENTCURSOR or DBPROP_IRowsetScroll property is surpported,the count of columns will be equal to actual count of columns + 1.The column 0 is used for bookmark.

HRESULT GetColumnInfo(CDataConnection& con,const CComBSTR& sql)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);

b=rs.SetBlobHandling( DBBLOBHANDLING_SKIP );

HRESULT hr=rs.Open(con, sql, &pset );

if(hr!=S_OK)

{

return hr;

}

DBTYPE dbtype;

for( DBORDINAL col = 1; col <= rs.GetColumnCount(); col++ )

{

hr=rs.GetColumnType( col, &dbtype );

if( col > 1 )

printf( ", " );

printf( "%S [%d]", rs.GetColumnName( col ), dbtype );

}

DBORDINAL count;

DBCOLUMNINFO* pInfo=NULL;

OLECHAR* pbuffer=NULL;

CComPtr<IColumnsInfo> spColumnsInfo;

rs.m_spRowset->QueryInterface(&spColumnsInfo);

if (SUCCEEDED(hr))

{

hr = spColumnsInfo->GetColumnInfo(&count, &pInfo, &pbuffer);

if(hr!=S_OK)

{

return hr;

}

}

CComPtr<IMalloc> spMalloc;

hr=CoGetMalloc(1,&spMalloc);

spMalloc->Free(pInfo);

spMalloc->Free(pbuffer);

}

Retrieving Rowset Count

HRESULT GetRowsetSize(CDataConnection& con,const CComBSTR& sql,unsigned long& size)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);

b=rs.SetBlobHandling( DBBLOBHANDLING_SKIP );

HRESULT hr=rs.Open(con, sql, &pset );

if(hr!=S_OK)

{

return hr;

}

return rs.GetApproximatePosition(NULL,NULL,&size);

}

CBulkRowset

Executing SQL Statement

HRESULT ExecuteSQLStatement(CDataConnection& con,const CComBSTR& sql)

{

CCommand<CNoAccessor,CNoRowset> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

return rs.Open(con.m_session, sql, &pset );

}

Updating Rowset

If we want to make our db_table attribute class provide the ability of updating its fields’s values,we should pay attention to thus points:

1）Implementing the inner codes

void GetRowsetProperties(CDBPropSet* pPropSet)

{

pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);

pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);

pPropSet->AddProperty(DBPROP_IRowsetUpdate,true,DBPROPOPTIONS_OPTIONAL);

pPropSet->AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);

pPropSet->AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE);

}

2）The codes of client

#include "stdafx.h"

#include "sender.h"

int _tmain(int argc, _TCHAR* argv[])

{

::CoInitialize(NULL);

Csender s;

s.OpenAll();

HRESULT hr=s.MoveFirst();

s.m_com_num=11;

s.SetData();

hr=s.UpdateAll();

::CoUninitialize();

return 0;

}

SetData() member function must be called.

The CDynamicAccesor class does not handle string columns correctly in Visual C++

Please refer to the below url:

http://support.microsoft.com/?id=201390

Inserting data into rowset

CTable_1 table;

HRESULT hr=table.OpenAll();

hr=table.MoveFirst();

table.m_a=5;

table.m_dwaStatus=DBSTATUS_S_OK;

table.m_dwaLength=4;

hr=table.Insert();

Using CDynamicAccessor with Unicode Columns Causes Memory Overwrites and Other Failures

Please refer to the below url:

http://support.microsoft.com/kb/q238539

Traditional Transaction

Multiple Accessor

Please see the arctile of MSDN named as Using Multiple Accessors on a Rowset

BookMark

Definition----A saved placeholder value that identifies a row in a rowset and can later be used by the consumer to retrieve that particular row.

void GetBookMark(CDataConnection& con,const CComBSTR& sql)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

bool b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_REQUIRED);

rs.SetBlobHandling( DBBLOBHANDLING_SKIP );

HRESULT hr=rs.Open(con.m_session, sql, &pset );

DBORDINAL count=rs.GetColumnCount();

CBookmark<> firstmark;

rs.MoveFirst();

char* pV=static_cast<char*>(rs.GetValue(1));

hr=rs.GetBookmark(&firstmark);

while(S_OK==rs.MoveNext())

{

CBookmark<> bookmark;

hr=rs.GetBookmark(&bookmark);

BYTE* p=bookmark.GetBuffer();

}

hr=rs.MoveToBookmark(firstmark);

pV=static_cast<char*>(rs.GetValue(1));

}

The bookmark retrieved through upon codes is numeric bookmark,the value is equalt to the index of current row.We can also use SetBookmark memeber function set the current bookmark’s value.

BLOB

XML

void GetXMLData(CDataConnection& con,const CComBSTR& sql)

{

CCommand<CXMLAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

HRESULT hr=rs.Open(con.m_session, sql, &pset );

CStringW str;

while(S_OK==rs.MoveNext())

{

rs.GetXMLRowData(str,true);

}

}

Error Handlering

void ConnectDatabase(LPTSTR conString)

{

CComBSTR str(L"Provider=MSDASQL.1;Persist Security Info=True;");

str+=conString;

CDataConnection con;

HRESULT hr=con.Open(str);

if(hr!=S_OK)

{

CDBErrorInfo info;

LCID lcid = ::GetUserDefaultLCID();

ULONG records;

hr=info.GetErrorRecords(con.m_source.m_spInit,__uuidof(IDBInitialize),&records);

CComBSTR errorMsg,source;

hr=info.GetAllErrorInfo(0,lcid,&errorMsg,&source);

string s("Failed connect to database: ");

s+=CW2A(errorMsg.m_str);

throw CTXDatabaseExcep(hr,s);

}

}

void ThrowDBException(const string& context)

{

CDBErrorInfo info;

LCID lcid = ::GetUserDefaultLCID();

ULONG records;

HRESULT hr=info.GetErrorRecords(m_con.m_source.m_spInit,__uuidof(IDBInitialize),&records);

if(hr!=S_OK)

{

throw std::runtime_error("CDBErrorInfo::GetErrorRecords faield");

}

CComBSTR errorMsg,source;

hr=info.GetAllErrorInfo(0,lcid,&errorMsg,&source);

if(hr!=S_OK)

{

throw std::runtime_error("CDBErrorInfo::GetAllErrorInfo faield");

}

string s=context+" : ";

s+=CW2A(errorMsg.m_str);

throw std::exception(s.c_str());

}

OLE DB Resource Pooling

Native Client For SQL Server 2005

How can I use native client?Just setting the udl property,and using native client provider.

When I fetch 10,000 records ,and each record has 118 columns,Native Client provider is faster than OLE DB provider.That’s my test function.

DWORD PerformanceTest(CDataConnection& con,const CComBSTR& sql)

{

DWORD x=GetTickCount();

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

bool b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_REQUIRED);

rs.SetBlobHandling( DBBLOBHANDLING_SKIP );

rs.Open(con.m_session, sql, &pset );

while(S_OK==rs.MoveNext())

{}

return GetTickCount()-x;

}

OLE DB provider always spend 2,000 million seconds at least,and Native Client always spend less than 1,790 million seconds.

Calling SQL Server Stored Procedure

CREATE PROCEDURE test AS

delete from carInfo2

GO

HRESULT CallStoredProcedureNoParameterNoResult(CDataConnection& con,const CComBSTR& procName)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset;

return rs.Open(con.m_session, procName, &pset );

}

CREATE PROCEDURE test2AS

select * from carInfo2

GO

HRESULT CallStoredProcedureNoParameter(CDataConnection& con,const CComBSTR& procName)

{

CCommand<CDynamicAccessor> rs;

CDBPropSet pset( DBPROPSET_ROWSET );

bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);

b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_OPTIONAL);

HRESULT hr=rs.Open(con.m_session, procName, &pset );

if(S_OK==hr)

{

DBCOUNTITEM count(0);

hr=rs.GetApproximatePosition(0,0,&count);

return hr;

}

else

{

return hr;

}

}

CREATE PROCEDURE test3 @id int

AS

delete from carInfo2

where carID=@id

GO

HRESULT CallStoredProcedureNoRowset(CDataConnection& con,const int parameter)

{

CCommand<CDynamicParameterAccessor,CNoRowset> rs;

HRESULT hr = rs.Create(con.m_session,"exec test3 ?");

hr=rs.Prepare();

void* p;

rs.BindParameters(&rs.m_hParameterAccessor,rs.m_spCommand,&p);

bool r=rs.SetParam<int>(1,&parameter);

return rs.Open(NULL,NULL,0);

}

CREATE PROCEDURE test4 @id int

as

select * from carInfo2

select * from carInfo2

where carID=@id

GO

HRESULT CallStoredProcedure(CDataConnection& con,const int parameter)

{

CCommand<CDynamicParameterAccessor,CRowset> rs;

HRESULT hr = rs.Create(con.m_session,"exec test4 ?");

hr=rs.Prepare();

void* p;

rs.BindParameters(&rs.m_hParameterAccessor,rs.m_spCommand,&p);

bool r=rs.SetParam<int>(1,&parameter);

CDBPropSet pset( DBPROPSET_ROWSET );

bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);

b=pset.AddProperty(DBPROP_IRowsetUpdate,true,DBPROPOPTIONS_OPTIONAL);

b=pset.AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);

hr=rs.Open(&pset);

hr=rs.MoveFirst();

DBORDINAL l=1;

int* pv=(int*)rs.GetValue(l);

return hr;

}