ADO programming in C++

<span style="color: rgb(51, 51, 255);">I'm not sure if a tutorial on
ADO database functions is really needed. &nbsp;It is better documented
for use under .NET or in the MFC structure. &nbsp;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. &nbsp;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.</span><br
 style="color: rgb(51, 51, 255);">
<br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">To begin you need to include the
header file stdafx.h. &nbsp;You also will need to connect to the
msado15.dll library. &nbsp;The easiest way to do that is with an
#import statement, like this:</span><br>
<br>
#import "c:\program files\common files\system\ado\msado15.dll"
rename("EOF", "EOFile")<br>
<br>
<span style="color: rgb(51, 51, 255);">The reason for the rename()
during the #import is so that any existing EOF markers are not
overwritten. &nbsp;The next thing needed is a variable that will
initialize and un-initialize the OLE libraries. &nbsp;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. &nbsp;Here is the structure I
use for this purpose:</span><br>
<br>
struct StartOLEProcess<br>
{<br>
&nbsp;&nbsp;&nbsp; StartOLEProcess()<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ::CoInitialize(NULL);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp; ~StartOLEProcess()<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ::CoUninitialize();<br>
&nbsp;&nbsp;&nbsp; }<br>
} _start_StartOLEProcess;<br>
<br>
<span style="color: rgb(51, 51, 255);">Now that everything is setup,
lets get into the function that needs to work on the database. &nbsp;We
will need several variables to connect to the database. &nbsp;First is
a variable used as a connection to the actual database, very similar to
a FILE * variable when working with a file. &nbsp;We will also need a
recordset variable to store any data retrieved from the database.
&nbsp;Now the recordset holds the data we got from the database, we
can't access the data directly from the recordset. &nbsp;To actually
read the database we need a variable for each field in the database.
&nbsp;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. &nbsp;And finally don't forget the variable used to
initialize OLE. &nbsp;Here is the declarations of these variables:</span><br>
<br>
/*The database connection variable*/<br>
ADODB::_ConnectionPtr Con = NULL;<br>
/*The recordset variable*/<br>
ADODB::_RecordsetPtr RecSet = NULL;<br>
/*A single field pointer, you will probably need multiple for real
database work*/<br>
ADODB::FieldPtr Field;<br>
/*Variable to get the affected records*/<br>
VARIANT *RecordsAffected = NULL;<br>
/*The important OLE variable*/<br>
StartOLEProcess OLEVar;<br>
<br>
<span style="color: rgb(51, 51, 255);">You may want to bundle all those
variables into the StartOLEProcess structure to keep things organized,
but that is up to you. &nbsp;The first thing we need to do is
initialize the connection variable. &nbsp;We do this by calling its
member function CreateInstance(). &nbsp;This function will return 0 if
it is successful. &nbsp;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.</span><br>
<br>
if (Con.CreateInstance(__uuidof(ADODB::Connection), NULL) != 0)<br>
{<br>
&nbsp;&nbsp;&nbsp; printf("Couldn't create the connection variable");<br>
}<br>
<br>
<span style="color: rgb(51, 51, 255);">Assuming all went well there we
can try to connect to the database. &nbsp;To do this we need to create
a connection string and call the Open() member function of the
connection pointer. &nbsp;The connection string describes where the
database is located and what type of database it is. &nbsp;ADO has the
ability to connect to many types of databases and each type uses a
different connection string. &nbsp;For an example connection I will use
a standard Microsoft Access database. &nbsp;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.
&nbsp;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. &nbsp;The username and
password are suplied to the database if the database is encrypted or
requires certain permissions to be accessed. &nbsp;Take notice here
that the Open() function is accessed as if the connection varialbe was
apointer, unlike the CreateInstance() funciton. &nbsp;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. &nbsp;Here is how that
would look:</span><br>
<br>
try<br>
{<br>
&nbsp;&nbsp;&nbsp; Con-&gt;Open("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\\Database.mdb", "", "", 0);<br>
}<br>
catch (int Exception)<br>
{<br>
&nbsp;&nbsp;&nbsp; /*I know I said I wouldn't show the try/catch but I
felt I should show it at least once*/<br>
&nbsp;&nbsp;&nbsp; printf("Unable to connect to the database");<br>
}<br>
<br>
<span style="color: rgb(51, 51, 255);">Now that we have the database
opened the easiest way to interact with the database is through SQL
statements. &nbsp;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. &nbsp;You can
use the Execute() member function of the connection variable to pass a
SQL statement to the database. &nbsp;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. &nbsp;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. &nbsp;The
Execute() function will return a recordset when a SELECT statement is
passed. &nbsp;In this way you can retrieve data from the database, to
do this just put the return into the recordset variable we created
before. &nbsp;Here is how that looks:</span><br>
<br>
RecSet = Con-&gt;Execute("SELECT * FROM Table1", RecordsAffected, 1);<br>
<br>
<span style="color: rgb(51, 51, 255);">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. &nbsp;You set the
field variables to show the data from a specific field that was
returned by the select statement. &nbsp;The field variables will only
give the value of that one field from the current record selected by
the recordset variable. &nbsp;You can use the MoveNext() and
MovePrevious() member functions of the recordset variable to navigate
through all the returned records. &nbsp;The field variables will
advance through the records with the recordset variable, you don't need
to do anything special to have them update. &nbsp;The recordset
variables have a EOFile member (normally is EOF, but it was renamed
during the #import) &nbsp;if this value is not 0, then the end of the
recordset has been reached. &nbsp;A simple while loop can be used to
scan through all the records in your recordset. &nbsp;Here is how that
would look:</span><br>
<br>
/*Link the Field variable to the field named "Field1"*/<br>
Field = RecSet-&gt;Fields-&gt;GetItem("Field1");<br>
<br>
while (!RecSet-&gt;EOFile)<br>
{<br>
&nbsp;&nbsp;&nbsp; /*Access the data from the Field variable*/<br>
<br>
&nbsp;&nbsp;&nbsp; RecSet-&gt;MoveNext();<br>
}<br>
<br>
<span style="color: rgb(51, 51, 255);">That comment in the while loop
is hiding some fairly important code. &nbsp;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.
&nbsp;Generally speaking there are three types of data in a database,
strings, numbers, and dates. &nbsp;Each one has a subset of data in it,
but programatically you can handle them in general terms. &nbsp;To
determine what type of data the field is check the Type member of the
field variable. &nbsp;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:</span><br
 style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">&nbsp;&nbsp;&nbsp;
7&nbsp;&nbsp;&nbsp; &nbsp; Boolean</span><br
 style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">&nbsp;&nbsp;&nbsp;
11&nbsp;&nbsp;&nbsp; Boolean</span><br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">&nbsp;&nbsp;&nbsp; 2 &nbsp;
&nbsp; &nbsp;Integer</span><br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">&nbsp;&nbsp;&nbsp; 202
&nbsp;String</span><br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">&nbsp;&nbsp;&nbsp; 203
&nbsp;String</span><br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">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. &nbsp;I know its boring and
tedious, but I don't know of any other way of retrieving accurate field
types. &nbsp;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. &nbsp;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.
&nbsp;There may be a way to perform that conversion but I'm not
familiar enough with the _varian<span style="color: rgb(51, 51, 255);">t_t
variable type to do that. &nbsp;Integer values can be accessed straing
out of the Value member, strings through need to be converted into the
standard NULL terminated array. &nbsp;To do that you can use the
function </span></span><span style="color: rgb(51, 51, 255);">WideCharToMultiByte(),
this function takes 8 parameters. &nbsp;Only the third, fifth, and
sixth paramters are really needed, the rest can always be the same.
&nbsp;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. &nbsp;Here are a few if statements
that can be used to handle the data from the field variable:</span><br>
<br>
if ((Field-&gt;Type == &nbsp;202)||(Field-&gt;Type == 203))<br>
{/*Handling a string database field type*/<br>
&nbsp;&nbsp;&nbsp; char String[100];<br>
&nbsp;&nbsp;&nbsp; WideCharToMultiByte(CP_ACP, 0, Field.bstrVal, -1,
String, 100, NULL, NULL);<br>
&nbsp;&nbsp;&nbsp; printf("Data: %s\n", String);<br>
}<br>
<br>
if ((Field-&gt;Type == 7)||(Field-&gt;Type == 11))<br>
{/*Handling a boolean database field type*/<br>
&nbsp;&nbsp;&nbsp; printf("Data: %d\n", Field-&gt;Value.boolVal);<br>
}<br>
<br>
if (Field-&gt;Type == 2)<br>
{/*Handling a integer database field type*/<br>
&nbsp;&nbsp;&nbsp; printf("Data: %d\n", Field-&gt;Value.intVal);<br>
}<br>
<br>
<span style="color: rgb(51, 51, 255);">Once you have done all the work
you needed with the recordset and the database you have to do a little
cleanup. &nbsp;The recordsets need to be closed as well as teh database
connection, both have convenient member functions called Close().
&nbsp;Here is how they look:</span><br>
<br>
RecSet-&gt;Close();<br>
Con-&gt;Close();<br>
<br style="color: rgb(51, 51, 255);">
<span style="color: rgb(51, 51, 255);">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. &nbsp;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. &nbsp;The idea of this article was only to present the background
and basics for working with ADO database connections. &nbsp;Hopefully
that much at least was covered.</span><br>