OLE DB Templates - A light and simple Data Access Method

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.

//file: olsamp.cpp
//auth: dick warg
//date: 6/25/99
//func: minimal oledb program

#include <atldbcli.h>
#include <iostream>
using namespace std;

// define a class to hold the data from the table
class myNwCust
{
public:
    // data elements
    TCHAR m_CustomerID[6];
    TCHAR m_CompanyName[41];
    TCHAR m_ContactName[31];
    TCHAR m_Phone[25];
    
    // column binding  -- I only want these 4 fields
    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()
};

// declare the OLEDB objects
CDataSource ds;
CSession    session;
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;
        }
        
        // 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;
        }
        
        // 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
        TCHAR mySQL[] = "SELECT CustomerID, CompanyName, ContactName, \
            Phone FROM Customers";
        
        // open the dataset
        hr = cust.Open(session, mySQL);
        if(FAILED(hr))
        {
            cout << "Can't open Nwind TABLE" << endl;
            session.Close();
            ds.Close();
            return -1;
        }
        
        // read all the data
        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:

//file: olsamp.cpp
//auth: dick warg
//date: 8/9/99
//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();
void my_update();
void my_delete();
bool my_find();


// declare the OLEDB objects GLOBALLY so we can use them in functions
CDataSource ds;
CSession    session;
CCommand <CAccessor<myNwCust> > cust;
CDBPropSet propset(DBPROPSET_ROWSET);

int main()
{
    
    try{
        // fire up COM
        HRESULT hr = CoInitialize(0);
        if(FAILED(hr))
        {
            cout << "Can't start COM!? " << endl;
            return -1;
        }
        
        // 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();
                break;
            case 'd':
                // you need to find a record before you can delete it
                if(my_find())
                    my_delete();
                break;
            case 'u':
                // you need to use find before you update too
                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;
       }
}


////////////////////////////// functions //////////////////////////////////////
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);  // <----- This is where we add the new record
    if(hr == S_OK)
    {
        cout << "INSERT OK \n";
    }
    else
    {
        cout << "INSERT FAILED\n";
    }
    
    
}

void my_update()
{
/* 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(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()
{
    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);  // upper case for compare
    
    
    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;
}