I was
amazed and delighted to see how simple
database
access has become thanks to
OLE DB.
I've been reading the hype and so I went
out and found the Microsoft Book on
OLE DB.
It's a great idea as it's explained
there but still rather complex to code.
I noticed that
Visual C++ 6 has some new
templates for Data Consumers so I
started looking at the documentation.
Wow! Is this simple stuff or what??
Here's a
sample,
a CONSOLE application at that, using the
NWind database that comes with the
DASDK. It's only 99 lines of code
including comments and it compiles into
a 60K executable.
Collapse
#include <atldbcli.h>
#include <iostream>
using namespace std;
class myNwCust
{
public:
TCHAR m_CustomerID[6];
TCHAR m_CompanyName[41];
TCHAR m_ContactName[31];
TCHAR m_Phone[25];
BEGIN_COLUMN_MAP(myNwCust)
COLUMN_ENTRY(1, m_CustomerID)
COLUMN_ENTRY(2, m_CompanyName)
COLUMN_ENTRY(3, m_ContactName)
COLUMN_ENTRY(4, m_Phone)
END_COLUMN_MAP()
};
CDataSource ds;
CSession session;
CCommand <CAccessor<myNwCust> > cust;
int main()
{
try{
HRESULT hr = CoInitialize(0);
if(FAILED(hr))
{
cout << "Can't start COM!? " << endl;
return -1;
}
hr = ds.Open(_T("MSDASQL"), "OLE_DB_NWind_Jet", "sa", "");
if(FAILED(hr))
{
cout << "Can't open Nwind" << endl;
return -1;
}
hr = session.Open(ds);
if(FAILED(hr))
{
cout << "Can't open Nwind SESSION" << endl;
ds.Close();
return -1;
}
TCHAR mySQL[] = "SELECT CustomerID, CompanyName, ContactName, \
Phone FROM Customers";
hr = cust.Open(session, mySQL);
if(FAILED(hr))
{
cout << "Can't open Nwind TABLE" << endl;
session.Close();
ds.Close();
return -1;
}
while(cust.MoveNext() == S_OK)
{
cout << cust.m_CustomerID << ", " << cust.m_CompanyName << ", ";
cout << cust.m_ContactName << ", " << cust.m_Phone << endl;
}
cust.Close();
session.Close();
ds.Close();
cout << "That's All Folks" << endl;
return 1;
}
catch(...)
{
cout << "Unknown failure" << endl;
return -1;
}
}
The
Microsoft documentation and samples are
in the MSDN pages "Visual
C++ Documentation \
References \ Microsoft Foundation Class
Library and Templates\OLE
DB Templates"
Here is
an extended
example of the console mode
OLEDB
program that has Insert/Update/Delete
functions:
Collapse
#include <atldbcli.h>
#include <iostream>
using namespace std;
class myNwCust
{
public:
char m_CustomerID[6];
char m_CompanyName[41];
char m_ContactName[31];
char m_Phone[25];
BEGIN_ACCESSOR_MAP(myNwCust,2)
BEGIN_ACCESSOR(0,true)
COLUMN_ENTRY_TYPE(1,DBTYPE_STR, m_CustomerID)
COLUMN_ENTRY_TYPE(2,DBTYPE_STR, m_CompanyName)
COLUMN_ENTRY_TYPE(3,DBTYPE_STR, m_ContactName)
COLUMN_ENTRY_TYPE(10,DBTYPE_STR, m_Phone)
END_ACCESSOR()
BEGIN_ACCESSOR(1,false)
COLUMN_ENTRY_TYPE(3,DBTYPE_STR, m_ContactName)
END_ACCESSOR()
END_ACCESSOR_MAP()
};
void my_insert();
void my_update();
void my_delete();
bool my_find();
CDataSource ds;
CSession session;
CCommand <CAccessor<myNwCust> > cust;
CDBPropSet propset(DBPROPSET_ROWSET);
int main()
{
try{
HRESULT hr = CoInitialize(0);
if(FAILED(hr))
{
cout << "Can't start COM!? " << endl;
return -1;
}
hr = ds.Open(_T("MSDASQL"), "OLE_DB_NWind_Jet", "sa", "");
if(FAILED(hr))
{
cout << "Can't open Nwind" << endl;
return -1;
}
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propset.AddProperty(DBPROP_IRowsetScroll, true);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT |
DBPROPVAL_UP_DELETE );
hr = session.Open(ds);
if(FAILED(hr))
{
cout << "Can't open Nwind SESSION" << endl;
ds.Close();
return -1;
}
char mySQL[] = "SELECT * FROM Customers ";
hr = cust.Open(session, mySQL, &propset) ;
if(FAILED(hr))
{
cout << "Can't open Nwind TABLE" << endl;
session.Close();
ds.Close();
return -1;
}
int line = 0;
while(cust.MoveNext() == S_OK)
{
char buff[81];
sprintf(buff,"%d %-5s %-35s %-20s %-15s",++line, cust.m_CustomerID,
cust.m_CompanyName,
cust.m_ContactName ,
cust.m_Phone);
cout << buff << endl;
}
char ans[10] ;
ans[0] = '\0';
while (ans[0] != 'q')
{
cout << "What action? f)ind, i)nsert, d)elete, u)pdate, q)uit ";
cin.getline(ans, sizeof(ans));
switch(ans[0])
{
case 'i':
my_insert();
break;
case 'd':
if(my_find())
my_delete();
break;
case 'u':
if(my_find())
my_update();
break;
case 'f':
my_find();
break;
}
}
cust.Close();
session.Close();
ds.Close();
CoUninitialize();
cout << "That's All Folks" << endl;
return 1;
}
catch(...)
{
cout << "Unknown failure" << endl;
return -1;
}
}
void my_insert()
{
char buff[200];
cout << "Insert Customer ID ";
cin.getline(buff, sizeof(buff));
strcpy(cust.m_CustomerID, buff);
cout << "Enter Company Name ";
cin.getline(buff, sizeof(buff));
strcpy(cust.m_CompanyName, buff);
cout << "Enter Contact Name ";
cin.getline(buff, sizeof(buff));
strcpy(cust.m_ContactName, buff);
cout << "Enter Phone ";
cin.getline(buff, sizeof(buff));
strcpy(cust.m_Phone, buff);
HRESULT hr = cust.Insert(0); if(hr == S_OK)
{
cout << "INSERT OK \n";
}
else
{
cout << "INSERT FAILED\n";
}
}
void my_update()
{
char buff[100];
cout << "Update Contact Name\n";
cin.getline(buff, sizeof(buff));
strcpy(cust.m_ContactName, buff);
cout << cust.m_CustomerID << endl;
cout << cust.m_CompanyName << endl;
cout << cust.m_ContactName << endl;
cout << cust.m_Phone << endl;
HRESULT hr = cust.SetData(1);
if (FAILED(hr))
{
cout << "UPDATE FAILED\n";
}
else
{
cout << "UPDATE OK\n";
}
}
void my_delete()
{
cout << "Delete ? \n";
char ans[10];
cout << cust.m_CustomerID << endl;
cout << cust.m_CompanyName << endl;
cout << cust.m_ContactName << endl;
cout << cust.m_Phone << endl;
HRESULT hr;
cin.getline(ans,sizeof(ans));
if (ans[0] == 'y')
hr = cust.Delete();
else
return;
if (FAILED(hr))
{
cout << "DELETE FAILED\n";
}
else
{
cout << "DELETE OK\n";
}
}
bool my_find()
{
char custid[10];
char SQL[200];
cout << "Enter customer id ";
cin.getline(custid, sizeof(custid));
strupr(custid);
sprintf(SQL,"SELECT * FROM Customers WHERE CustomerID = '%s'", custid);
cust.Close();
HRESULT hr = cust.Open(session, SQL, &propset);
if(FAILED(hr))
{
cout << "Can't open find that customer\n";
cout << SQL << endl;
return false;
}
hr = cust.MoveFirst();
if(FAILED(hr))
{
cout << "Can't move to that customer\n";
return false;
}
cout << cust.m_CustomerID << endl;
cout << cust.m_CompanyName << endl;
cout << cust.m_ContactName << endl;
cout << cust.m_Phone << endl;
return true;
}