ADO : 101-level tutorial

• Download source - 6.94 Kb

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 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
• Issue 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 to 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 is 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 simple of all examples 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 record set object also created. The other option is to completely bypass the processes of creating command objects altogether and execute the record set immediately specifying the SQL statement text on the record set 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 record set based. First however, it checks for the EOF state before it reading the rows and columns returned.

6. Iterate over the results

The record set 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 record set and the connection and releasing resources allocated for COM.