ActiveX Data Objects Database Programming in C++

Introduction

Although I intended this tutorial to be as straightforward as possible, I still can't dispense with the following information that I think is necessary to understand the motive behind making this tutorial.  Programming database applications in Visual C++ can be a pain because the tutorials that abound, especially those provided by Microsoft usually deal with the MFC database classes, CRecordset and CDaoRecordset.  While these classes are fine for most applications, we can't help but notice the trend towards OLE DB programming.  So what exactly is OLE DB?

OLE DB and ADO

According to Microsoft, OLE DB is the data access 'in' thing of the future.  This is because OLE DB is intended to provide a simple way of getting data in and out of databases, wherever they may be located.  Through OLE DB it is possible to access data contained in files, RDBMS, email, and anything that could possibly have data.  But programming OLE DB stuff is not for the faint-hearted since having the ability to access data from virtually any source adds to the complexity of  the technology.  What we need is something that's made specifically for database access.  Enter ActiveX Data Objects or ADO.  It would be right to say that ADO is a layer spread on top of OLE DB so that the complexity of OLE DB is hidden from the casual programmer.  Among its many intended functions, ADO was designed to create controls that could provide data access and control in web pages. So to be easily usable in web scripting languages like VBScript and programming environments such as Visual Basic, ADO has a minimal number of objects, namely:

•  Connection - used for establishing and maintaining a connection with a database.
•  Error - checked for database error information.
•  Command - used to execute commands in the database.
•  Parameter - used for passing variables and for calling parameterized queries or stored procedures.
•  Recordset - contains a set of records from the database.
•  Field - represents a single column in the record set.

Using ADO

There are two ways to use ADO in Visual C++.  The first is by using ADO controls like the ADO Data Control and the ADO Data-bound Grid.  I won't be discussing this method here since using controls does not provide us with much flexibility.  The second would be to use ADO programmatically.  That is, using the ADO classes directly.

Connecting to a Database and Data Retrieval

Importing the ADO DLL

To be able to use ADO we must first import the ADO DLL since ADO is not part of the MFC class hierarchy.  We can use the #import directive to import ActiveX DLLs, in this case, the msado15.dll.  The directive tells the compiler to extract the object information from this DLL and place the information in a couple of header files (.tlh and .tli) that are automatically included in our project. The following lines, usually placed in "stdafx.h" should do the trick:

#define INITGUID        // This constant is needed by ADO.
#import "C:\Program Files\Common Files\System\ADO\msado15.dll"\
rename_namespace("ADOCG") rename("BOF", "BOFILE") rename("EOF", "EOFILE")    // Rename some elements.
using namespace ADOCG;
#include "icrsint.h"    // Contains definition of some macros used to write ADO application.

In line no. 3, we renamed some elements in order to avoid potential conflict with other elements.  For example, we renamed EOF to avoid conflict with the EOFdefined in <stdio.h>.  Next we will try to establish a connection with a database.

Connecting to a Database

Before we can use ADO, we must first initialize the COM environment for our application with the following:

::CoInitialize(NULL);

This enables as to interact with ActiveX objects and so we are home-free to make calls to ADO.  After we are done with ADO we need to shut down COM by calling CoUnintialize() as follows:

::CoUninitialize();

After we initialize COM, we need to establish a connection to the database by using a Connection object pointer, the _ConnectionPtr.  This is done by declaring a _ConnectionPtr variable then initialize it with an instance of a Connection object.  Passing the UUID of the Connection object to the CreateInstance function of the Connection pointer does the trick:

_ConnectionPtr pCon;                        // _ConnectionPtr has only one underscore
pCon.CreateInstance(__uuidof(Connection));  // __uuidof has two underscores

After creating the Connection instance, we will open the actual connection to the database by using the member function Open():

pCon->Open(L"DSN=pcs;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=pcs;SERVER=localhost;PORT=3306;OPTION=35;STMT=",
           L"pcs", L"cygnus", adOpenUnspecified);

