Here is an extended example of the console mode OLEDB program that has Insert/Update/Delete functions:
//file: olsamp.cpp
//auth: dick warg
//date: 3/30/2000
//func: minimal oledb program
#include <atldbcli.h>
#include <iostream>
using namespace std;
// define a class for the ACCESSOR to hold the data from the table
class myNwCust
{
public:
// data elements
char m_CustomerID[6];
char m_CompanyName[41];
char m_ContactName[31];
char m_Phone[25];
// column binding -- I only want these 4 fields (see MS documentation
// for examples of Multiple Accessor)
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(CCommand <CAccessor<myNwCust> >& cust);
void my_update(CCommand <CAccessor<myNwCust> >& cust);
void my_delete(CCommand <CAccessor<myNwCust> >& cust);
bool my_find(CSession& session, CDBPropSet* propset, CCommand <CAccessor<myNwCust> >& cust);
int main()
{
try{
// fire up COM
HRESULT hr = CoInitialize(0);
if(FAILED(hr))
{
cout << "Can't start COM!? " << endl;
return -1;
}
// do all the work inside here so the destructors get called before
// co-uninitialize
{
// declare the OLEDB objects INSIDE a block so they will be destroyed before
// CoUnitialize is called
CDataSource ds;
CSession session;
CCommand <CAccessor<myNwCust> > cust;
CDBPropSet propset(DBPROPSET_ROWSET);
// connect to the database
hr = ds.Open(_T("MSDASQL"), "OLE_DB_NWind_Jet", "sa", "");
if(FAILED(hr))
{
cout << "Can't open Nwind" << endl;
return -1;
}
/* *************************************************************************
The CDBPropSet controls the way the database gets opened
The documentation for all these properties can be found if you look in
the MSDN Help reference:
\Platform SDK\Database and Messaging Services\Microsoft Data Access SDK\
OLEDB Programmer's Reference\Part 3 Appendixes\Appendix C\Properties Table
IF YOU DON'T SET THESE PROPERTIES YOU WON'T BE ABLE TO SCROLL THE TABLE OR MAKE
CHANGES TO IT!!
In addition the table MUST have a Primary Key defined.
No primary key = NO INSERT, NO UPDATE & NO DELETE
If you use an automatic integer (identity column) in your table it must not be
in the Accessor that is used to INSERT to the table. This means that you and I must
use Multiple Accessors if there is an identity column for the table.
******************************************************************************/
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 );
// start the session
hr = session.Open(ds);
if(FAILED(hr))
{
cout << "Can't open Nwind SESSION" << endl;
ds.Close();
return -1;
}
// construct the query string
char mySQL[] = "SELECT * FROM Customers ";
// open the dataset
hr = cust.Open(session, mySQL, &propset) ;
/* use this form of open without a property set
if you only need to read the table
hr = cust.Open(session, mySQL) ; // <---- read only
*/
if(FAILED(hr))
{
cout << "Can't open Nwind TABLE" << endl;
session.Close();
ds.Close();
return -1;
}
int line = 0;
// read all the data
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;
}
// process some user interactions
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(cust);
break;
case 'd':
// you need to find a record before you can delete it
if(my_find(session, &propset, cust))
my_delete(cust);
break;
case 'u':
// you need to use find before you update too
if(my_find(session, &propset, cust))
my_update(cust);
break;
case 'f':
my_find(session, &propset, cust);
break;
}
}
cust.Close();
session.Close();
ds.Close();
}// the destructors get called before the CoUninitialize
cout << "That's All Folks" << endl;
CoUninitialize();
return 1;
}
catch(...)
{
cout << "Unknown failure" << endl;
return -1;
}
}
////////////////////////////// functions //////////////////////////////////////
void my_insert(CCommand <CAccessor<myNwCust> >& cust)
{
char buff[200];
cout << "Insert Customer ID ";
cin.getline(buff, sizeof(buff));
strcpy((char*)cust.m_CustomerID, buff);
cout << "Enter Company Name ";
cin.getline(buff, sizeof(buff));
strcpy((char*)cust.m_CompanyName, buff);
cout << "Enter Contact Name ";
cin.getline(buff, sizeof(buff));
strcpy((char*)cust.m_ContactName, buff);
cout << "Enter Phone ";
cin.getline(buff, sizeof(buff));
strcpy((char*)cust.m_Phone, buff);
HRESULT hr = cust.Insert(0); // <----- This is where we add the new record
if(hr == S_OK)
{
cout << "INSERT OK \n";
}
else
{
cout << "INSERT FAILED\n";
}
}
void my_update( CCommand <CAccessor<myNwCust> >& cust)
{
/* this is a simple minded update that only changes the contact name
but you get the idea */
char buff[100];
cout << "Update Contact Name\n";
cin.getline(buff, sizeof(buff));
strcpy((char*)cust.m_ContactName, buff);
cout << cust.m_CustomerID << endl;
cout << cust.m_CompanyName << endl;
cout << cust.m_ContactName << endl;
cout << cust.m_Phone << endl;
/* update the record.
The SetData() method actually does the update. The Update() Method
kinda flushes the changed data to the database, we don't need that for
this simple example.
*/
HRESULT hr = cust.SetData(1);
if (FAILED(hr))
{
cout << "UPDATE FAILED\n";
}
else
{
cout << "UPDATE OK\n";
}
}
void my_delete( CCommand <CAccessor<myNwCust> >& cust)
{
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(CSession& session, CDBPropSet* p_propset, CCommand <CAccessor<myNwCust> >& cust)
{
char custid[10];
char SQL[200];
cout << "Enter customer id ";
cin.getline(custid, sizeof(custid));
strupr(custid); // upper case for compare
sprintf(SQL,"SELECT * FROM Customers WHERE CustomerID = '%s'", custid);
cust.Close();
HRESULT hr = cust.Open(session, SQL, p_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;
}
