article

ADO programming in C++

Email
Submitted on: 1/4/2015 11:58:00 PM
By: Jason Beighel (from psc cd)  
Level: Beginner
User Rating: By 9 Users
Compatibility: C++ (general), Microsoft Visual C++
Views: 2593
 
     This article is intended to cover the basics of working with a database through ADO without diving into MFC, .NET, or any other deep Microsoft framework.

This article has accompanying files
 
				I'm not sure if a tutorial on
ADO database functions is really needed.  It is better documented
for use under .NET or in the MFC structure.  I'm not much of a fan
of either of these so I found a way to use it without getting into too
much of the Microsoft stuff.  The code examples given here and the
attached sample program was built and tested under MS Visual Studio 6,
I haven't tried it under any other compilers but it ought to work there
as long as you have the Windows API libraries available.

To begin you need to include the header file stdafx.h.  You also will need to connect to the msado15.dll library.  The easiest way to do that is with an #import statement, like this:

#import "c:\program files\common files\system\ado\msado15.dll" rename("EOF", "EOFile")

The reason for the rename() during the #import is so that any existing EOF markers are not overwritten.  The next thing needed is a variable that will initialize and un-initialize the OLE libraries.  You ought to be able to call the functions directly, I usethe variable so I can't get them out of place or forget to cleanup.  Here is the structure I use for this purpose:

struct StartOLEProcess
{
    StartOLEProcess()
    {
        ::CoInitialize(NULL);
    }
    ~StartOLEProcess()
    {
        ::CoUninitialize();
    }
} _start_StartOLEProcess;

Now that everything is setup, lets get into the function that needs to work on the database.  We will need several variables to connect to the database.  First is a variable used as a connection to the actual database, very similar to a FILE * variable when working with a file.  We will also need a recordset variable to store any data retrieved from the database.  Now the recordset holds the data we got from the database, we can't access the data directly from the recordset.  To actually read the database we need a variable for each field in the database.  When we work on a database ADO can give us information about what records were affected by our actions, this goes a little beyond the scope of this tutorial but we need a variable for this to satisfy some function calls.  And finally don't forget the variable used to initialize OLE.  Here is the declarations of these variables:

/*The database connection variable*/
ADODB::_ConnectionPtr Con = NULL;
/*The recordset variable*/
ADODB::_RecordsetPtr RecSet = NULL;
/*A single field pointer, you will probably need multiple for real database work*/
ADODB::FieldPtr Field;
/*Variable to get the affected records*/
VARIANT *RecordsAffected = NULL;
/*The important OLE variable*/
StartOLEProcess OLEVar;

You may want to bundle all those variables into the StartOLEProcess structure to keep things organized, but that is up to you.  The first thing we need to do is initialize the connection variable.  We do this by calling its member function CreateInstance().  This function will return 0 if it is successful.  This is the only function that can be error tested this way, all other function calls use the try/catch method of error detection, I won't include the error detection for them to keep the code samples shorter.

if (Con.CreateInstance(__uuidof(ADODB::Connection), NULL) != 0)
{
    printf("Couldn't create the connection variable");
}

Assuming all went well there we can try to connect to the database.  To do this we need to create a connection string and call the Open() member function of the connection pointer.  The connection string describes where the database is located and what type of database it is.  ADO has the ability to connect to many types of databases and each type uses a different connection string.  For an example connection I will use a standard Microsoft Access database.  For that our connection string looks like this "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Database.mdb", assuming the database file is C:\Database.mdb.  The Open() function takes 4 parameters, the first is the connection string, the second is a user name, the third is a password, and the fourth is an options variable that selects the access necessary to get the default read/write access use 0.  The username and password are suplied to the database if the database is encrypted or requires certain permissions to be accessed.  Take notice here that the Open() function is accessed as if the connection varialbe was apointer, unlike the CreateInstance() funciton.  This is because the connection variable we created is a pointer, the CreateInstance() function created a true connection variable for it to point to, there isn't any way I have found to directly create a connection variable you always have to go through a pointer like this.  Here is how that would look:

try
{
    Con->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Database.mdb", "", "", 0);
}
catch (int Exception)
{
    /*I know I said I wouldn't show the try/catch but I felt I should show it at least once*/
    printf("Unable to connect to the database");
}

