ADO Database Access with C++

ADO (ActiveX Data Objects) database access with C++ -- the short version.

As a C++ programmer, I sometimes envy programmers of scripting languages because they think that accessing COM objects is so very easy. Those languages do all of the underlying work to instantiate the object, handle the data type issues, and so forth. For instance, in my article, HTA - Hypertext Application tutorial, I needed a trivial example to illustrate JScript usage, so I use JScript to open and display an ADO recordset -- in just a few lines of code.

The first time I tried that in C++, I nearly drowned in the complexity. For instance, to access a field value, in C++, you would normally create and initialize a VARIANT structure (a scary proposition in and of itself) and use that as the index into some sort of Fields collection. The result? Nothing useful (yet) -- a pointer to a Field object. If you can find it in the documentation, you'll see that it has a Value property, but all that yields is another one of those scary VARIANT values, and you need to get that into some sort of string variable for display purposes. With COM objects, we need to use BSTR datatype, and even that may sound intimidating if you are use to simple char* data for strings.

Compare all that to the JScript version:

var s= oRs.Fields('CustomerID' ).Value;

Open in new window

Well, it's not as bad as it might seem. Microsoft eventually created some objects that simplify use of VARIANT and BSTR values -- the _bstr_t and _variant_t classes. Using these, we can boil down database column access to...

_bstr_t s= oRs->Fields->Item[_variant_t("CustomerID")]->Value;

Open in new window

...because the objects know how to convert from one thing to another and do it all behind the scenes. After struggling with these data types and COM objects, I eventually found a C++ example that distilled all of the arcane ADO mumbo-jumbo down to the minimum -- and that was what I needed to get started.

I've written my own version of a minimal ADO access program and I'll describe what's going on as simply as possible. Here is the complete program, written as a Console application:

// ADOinCPP.cpp
                      //
                      #include <stdio.h>
                      #import "c:\program files\common files\system\ado\msado15.dll" rename ("EOF","adoEOF") no_namespace
                      
                      struct InitOle {
                          InitOle()  { ::CoInitialize(NULL); }
                          ~InitOle() { ::CoUninitialize();   }
                      } InitOle_tag;   
                      
                      //------------------ utility fns to simplify access to recordset fields
                      _bstr_t RsItem( _RecordsetPtr p, BSTR fldName ) {  // by field name
                          return( p->Fields->Item[_variant_t(fldName)]->Value );
                      }
                      _bstr_t RsItem( _RecordsetPtr p, long nIdx ) { // by field # (0 is first)   
                          return( p->Fields->Item[_variant_t(nIdx)]->Value );
                      }
                      //-------------------------------- The Program ----------------
                      void main(){
                          _RecordsetPtr spRs;
                          HRESULT hr;
                          _bstr_t sConn= "driver={sql server};SERVER=local);Database=pubs;UID=sa; PWD=;"; 
                          _bstr_t sSQL= "SELECT au_fname+' '+au_lname, phone FROM authors"; 
                          try{
                              hr= spRs.CreateInstance( __uuidof(Recordset) );
                              if FAILED(hr) printf("CreateInstance failed\n");
                      
                              hr= spRs->Open( sSQL, sConn, adOpenForwardOnly, adLockReadOnly, adCmdText );
                              if FAILED(hr) printf("Open failed\n");
                      
                              printf("NAME                      PHONE\n"); 
                              while( !(spRs->adoEOF) ) {
                                  printf("%-25.25s %s\n",
                                          (char*) RsItem( spRs, 0L ), 
                                          (char*) RsItem( spRs, L"phone")  // or 1L
                                  );
                                  spRs->MoveNext();
                              }
                              spRs->Close();
                          }
                          catch( _com_error &e) {
                              printf("Error:%s\n",(char*)e.Description());
                          }
                      }

Open in new window

About The Program
Let's look at the example program in detail:

Line 4 is the key to using the ADO object model in your C++ programs. The #import directive causes the compiler to open the DLL and read all about the object model that is exposed. It creates a special header file that defines such things as _RecordsetPtr and its methods and attributes. The
rename ("EOF","adoEOF") no_namespace
part is there because without it, there is an ambiguity in the meaning of EOF. Like lots of things in programming land, just use it "as is" in the example and then take time to wonder about when your project deadline has been met.

Lines 6-9 define a global object that does just this: It makes a call to the CoInitialize API at the start of the program and calls CoUninitialize at the end. CoInitialize must be called before we try to use any COM objects. We could put it inline (early in the main function), but this technique is a good one to know: Global objects like this get instantiated (constructed) before other objects.

Lines 12-17 are a couple of little utility functions that take some of the sting out of accessing the recordset data. Together, they act as one overloaded function: We can pass either a string (column name) or an integer (column position) as the second parameter. It extracts the recordset field value while taking care of various conversions, and it returns a _bstr_t value that can be cast directly into a char* and used without fear.

The main function creates an instance of a _Recordset, and opens it in lines 25-28. You may see this done a different way -- create a _Connection object, and pass it into the _Recordset. But the _Recordset's Open method provides a way to create an internal database connection behind the scenes, so I've used that. It is probably more common to explicitly create a long-lived _Connection object and then reuse it as needed, say for other _Recordset objects.

Note: You will need to tailor your connection string (sConn in the program) to access your own database. The one used here accesses the Pubs database that comes with SQL Server.

Some notes on the Open call used here (line 28):

The sSQL string will more typically request individual fields -- say for first name, last name, and phone number. But here I used the tactic of having the DBMS return the first and last name as a single field, concatenated at the server. It is an unnamed field, so I use an integer index parameter.

As this is a standard SELECT statement, I could append a WHERE clause and/or an ORDERED BY clause without needing to know anything special about how to do that in ADO (i.e., the Filter and Sort properties).

The combination of
adOpenForwardOnly, adLockReadOnly
is your best friend for purposes of performance. It's called a "firehose cursor" because the DBMS can blow through the data with the minimum overhead and without jamming up other processes that are accessing the same table.

Finally (in lines 32-38), we get to a familiar while ... display ... moveNext loop. Here I call upon my RsItem utility functions to extract data and display it. I use the printf command because I liked it when I saw it in that book by Kernighan and Ritchie in 1978 and I still like it.

Note also that I've wrapped the entire sequence with a try...catch construct. There is a lot that can go wrong when using COM objects in C++ and you will surely get bitten if you don't provide some sort of exception handler.

Summary:

To use ADO objects in your C++ program:
1) #import the ado DLL
2) Call CoInitialize
3) Instantiate a _RecordsetPtr and Open it, passing a connection string and a SELECT statement.
4) Access the data by using the Fields collection; we created a pair of functions to simplify that.
5) Cycle through the recordset using MoveNext until the end of the recordset is reached.

This short article is a starting point for ADO access via C++. The ADO documentation can be somewhat daunting and I wanted to put a more friendly face on it. The example program has been cut (almost) to the bone -- everything you need and not much that you don't.

References:

ADO API Reference
http://msdn.microsoft.com/en-us/library/ms678086(VS.85).aspx

ADO Code Examples in Visual C++
http://msdn.microsoft.com/en-us/library/ms677563(VS.85).aspx
Earlier version of Microsoft's ADO documentation were aimed mainly at Visual Basic programmers. Recent versions now include many examples for C++ programmers.

msado15.tlh and msado15.tli
After one compiler pass, you will find these two files in your output directory. They are created by the #import statement. They are long and complicated, but they can be used as a reference -- all of the methods and enumerations of all of the ADO objects are in there.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author, please click the Yes button near the:
Was this article helpful?
label that is just below and to the right of this text. Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Comments (1)