The Open() function takes four parameters.  The first parameter is the connection string which defines the OLE DB data source for the database.  For an idea of how a connection string like this is formed (MySQL ODBC DSN's only), see the following supplement.  The second parameter is the user ID.  The third is the password, and the fourth is the type of cursor to be used.  These cursor types are defined in the msado15.tlh that are created with the import directive:

enum CursorTypeEnum
{
    adOpenUnspecified = -1,
    adOpenForwardOnly = 0,
    adOpenKeyset = 1,
    adOpenDynamic = 2,
    adOpenStatic = 3
};

Executing Commands and Retrieving Records

After establishing a connection to the database, we can pass SQL commands to the database for execution by using a Command object.  Creating a Command object instance entails almost the same process as creating a Connection:

_CommandPtr pCmd;                        // Declare a CommandPtr variable
pCmd.CreateInstance(__uuidof(Command));  // Create an instance of the Command object

After creating the Command object instance, we must specify which database connection we're going to use:

pCmd->ActiveConnection = pCon;

Next, we'll specify the (SQL) command we will be executing:

pCmd->CommandText = "select * from item";

Since the SQL command above returns records, we have two options on how to proceed.  We can directly use the Execute() member of the Command object.  Execute() has the following syntax:

_RecordsetPtr Execute(VARIANT *RecordsAffected, VARIANT *Parameters, long Options);

The Options parameter of Execute() may be any of the following:

enum CommandTypeEnum
{
    adCmdUnspecified = -1,
    adCmdUnknown = 8,
    adCmdText = 1,
    adCmdTable = 2,
    adCmdStoredProc = 4,
    adCmdFile = 256,
    adCmdTableDirect = 512
};

The RecordsetPtr returned by Execute is valid only for commands that return record sets like the SQL SELECT statement.

_RecordsetPtr pRset;
pRset = pCmd->Execute(NULL, NULL, adCmdText);

A second option would be to specify the Command object as the record source for a Recordset:

_RecordsetPtr pRset;
pRset.CreateInstance(__uuidof(Recordset));
pRset->PutRefSource(pCmd);

Afterwards we will need to call on the Open() member of the Recordset object, which has the following syntax:

HRESULT Open(const _variant_t &Source,            // the record source (an SQL command)
             const _variant_t &ActiveConnection,  // the connection
             enum CursorTypeEnum CursorType,      // cursor type
             enum LockTypeEnum LockType,          // locking type 
             long Options);                       // kind of command

Since we already have a record source and a connection, we need to pass on a Null for the first two parameters.  The second parameter is the cursor type exactly like the values passed to the Open() member of the Connection object.  The third parameter specifies the locking mechanism to be used on the resulting record set, while the fourth would be exactly like the values passed in the Execute() member of the Command object.

enum LockTypeEnum
{
    adLockUnspecified = -1,
    adLockReadOnly = 1,
    adLockPessimistic = 2,
    adLockOptimistic = 3,
    adLockBatchOptimistic = 4
};

The code for executing the command and retrieving the record would then be:

_variant_t vtNull;
vtNull.vt = VT_ERROR;
vtNull.scode = DISP_E_PARAMNOTFOUND;
pRS->Open(vtNull, vtNull, adOpenDynamic, adLockUnspecified, adCmdText);

There is a more direct way of retrieving records that allows us to dispense with the Connection and Command objects altogether:

_RecordsetPtr pRset;
pRset.CreateInstance(__uuidof(Recordset));
pRS->Open(L"select * from item",
          L"DSN=pcs;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=pcs;SERVER=localhost;UID=pcs;PASSWORD=cygnus;PORT=3306;OPTION=35;STMT=",
          adOpenDynamic, adLockUnspecified, adCmdText);

Take note though  that it is still best to open a single connection to the database and use it for access instead of repeatedly specifying a connection string for opening record sets.

Navigating the Recordset

For navigating through the record set you can use the following member functions of the Recordset object:

HRESULT MoveFirst();

HRESULT MovePrevious();

HRESULT MoveNext();

HRESULT MoveLast();

Their names speak for themselves and I won't explain their purposes anymore.  In addition to these functions, the BOFILE and EOFILE variables (remember that we've renamed these variables when we imported the msado15.dll), provide information where we are in the record set.  BOFILE is true when we are past the first record while EOFILE is true when we are past the last record.  A typical application would be:

while (!pRset->EOFILE)
{
    // Display the contents of the record set.
    // . . .

    pRset->MoveNext();
}

Accessing and Updating Field Data in the Recordset

Accessing Field Values

Because ADO is intended to be used in other languages other than C++, there is no straightforward way to access data from a record set, since the values are of a variant type.  And because these values are variant, we need to convert them to their proper type to be able to make sense out of them.  For example:

_variant_t vtItem;
CString strItem;
vtItem = pRs->GetCollect(_variant_t("item"));
vtItem.ChangeType(VT_BSTR);
strItem = vtItem.bstrVal;

To make life easier I created a function that should bring some uniformity to the way we access data:

_variant_t FieldValue(const _RecordsetPtr pRS, const char *field)
{
    return pRS->GetCollect(_variant_t(field));
}

Applying this to the example above:

CString strItem = (const char *)_bstr_t(FieldValue(pRset, "item"));

double basePrice = double(FieldValue(pRS, "baseprice"));    // <-- Another example