Now that we have the database opened the easiest way to interact with the database is through SQL statements.  I won't to go into SQL since there are a lot of good tutorials already available, the statements I use most often are SELECT to retieve data from the database, INSERT to add records to the database, and UPDATE to modify a record in the database.  You can use the Execute() member function of the connection variable to pass a SQL statement to the database.  The Execute() function takes three parameters, the first is the SQL statement as a NULL terminated string, the second is the records affected variable, the third value is for options I use 0 again to get the defaults.  The Execute() function will throw an error if there is a problem processing the SQL statement so be sure that you catch the exceptions from this function.  The Execute() function will return a recordset when a SELECT statement is passed.  In this way you can retrieve data from the database, to do this just put the return into the recordset variable we created before.  Here is how that looks:

RecSet = Con->Execute("SELECT * FROM Table1", RecordsAffected, 1);

As I stated earlier you can't get the data directly from the recordset you need to have additional variables for each field you want the data from.  You set the field variables to show the data from a specific field that was returned by the select statement.  The field variables will only give the value of that one field from the current record selected by the recordset variable.  You can use the MoveNext() and MovePrevious() member functions of the recordset variable to navigate through all the returned records.  The field variables will advance through the records with the recordset variable, you don't need to do anything special to have them update.  The recordset variables have a EOFile member (normally is EOF, but it was renamed during the #import)  if this value is not 0, then the end of the recordset has been reached.  A simple while loop can be used to scan through all the records in your recordset.  Here is how that would look:

/*Link the Field variable to the field named "Field1"*/
Field = RecSet->Fields->GetItem("Field1");

while (!RecSet->EOFile)
{
    /*Access the data from the Field variable*/

    RecSet->MoveNext();
}

That comment in the while loop is hiding some fairly important code.  The reason for that is because each field in a database can be a different type of data, and each datatype will need to be handled slightly differently.  Generally speaking there are three types of data in a database, strings, numbers, and dates.  Each one has a subset of data in it, but programatically you can handle them in general terms.  To determine what type of data the field is check the Type member of the field variable.  This will be an integer value that gives the type of data in this field, unfortunately I don't have a list of all the possible values but I can give a few of them from Access databases:
    7      Boolean
    11    Boolean
    2      Integer
    202  String
    203  String
To find the type number of various field types I created a database with several field types and pulled a recordset for all of the fields then compared the type numbers with the field type in the database.  I know its boring and tedious, but I don't know of any other way of retrieving accurate field types.  Inside of each field variable there is a member called Value that is of type _variant_t which can be used to get the actual field data.  In Value only the appropriate value will be filled in, for example if the database field type is an integer the Value member won't give the value as a string, only as an integer.  There may be a way to perform that conversion but I'm not familiar enough with the _variant_t variable type to do that.  Integer values can be accessed straing out of the Value member, strings through need to be converted into the standard NULL terminated array.  To do that you can use the function WideCharToMultiByte(), this function takes 8 parameters.  Only the third, fifth, and sixth paramters are really needed, the rest can always be the same.  The third parameter is the string to be converted, the fifth parameter is a pointer to a buffer to store the converted string, and the sixth is the size of the buffer.  Here are a few if statements that can be used to handle the data from the field variable:

if ((Field->Type ==  202)||(Field->Type == 203))
{/*Handling a string database field type*/
    char String[100];
    WideCharToMultiByte(CP_ACP, 0, Field.bstrVal, -1, String, 100, NULL, NULL);
    printf("Data: %s\n", String);
}

if ((Field->Type == 7)||(Field->Type == 11))
{/*Handling a boolean database field type*/
    printf("Data: %d\n", Field->Value.boolVal);
}

if (Field->Type == 2)
{/*Handling a integer database field type*/
    printf("Data: %d\n", Field->Value.intVal);
}

Once you have done all the work you needed with the recordset and the database you have to do a little cleanup.  The recordsets need to be closed as well as teh database connection, both have convenient member functions called Close().  Here is how they look:

RecSet->Close();
Con->Close();

This by no means covers all of the aspects of ADO, for that matter it doesn't even cover all of the aspects of the functions I mentioned.  There are a lot of options you can set in the connection string when connecting to a database, and dozens of additional database field types that will need to be dealt with.  The idea of this article was only to present the background and basics for working with ADO database connections.  Hopefully that much at least was covered.

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Other 2 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.