Click here to Skip to main content
14,492,338 members

ADO: 101-level Tutorial

Rate this:
4.48 (22 votes)
Please Sign up or sign in to vote.
4.48 (22 votes)
2 May 2004CPOL
101-level tutorial on ActiveX Data Objects

Introduction

ActiveX Data Objects, more commonly known as ADO, is a popular database access technology in the Microsoft world. Microsoft has developed and promoted this programming interface for several years now, and in the case of .NET, reinvented large portions of it to fit into the world of .NET (along with a new branding -- ADO.NET).

Although ADO.NET has arrived on the scene, there is still much legacy ADO code out there written in C++. As a result, programmers who did not first grow-up with ADO will be faced with maintaining legacy ADO code in C++ for many years to come.

In this short article, I will provide a simple introduction to ADO in C++. This 101-level tutorial will highlight the following commonly performed ADO operations in:

  • Initializing the COM subsystem
  • Establishing a database connection
  • Issuing a simple select statement
  • Retrieving the record results
  • Closing the result and connection objects

The target audience for this article is someone who is familiar with C++ and has some exposure to ActiveX Template Library (or ATL) but has done little or no ADO programming. The code I have included here can be cut-and-paste into a new C++ source file rather quickly, and I encourage anyone who needs to re-use this code to grab it and run.

One of the potential issues with using ADO from C++ is managing COM objects. If you use the smart pointers correctly, then the objects will automatically be released when they go out of scope. The example I have will demonstrate some of this but it is best to get a book like XYZ if you want further details.

Let’s get started!! First, we have to choose the version of ADO we want to use.

1. Import the type Library

There are several ADO type libraries that can be imported, and they differ based on version. The list of type libraries available will differ based on what Windows operating system and developer tools (Visual C++ 6, Visual C++ 7.x, etc.) you have installed. In the subdirectory, C:\Program Files\Common Files\System\ado on my system, the list includes the following:

  • msado20.tlb
  • msado21.tlb
  • msado25.tlb
  • msado26.tlb

For this example, I selected the latest type library file, version msado26.tlb. Then, I added the following import statement:

#import "c:\program files\common files\system\ado\msado26.tlb" no_namespace rename( "EOF", "A_EOF" )

2. Initialize COM and Create a Connection Object

The first steps in my example include initializing COM and creating an instance of the ADO Connection object.

HRESULT hr = ::CoInitialize(NULL);
if (FAILED(hr))
{
      return false;
}
_ConnectionPtr pConnection;
hr = pConnection.CreateInstance(__uuidof(Connection));
if (FAILED(hr))
{
      return false;
}

3. Make the Database Connection

Now, we are ready to open a database connection. This operation is placed within a C++ try/catch block. If this operation fails, then an exception of type _com_error is thrown and immediately caught:

try
{
      pConnection->Open(strConnectionString,
         _T(""), _T(""), adOpenUnspecified);
      // ...
}
catch (_com_error&)
{
      ::CoUninitialize();
      // ...
}

4. Construct a SQL Statement

Finally, we are reading to execute a SQL statement. I am going to use the simplest of all examples to issue a “SELECT GETDATE()” which returns a one row/column result.

_CommandPtr pCommand(__uuidof(Command));
pCommand->ActiveConnection = pConnection;
pCommand->CommandText = "SELECT GETDATE()";

5. Execute the Statement and Retrieve the Results

ADO, unlike other database abstraction layers such as JDBC, statements are executed on the result set object. To execute a statement, you can create a command object and set it or link it directly to the recordset object also created. The other option is to completely bypass the processes of creating command objects altogether and execute the recordset immediately specifying the SQL statement text on the recordset object.

_RecordsetPtr pRecordSet(__uuidof(Recordset));
 pRecordSet->PutRefSource(pCommand);
 _variant_t vNull(DISP_E_PARAMNOTFOUND, VT_ERROR);
  pRecordSet->Open(vNull, vNull, adOpenDynamic,
     adLockOptimistic, adCmdText);

 char szTimeStamp[64] = { 0 };
 if (!pRecordSet->A_EOF)
 {
       _Recordset **ptrResults = NULL;
       pRecordSet->QueryInterface(__uuidof(_Recordset),
          (void **) ptrResults); 

The code above retrieves the resulting recordset based. First however, it checks for the EOF state before it reading the rows and columns returned.

6. Iterate Over the Results

The recordset object that is returned is very simple; it only contains a single row and column of data.

// SELECT GETDATE() returns one row without a column name
_variant_t vField(_T(""));
_variant_t vResult;
vResult = pRecordSet->GetFields()->GetItem(vField)->Value;
_bstr_t strTimeStamp(vResult);
strncpy(szTimeStamp, (char*) strTimeStamp, 63);
if (szTimeStamp > 0)
{
      char szFeedback[256] = { 0 };
      sprintf(szFeedback, "SQL timestamp is: %s", szTimeStamp);
      AfxMessageBox(szFeedback, MB_OK | MB_ICONINFORMATION, 0);
}

7. Release Resources

pRecordSet->Close();
pConnection->Close();
::CoUninitialize();

Here, we finish up by closing both the recordset and the connection and releasing resources allocated for COM.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kevin Wittmer
Web Developer
United States United States
. . . . . . . . . . . .

Comments and Discussions

 
GeneralWTL/ATL Pin
manosza10-Jan-09 20:58
Membermanosza10-Jan-09 20:58 
QuestionHow to get fields by index Pin
eusto13-May-06 8:03
Membereusto13-May-06 8:03 
AnswerRe: How to get fields by index Pin
Snorri Kristjansson13-May-06 10:35
professionalSnorri Kristjansson13-May-06 10:35 
GeneralWorked Out Pin
Ye Olde Pub30-Mar-06 17:10
MemberYe Olde Pub30-Mar-06 17:10 
GeneralEOF -> A_EOF Pin
Tim Abell11-May-04 12:48
MemberTim Abell11-May-04 12:48 
GeneralRe: EOF -> A_EOF Pin
Tim Abell10-Aug-05 6:15
MemberTim Abell10-Aug-05 6:15 
Generalvery good article Pin
Vadim Tabakman3-May-04 13:54
MemberVadim Tabakman3-May-04 13:54 
I have just been using ADO for my database access. Still learning though, but just as I got most of code working that I wanted, you release this article. Couldn't you have published it 2 weeks ago?? Would have save me lost of time haha.

I think this is great for people just getting into using ADO or trying to figure out why ADO would be an option.

Thanx for the article.;)
General. Pin
wb3-May-04 3:33
Memberwb3-May-04 3:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 2 May 2004

Stats

100.8K views
2.5K downloads
61 bookmarked