Testing For NULL Fields

When we create table schemas sometimes it is logical to allow some fields to have null values.  When a field is null, trying to access its value ADO-style results to an error.  To avoid this error, we must find a way to test if a field has a null value.  The following function does just this.

BOOL IsNull(_variant_t v)
{
    return (v.vt == VT_NULL);
}

Updating Field Values

Updating field values entails the same tedious process.  For example:

_variant_t vtItem, vtValue;
vtItem.SetString("item");
vtValue.SetString("Power drill");
pRset->Update(vtItem, vtValue);

Again, to make life easier, I created a function or, rather, a series of overloaded functions in the spirit of the one I wrote to access data, to bring uniformity in updating data.  The third parameter should provide you a clue on what each function does:

void FieldValue(_RecordsetPtr pRS, const char *field, const short value)
{
    pRS->Update(_variant_t(field), _variant_t(value, VT_I2));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const long value)
{
    pRS->Update(_variant_t(field), _variant_t(value, VT_I4));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const float value)
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const double value)
{
    pRS->Update(_variant_t(field), _variant_t(value, VT_R8));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const char *value)   // strings of type char*
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const wchar_t *value)    // wide strings of type wchar_t*
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, _bstr_t &value)    // strings of type _bstr_t
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const unsigned char value)
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const bool value)
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const CY &value)
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

void FieldValue(_RecordsetPtr pRS, const char *field, const DECIMAL &value)
{
    pRS->Update(_variant_t(field), _variant_t(value));
}

Applying to our example above:

FieldValue(pRset, "item", "Power drill");
FieldValue(pRset, "baseprice", 235.00);    // <-- Another example

Adding and Deleting Records

To add a new record to the record set call the Recordset object's AddNew() function.  Doing so adds a new, albeit, blank record to the record set while making that record the current one.  After the record is added you can begin to update each field value using the PutCollect().  For example:

pRset->AddNew();
pRset->PutCollect(_variant_t("item"), _variant_t("Lasgun"));
pRset->PutCollect(_variant_t("specification"), _variant_t("High-powered, medium range"));
pRset->PutCollect(_variant_t("baseprice"), _variant_t(3456.50));
pRset->PutCollect(_variant_t("actualprice"), _variant_t(3400.00));
pRset->PutCollect(_variant_t("supplier"), _variant_t(1));

To delete a record, call the Delete() member function of the Recordset object, which has the syntax:

HRESULT Delete(enum AffectEnum AffectRecords);

Where AffectEnum has the following values:

enum AffectEnum
{
    adAffectCurrent = 1,
    adAffectGroup = 2,
    adAffectAll = 3,
    adAffectAllChapters = 4
};

For example, to delete the current record:

pRset->Delete(adAffectCurrent);
pRset->MovePrevious();
if (pRset->BOFILE)
    pRset->MoveNext();

Finding a Specific Record

To find the specific record in the record set, use the member function Find(), which has the syntax:

HRESULT Find(_bstr_t Criteria, long SkipRecords, enum SearchDirectionEnum SearchDirection, const _variant_t &Start = vtMissing);

Where SearchDirectionEnum has the values:

enum SearchDirectionEnum
{
    adSearchForward,
    adSearchBackward
};

For example, to find the item that has the id of 23 after and including the current record:

pRet->Find("itemdid = 23", adSearchForward);

Error Handling

Most of the member functions of the ADO objects return an HRESULT value that will give us a clue of the how an operation fares.  However, it would be far elegant and better to use the C++ try and catch mechanism for handling ADO errors.  ADO objects throw an exception of type _com_error whenever an error is encountered.  The Description() member of _com_error returns a meaningful description of the error encountered.  For example:

try
{
    // Delicate ADO operations normally go here
    // . . . 
}

catch (_com_error &e)    // Catch any error...
{
    cerr << endl << (const char *)e.Description() << endl;  // ...then show its description.
}

Sample Code

This tutorial comes with supplementary sample programs that will further demonstrate the concepts elaborated above.

•  ADOTemplate - A console applet that demonstrates opening a connection to the database, executing an SQL command and navigating through a record set.  [screenshot]
   
•  ADOMFC - A program demonstrating how the concepts in the tutorial could be applied in creating a simple but concrete database application in MFC.  [screenshot]
   
•  MySQL commands - SQL statements used in creating the pcs database used in the examples

Future Installments

Watch out for Part II of this tutorial which would include a detailed walkthrough of the creation of the MFC example program above and another program with a different approach to accessing and updating field values using ADO record binding.  'Till then, happy new year and happy hacking!!!

Back to Programming Page

Copyright 2003-2004 Mark Jundo P. Documento