ADO is AOK – Part II

(or, How I spent my summer vacation…)


.

A little over a year ago I wrote about the Generic Recordset. This was a simple class that wrapped CDaoRecordset,
and made it a lot simpler to use dynamic binding. Then along came ADO, which used dynamic binding, and it was goodbye
Generic Recordset and DAO. To help other see how easy ADO really is, and because of the lack of documentation about
ADO and VC, I wrote ADO is AOK. Now I want to take that to the next level. This article will show you how to make
ADO even simpler for all your future application development. But most of all, it will also show you a little more
detail on how to use ADO (be it in your an ATL COM object or just in an application). The example I will be using
will show you how t use ADO in an ATL com object. We use this all the time at the office to build business layer
object that handle all of the dataIO and rules. This allows not only a specific application to use these rules
and the data, but also other "related" applications for doing the same without the need for them to have
an intimate knowledge of the application of the data. Anyway, here is what I will try to touch in this article:



1. Creating an ATL project.

2. Adding ADO to an ATL project (or any application)

3: Adding methods and properties to an interface.

4. BSTR(_bstr_t), VARIANT(_variant_t), and SAFEARRAY

4. Creating global instances of the Connection, Command, and the Recordset.

5. Use the Connection to open/close a connection, create a Recordset from SQL, and to be used as the active connection
with a Command Object.

6. Using the Command Object to run stored procedures with and without parameters.

7. Turn a recordset into a delimited string and passing it back to the client application.

8. Create wrappers for business logic that access ADO through the ADO methods.

9. And show both Visual C++ and Visual Basic code to access all of the methods and properties we add to the COM
object.



As you can tell, this is a very long article. I could not cover everything as well as I would have liked to, so
I will point you to other references if you want greater detail. I hope the time it took me to get a handle on
this will help you get a handle on it. Once you get the basics down neither ADO or ATL is that intimidating.








Creating the GenericAdo Com object using ATL:

The first thing we will need to do is create a new ATL (Active Template Library) COM object. We will let the
wizards do this for us. We made our GenericADO an EXE with the stub DLL, the choice is yours if you want it to
be compiled as an exe or a dll. Creating it as an exe will help stop it from crashing your application because
it is not running in the same process. If you are a programming GOD and your code never crashes, then by all means
make it a DLL, if you are a human like me, then I would suggest making it an EXE. But again the choice is yours.
If you have never created an ATL com object you will be pleasantly surprised how simple it is.



1. Open a new project ATL COM AppWizard, naming it MyADO (or what ever you want to name it). Press OK

2.Choose server type, either EXE or DLL. We are not going to use MFC or choose anything else on this page so just
click FINISH.



There you have created a com object (albeit totally useless). Now we have to add an interface to it.



1. Go to the Insert menu, select New ATL Object.

2. Double click on the Simple Object Icon. This will pop up a new dialog.

3: Enter the short name of ADO. This will populate all the other edit boxes.

4. Click on the Attributes Tab, look at it, nod your head knowingly, but don’t touch anything.

5. Click OK.

Is this not simple or what? At this point I could go into topics about customer interfaces, and cover what every
option on the attributes tab means, but that is way beyond the scope of this article. This article is meant to
get you up and running in the shortest amount of time.



Ok, you have now created the MyADO com object with the CADO class (also called co-classes), and the IADO interface.
Here is a simple explanation of what we have so far. The CADO class is the C++ class that you can use to control
what will happen. It is just like any other class that you have ever created. Nothing new there. The IADO interface
is nothing more than the things that your Com Client (the application that will use this com server) can see. When
you create a MyADO object, you will be able to use anything added to the interface. This will become a little clearer
as we continue. It is important only to remember that an interface has 2 important pieces. They are a PROPERTY,
which looks to the Com Client as a variable (though they really are not a variable) and a METHOD. This is like
a function. To use properties and methods from clients you can use the below sample code:



Property

(VB)

MyADOObject.SomeProperty = 10



(VC)

MyADOObject->put_SomeProperty(10);



Method

(VB)

Counter = MyADOObjcet.GetInt("SELECT COUNT(*) FROM some_table")



(VC)

MyADOObjcet->GetInt("SELECT COUNT(*) FROM some_table",&Counter)



One important difference in access both ADO and Com methods in VB and VC are that there are no defaults when accessing
with CV. that is, if the method takes 5 parameters, then you must supply 5 parameters. In VB you can skip the ones
you do not use. In my COM objects I always have a global variable declared like this:



VARIANT m_vNull;


Then initialized in the constructor like this:


m_vNull.vt=VT_NULL;
m_vNull.scode = DISP_E_PARAMNOTFOUND;

This variable can be used when ever you want to use the default value of a parameter. You can use this as a
parameter when ever you want to use a default parameter of a method.



So now we have a very simple COM Object with one interface. We now need to add the ability to use ADO. This is
done by editing the stdfx.h file for the application. You will need to add the same code to import ADO as you do
in any application. I have the location of the ADO files in the "include" files (Tools->Options->Directories
Tab) So all we need to do is add this line:


#import "msado15.dll" no_namespace rename("EOF","adoEOF")

If you do not have the directory listed, then you will have to add the entire path to the ADO files. If you
read ADO is AOK you will remember that we use no_namespace so we do not have to use name spaces, and we have to
rename EOF because of a nasty naming problem. Now we are ready to move ahead. The first thing we will be doing
is creating a global instance of _ConnectionPtr, _CommandPtr, and _RecordsetPtr. While we will be constantly creating
new instances of the _RecordsetPtr we will need at least one to do some fancy stuff later. There is a Now lets
add these now as follows:



1. Right Click on CGAdo.

2. Choose Add Member Variable;

3. Data Type is _ConnectionPtr, named m_Connection.

4. Do the same for data Type _CommandPtr, named m_Command and _RecordsetPtr named m_Recordset.



Because our sample will only connect to one database at a time, we can use a global Connection and a global Command.
Remember that the connection object is what is used to connect to the database, and the Command is used to access
the stored procedures. Usually one of these per COM session will be enough. There is nothing saying that you can
not add additional one if you like, but we have found that simpler is better and having one has never failed us
yet.



Now that we have our global connection and command we will need to initiate them in the constructor. Add the following
code to the CADO constructor


m_Connection.Createinstance(__uuidof(Connection));
m_Command.Createinstance(__uuidof(Command));
m_Recordset.CreateInstance(__uuidof(Recordset));

Now we must also add the code needed to eliminate these in the destructor So add the following code


try
{
if(m_Connection->State == adOpen)
{
// the connection is still open so we need to close it
m_Connection->Close();
}
if(m_Recordset->State == adOpen)
{
// the connection is still open so we need to close it
m_Recordset->Close();
}
m_Connection = NULL;
m_Command = NULL;
m_Recordset = NULL;
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here

Notice that we are checking to make sure that the connection and the recordset are not open before we set them
to NULL. That is all there is to adding ADO to the COM server, and adding a global connection, command, and recordset
variable. Now we have to decide what we want to have the ADO COm Object do. But first, lets take a look at SAFEARRAYS,
VARIANTS, and BSTRS. You will be using these over and over so we will have to have
an understanding of how they work. (You also might want to talk a look at the STD string and strstream templates
which are a great alternative to the CString class you can not use.) Because we like to keep things simple, and
our COM server may be used by any client regardless of the language it was written in, we like to keep the marshaling
simple, and we never (never say never) use anything but standard data types. So using these three makes our life
simple.



The BSTR ( and the wrapper _bstr_t) is a variable type that holds a string. In a nut shell, it is a string
that is proceeded by a number that tell you how long the string is. To use them is rather simple, but you have
to make sure that you allocate memory for them and de-allocate the memory for them. There are several ways to do
this, the most common is using::SysAllocString to allocate the memory and ::SysFreeString to free it. I on the
other hand like to use the _bstr_t wrapper. Using this allows you to completely ignore any of the allocating or
de-allocating because it is all done for you. Lets use _bstr_t.


#include <comdef.h> // this is where the _bstr_t is
_bstr_t BstrHolder("this is a BSTR");

To get the char* from a _bstr_t you simply cast it.


char *holder = (char*)BstrHolder;

We use this to pass the information to and from the com object. Once you have it in the CGAdo class, or in your
client you can convert it to what ever you like, char*, CString, whatever. We will only be using it to pass information
(string information) to and from the Com Server (and to and from ADO in our Com Server). If you search for _bstr_t
in VC help you will get a lowdown on what you can and can not do with this. This may be a good time to consider
memory allocation and the BSTR. The BSTR is created with the some variation of ::SysAllocString. But who should
create and who should delete the memory. Well the rules are actually rather simple.



1. If a parameter is [in], then the client must allocate and free the memory. The COM object can not change it.

2. If the parameter is [out] then the client must allocate it, the COM object can re-allocate it, but the client
must free it.

3. If a variable is created internal of the COM object, then the COM object must free it.



The VARIANT (and the wrapper _variant_t) is a huge union of different data types. You have several different
ints, bool, char char* IDispatch, date, and others. The_variant_t gives you a simple way of extracting the data
from the VARIANT. This simple way is to simply cast the_variant_t variable into the data type you want. VARIANTS
are used through out VB and scripting languages. Once you get use to them, they are also rather useful in C++.
We will use the VARIANT to pass information to and from the COM object when we are not using the simple data types,
or if we want a return that could be one of several different things. (for example it may be a string or an error
code number. The calling program can check the vt property of the VARIANT to see what data type it is and take
the appropriate action. We will use this ability in our recordset calls. But first lets take a look at creating
a VARIANT using the_variant_t wrapper.


#include <comdef.h> // this is where the _bstr_t is
_bstr_t BstrHolder("this is a Bstr");
_variant_t Holder(BStrHolder)

or


_variant_t Holder;
Holder = BstrHolder;
// then to extract the information
char * cHolder = (char*)_bstr_t(Holder);

Basically all we have done here is created a _bstr_t, and made it into a _variant_t, then converted it back
using casts. Not a really functional example, but it demonstrated the point that the VARIANT holds different types
of data and can easily (using_variant_t) extract that data too. As mentioned before this union ability is going
to com in handy when we start passing different types back and forth to the COM objects.



The SAFEARRAY (sorry no wrapper here…) is a way for us to easily pass arrays to and from a com object.
(Of course easy is a relative term….) I personally think they stink but it is what we are stuck with. This is
how you use them. There are several parts to what everyone calls a SAFEARRAY. In actuality we are making a VARIANT
of the type VT_ARRAY. Anyway, the three parts are:



The SAFEARRAYBOUND. This is how you set the bounds of your array. You have to set the upper and lower bounds. First
we create a SAFEARRAYBOUND showing how many dimensions it will have. You could look at each of the dimensions as
a field in a record. So lets create a 2 dimensional SAFEARRAY.


SAFEARRAYBOUND MyBound[2];

Now, for each of the dimensions you have to tell it how big it is and whether we are starting the count at 0
or 1.


// First how many elements do we have?
MyBound[0].cElements = 10);
MyBound[1].cElements = 10;
// Are we starting our counting at 1 or 0?
MyBound[0].lLbound = 0;
MyBound[1].lLBound = 0;

So now we have set up an array with 2 columns and 10 rows. You could in theory make a safe array a duplicate
of a recordset if you wanted. The next thing we have to do is initialize a VARIANT. This VARIANT can either
be passed to us from the client application or created right here. Remember we are only using the VARIANT as a
safe array, you can also use a SAFEARRAY variable, but since we are passing it back to our client, we will use
a VARIANT. We initialize it like this:


VariantInit(pVariant);

Now we have to tell the array what kind of data it will be holding. The SAFEARRAY can only hold one data type
at a time. We are going to use an array of variants because by using the variant we actually have an array where
each column can be a different data type. See the article "Using a Multidimensional SAFEARRAY to pass data
across from COM objects" written by Aravind Corera
to get a little more in depth with this idea.


pVariant->vt = VT_VARIANT | VT_ARRAY; 

The next thing we do is create the safe array in the VARIANT. The parameters to the SafeArrayCreate function
are, the type of data it will hold, the dimensions, and the name of the bound that you just created.


pVariant->parray = SafeArrayCreate(VT_VARIANT,2,MyBound);
or
SAFEARRAY pArray = SafeArrayCreate(VT_VARIANT,2,MyBound); // if you were using SAFEARRAY rather than a VARIANT

Ok, lets re-cap here, First we created a SAFEARRAYBOUND variable and set the bounds. Then a variable called
pVariant was sent to us as a parameter (thats how we got it), we use VariantInit() to initialize it, then we set
the vt member to VT_VARIANT | VT_ARRAY. Then lastly we created the SAFEARRAY as the variable in the VARIANT (pVariant)
This was done using the SafeArrayCreate() We basically told the function to create a SAFEARRAY for us, that will
hold VARIANTS (remember we are holding VARIANTS because we may have different data types and a VARIANT can hold
many different data types), the size of the Array is 2×10 that is 2 columns and 10 rows



Now we have to fill the array with something. You can do this any way you like, manually put it in, do it through
a loop of some sort, the choice is yours. I am just going to fill them manually for this sample, as I think it
demonstrates exactly what is happening better. I create a VARIANT to use, then fill it with some data. (In this
case a BSTR, which is created with the SysAllocateString() function, and an integer.)


VARIANT Column1(SysAllocString("COW");
VARIANT Column2(100);

Now we have to add this VARIANT to the array using the SafeArrayPutEliment() function. The First parameter is
a SAFEARRAY (now called pVariant->parray). The second parameter is an array of ints that matches the dimensions
of the array we just created. (int MyPosition[2]) The numbers in the 2 columns represents the position in your
array, [0,0] or [1,0] or what ever. The last parameter is the VARIANT you just made.


int MyPosition[2];
MyPosition[0] = 0;
MyPosition[1] = 0;
SafeArrayPutElement(pVariant->parray,MyPosition,&Column1);
MyPosition[0] = 1;
MyPosition[1] = 0;
Dimension[0] = 0;
SafeArrayPutElement(pVariant->parray,lDimension,&Column2);

Note the second element we put in was a number so both of our columns contain different data types. Well that
is about it for SAFEARRAYS. They are convenient for passing structured data back to the client application, especially
when sending information from COM to a VB application.. So let move on now. Remember, the most important things
to know about a SAFEARRAY are:



SAFEARRAYBOUND: Sets the bounds (upper and lower limit) or the SAFEARRAY.

SAFEARRAY and VT_ARRAY are basically the same thing.

You create a SAFEARRAY with the SafeArrayCreate Function. It takes 3 parameters, the Data type, the dimension,
and the SAFEARRAYBOUND.

You access items in the safe array with a dimension integer which is an array of ints with the same dimensions
as the SAFEARRAY.

You use SafeArrayGetElement and SafeArrayPutElement to get and put the information into a SAFEARRAY item.



Now lets move on to the component……..



What methods do we need?

Well the answer to this question is up to you. We have a ton of them covering just about everything you can
think of (or at least everything we could think of) In this article I will cover several different types so you
have an idea of what we will be doing. You can expand your personal version to include everything you think you
need. First lets take a look at the opening and closing of the connection We all have to do that.



Opening a connection:

In out ADO model, we have the rule that every ADO COM Object knows what database it is supposed to talk to. So
by default you need to send no parameters to the ADOOpenConnection method. We create the method by right clicking
on IADO then choosing New Method. Out parameters are entered as:



[in] BSTR ConnectionString, [in] BSTR UserId, [in] BSTR Password, [out,retval] int *Result



The [in] means that this parameter is being sent from the client application, the out means that we are sending
it back to the clients and retval means that to clients written in VB (and similar languages) this will appear
as the returned value of the method. The client application sends us the connection string, the user id and the
password, and the COM object will send back the result. Now lets look at the method and what we need to do to make
it work:


STDMETHODIMP CADO:ADOOpenConnection(BSTR ConnectionString, BSTR UserId, BSTR Password, int *Result)
{
_bstr_t TempConnectionString(ConnectionString);
if(TempConnectionString.length() <1)
{
// There is no connection string This would normally be set up to connect to the ADO main database
// It will change with each ADO COM Object
std::string Holder = "MY_DSN";
// Now open the connection only if it is not already open
try
{
if(m_Connection->State != adStateOpen)
{
HRESULT hr = m_Connection->Open(_bstr_t(Holder.c_str()),_bstr_t("Admin"),_bstr_t("ORAIDERS"),-1);
if(hr != S_OK)
{
Result = 0;
return S_FALSE;
}
}
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
return S_OK;
}
else
{
try
{
m_Connection->Open(ConnectionString,UserId,Password,adAsyncConnect);
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
return S_OK;
}
}

Lets look at what is happening here. First we put that nasty BSTR into a _bstr_t so we can work with it a little
easier. Then we check to see if there is anything in it. If not, then we are using the default, if there is something
in it then we will try to use what the user sent us. The next thing we do is simply use the ADO Open method, passing
in with the default or what the user sent. Notice we are using the STD string template here for our strings. It
should also be noted the error checking we are doing. BASICLY NONE, you should send appropriate messages
back to the client when there is an error. For no other reason than my laziness I have only included the simplest
catching of errors in this article. To call this method you do this:


(VB)

Result = MyADOObject.ADOOpenConnection "","",""

(VC)

MyADOObject->ADOOpenConnection("","","",&Result);



After this Method is called your COM object now has a connection to the database. So lets close it . Again we will
add a method to the IADO interface by right clicking on it then choosing New Method. This time there are no parameters
so we can just press OK. Lets look at the code:


STDMETHODIMP CADO::ADOCloseConnection()
{
try
{
if(m_Connection->State == adStateOpen)
m_Connection->Close();
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
return S_OK;
}

Again we simply wrapped the ADO.Close() method with our wrapper. So now we can open and close a database connection.
Lets get it to do something. In our ADO COM Object we decided we would return information 3 different ways. The
first would be to return the Recordset, the second would be to return a delimited string, and the third would be
to return a specific piece of information (one value). We also decided that we needed to be able to run both a
SQL query and stored procedure. So with this in mind we came up with these "generic" methods:



ADOExecuteReturnDelimited)(/*[in]*/ BSTR SQL, /*[in]*/ BSTR Delimiter, /*[out,retval]*/VARIANT *Result);

This function will run the Connection.Execute method and return a string delimited by what ever the user wants
it delimited by (to include nothing).



ADOExecute)(/*[in]*/ BSTR SQL, /*[out,retval]*/ _Recordset **Result);

This function will run the Connection.Execute method and return a _RecordsetPtr.



ADOExecuteSPReturnDelimited)(/*[in]*/ BSTR SPName, /*[in]*/ VARIANT ParameterArray, /*[in]*/BSTR Delimiter,
/*[out,retval]*/ VARIANT *Result);


This will run a stored procedure, passing it an array of parameters and return a delimited string containing the
result.



ADOExecuteSP)(/*[in]*/ BSTR SPName, /*[in]*/ VARIANT ParameterArray, /*[out,retval]*/ _Recordset **Result);

This will run any stored procedure and return a _RecordsetPtr.



ADOGetVariant)(/*[in]*/ BSTR SQL, /*[out,retval]*/ VARIANT *Result);

And this will return 1 value of any type. By default this will be the first column in the first recordset of
what ever SQL statement you send.



To create any of these you do that right click thing on the interface again and choose New Method. Then enter the
parameters as listed above. But wait a second….. When you try to compile this you will get an error stating something
like there is no such thing as a _Recordset. to fix this you will need to add several things to your application.
First is a file called Helper.h This contains the following (exactly like this).


struct _Recordset;
#if !defined(__cplusplus) || defined(CINTERFACE)
typedef struct _Recordset _Recordset;
#endif

You will also need to create a helper.idl file that contains the following (exactly like this).


import "msado15.idl";

And…. in your ADO.h file you need to add:


#include "helper.h"

And lastly in your application idl file you need to add:


import "helper.idl";

What the above does, is it allows you to pass a recordset back to the client application. There is a KB article
on this. Also take a look at the article Creating a Disconnected Recordset in C++ For VB – Jeff Lundgren (1999/05/21)
here at code guru. It has the KB references too. Once you have added this to your application you are ready
to compile and ready to pass recordsets back to the client application. Lets look at these methods.


STDMETHODIMP CADO::ADOExecute(BSTR SQL, _Recordset **Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
try
{
if(m_Recordset == NULL)
m_Recordset.CreateInstance(__uuidof(Recordset));
if(m_Recordset->State == adStateOpen)
m_Recordset->Close();
m_Recordset = m_Connection->Execute(_bstr_t(SQL),&RecordsEffected,adCmdText);
*Result = m_Recordset.Detach();
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
}

Ok, the first thing we do is make sure that our m_Recordset is valid and CLOSED.


if(m_Recordset == NULL)
m_Recordset.CreateInstance(__uuidof(Recordset));
if(m_Recordset->State == adStateOpen)
m_Recordset->Close();

After that we use the m_Connection Execute method sending it the SQL string and the other parameters needed.


m_Recordset = m_Connection->Execute(_bstr_t(SQL),&RecordsEffected,adCmdText);

We then detach the global recordset to the Result (which is also a recordset).


*Result = m_Recordset.Detach();

You would use this method as follows:



(VB)

Dim MySet as Object

Object = MyADOObject.ADOExecute("SELECT * FROM some_table")




(VC)

_RecordsetPtr MySet;

MyADOObject->ADOExecute(_bstr_t("SELECT * FROM some_table),&MySet);




The method is pretty straight forward. All we are doing here is wrapping the Connection. Execute method
and returning the result. This may be a good time to ask: Since this is actually so much more work then simply
creating a connection and a recordset in the client, why go through all this trouble? To answer that, lets remember
what we are actually building here. We are building a way that an application that has no knowledge of how our
data is set up, to be able to access that date via different methods in your COM object. The methods we are currently
building (The ADO Wrappers) will be used internal of the COM object to simplify writing these application specific
methods. I have included the ADO wrapper methods so the client developer can access them, you may want to hide
them from the client developer. The choice is yours. Bottom line is when we want to use the Execute function on
our connection. From this point on we do not have to worry about anything but the SQL statement. A little less
typing, and when you see some of the application specific methods we will be using the need for the ADO wrappers
will be even more obvious..



Now lets look at the return delimited method:


STDMETHODIMP CADO::ADOExecuteReturnDelimited(BSTR SQL, BSTR Delimiter, VARIANT *Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
*Result = _variant_t("");
try
{
_RecordsetPtr PopSet;
PopSet.CreateInstance(__uuidof(Recordset));
PopSet->CursorLocation = adUseClient;
if(m_Connection->State != adStateOpen)
{
*Result = _variant_t("VISP_COM_ERROR_CONNECTION_NOT_OPEN");
return S_FALSE;
}
ADOExecute((char*)_bstr_t(SQL),&PopSet);
if(!PopSet->adoEOF)
{
BSTR bstrResult = NULL;
_bstr_t btColDelim(L",");
_bstr_t btRowDelim(L"rn");
_bstr_t btNullExp(L"");
PopSet->raw_GetString(adClipString,-1,btColDelim,btRowDelim,btNullExp,&bstrResult);
*Result = _variant_t(bstrResult);
}
else
*Result = _variant_t("");
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
return S_OK;
}

Ok, you can see here that we are using a local instance of a _RecordsetPtr.



_RecordsetPtr PopSet;

PopSet.CreateInstance(__uuidof(Recordset));

PopSet->CursorLocation = adUseClient;



We can do this because when we leave this method we do not care about the recordset anymore, we already have the
information we want in the BSTR we receive from the raw_GetString method. It is IMPORTANT to note
here that there is a known bug in the GetString Method that will sometimes throw an unhandled exception. You should
not use the GetString method, but you can use the raw_GetString method. In this method we use OUR ADOExecute
rather than the Connection. Execute method. There is no reason for this other than it takes less typing.


ADOExecute((char*)_bstr_t(SQL),&PopSet);

When we get our recordset back we check to make sure there are records there, then we use the raw_GetString
method to convert the recordset into a delimited string.


if(!PopSet->adoEOF)
{
BSTR bstrResult = NULL;
_bstr_t btColDelim(L",");
_bstr_t btRowDelim(L"rn");
_bstr_t btNullExp(L"");
PopSet->raw_GetString(adClipString,-1,btColDelim,btRowDelim,btNullExp,&bstrResult);
*Result = _variant_t(bstrResult);
}
else
*Result = _variant_t("");

Now you can get some rather large recordsets into these strings. I have not played with it enough to see if
there is a max size recordset you can return. If someone wants to do that I would be interested in hearing what
they find out. After we get the string back we send it back to the client application. Typical call to this method
would be:



(VB)

ResultString = MyADOObject.ADOExecuteReturnDelimited("SELECT * FROM some_table",",")


(VC)

BSTR ResultString;

MyADOObject->ADOExecuteReturnDelimited(_bstr_t("SELECT * FROM some_table"),_bstr_t(","),&Result);



Ok, at this point we have created a ATL com object, added ADO support to it, and added 4 methods. All of
which simply wrap existing ADO methods. Not lets take a look at what we can do to make this more application specific.
Lets say in your company several applications need a customer name, and the current balance. In your application
database they are kept in 2 tables cust_info and cust_credit. The fields of importance are cust_info.cust_id, cust_info.cust_name,
and lastly cust_credit.cust_balance. For another application to access this data they need to know the names of
the tables , the names of the fields, and the fact that they are linked with cust_id.



Now, if we take this "Intimate Knowledge" about the database and application and put it into a method
we can give the client application the information and still hide them from the database. Lets create this new
Method.


STDMETHODIMP CADO::sGetCustomerNameAndBalance(BSTR CustKey, VARIANT *Result)
{
// std stream used to create the SQL statement
std::strstream SQL;
// Put the BSTR into something we can deal with
_bstr_t Holder(CustKey);
SQL.str("");
SQL<<"SELECT a.cust_name, b.cust_balance FROM cust_info as a, cust_credit as b WHERE a.cust_id = ‘<<(char*)Holder<<" AND a.cust_key = b.cust_key";
ADOExecuteReturnDelimited(_bstr_t(SQL.str().c_str()),_bstr_t(","),Result);
return S_OK;
}

Whoa, was that easy or what? Let look at what we did here. First we created an strstream. This is a STD template
that allows us to have a formatted string.


std::strstream SQL;

The next thing we did was convert the BSTR the client sent to us into a _bstr_t. I do this all the time because
I think it is much easier to use _batr_t than BSTR. There are other methods you can use too, (CComBstr I think
is one of them) the choice is yours. I like _bstr_t. Anyway… we know what the SQL statement needs to be to extract
this infomration, the client should not need to know this. So we create our SQL statement using the strstream variable.


SQL.str("");// this resets it to nothing
SQL<<"SELECT a.cust_name, b.cust_balance FROM cust_info as a, cust_credit as b WHERE a.cust_id = ‘<<(char*)_bstr_t(CustKey)<<" AND a.cust_key = b.cust_key";

Then the last thing we do is call the ADOExecuteReturnDelimited() method we created a few minutes ago to run
the Query and return a delimited string.


ADOExecuteReturnDelimited(_bstr_t(SQL.str().c_str()),_bstr_t(","),Result);

So now we have a method in our COM Object that will return important information to the client application but
it hid the client from the need to know anything about our database. Of course the client application had to know
that we had the COM Object, and that there was a method that got the information they needed, and that this method
required them to send the customer id. But that information should be given out with your ADO COM Object to the
client developers. So lets see what this take from VB and from VC.



(VB)

MyResult = MyADOObject.sGetCustomerNameAndBalance(SomeCustID)



(VC)

VARIANT MyResult;

MyADOObject.->sGetCustomerNameAndBalance(SomeCustID,MyResult);




Now this example was rather simple, but I think you can see the power that an ADO Com Object could have. The client
application did not (necessarily) need to know the connection information, the table names, how to use a recordset,
how to extract the information from the recordset, or anything about SQL to get the information. They needed one
call to a method in our COM Object, knowing that they needed to send a customer ID and that they would get back
a comma delimited string containing the customer name and the balance.



What we have done with most of our application specific functions (the ones dealing with our application and our
database) is to create 2 methods that are identical. One will return a delimited string, (the method name is prefixed
with a "s") the other will return a recordset. Lets look at the sister to this function that will return
a recordset rather than a delimited string.


STDMETHODIMP CADO::sGetCustomerNameAndBalance(BSTR CustKey, _Recordset **Result)
{
// std stream used to create the SQL statement
std::strstream SQL;
// Put the BSTR into something we can deal with
_bstr_t Holder(CustKey);
SQL.str("");
SQL<<"SELECT a.cust_name, b.cust_balance FROM cust_info as a, cust_credit as b WHERE a.cust_id = ‘<<(char*)Holder<<" AND a.cust_key = b.cust_key";
ADOExecute(_bstr_t(SQL.str().c_str()),_bstr_t(","),Result);
return S_OK;
}

The only difference is that we use our ADOExecute method rather than the delimiter method, and our retval parameter
is a _recordset rather than a VARIANT. To call this from our clients would look something like this:



(VB)dim MySet as object

MySet = MyADOObject.GetCustomerNameAndBalance(CustID)



(VC)

_RecorsetPtr MySet;

MyADOObject->GetCustomerNameAndBalance(CustID,&MySet);




Ok, now we have a way to run SQL statements from the client application, but what about running a stored procedure?
To do this we have create 2 methods. One will run a stored procedure and return a delimited string, the other will
return a recordset. Lets look at the recordset method first.


STDMETHODIMP CADO::ADOExecuteSP(BSTR SPName, VARIANT ParameterString, _Recordset **Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
if(m_Recordset == NULL)
m_Recordset.CreateInstance(__uuidof(Recordset));
if(m_Recordset->State == adStateOpen)
m_Recordset->Close();
try
{
// Now set up the command object
m_Command->ActiveConnection = m_Connection;
// enter the SP name
m_Command->CommandText = _bstr_t(SPName);
// Now execute it
m_Recordset = m_Command->Execute(&RecordsEffected,&ParameterString,adCmdStoredProc);
*Result = m_Recordset.Detach();
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here
return S_OK;
}

Ok, lets see what we did here. The beginning of this method looks like our SQL method. We make sure our global
recordset is valid, but not open.


if(m_Recordset == NULL)
m_Recordset.CreateInstance(__uuidof(Recordset));
if(m_Recordset->State == adStateOpen)
m_Recordset->Close();

After this we set up our command variable, by setting the ActiveConnection and entering the name of the stored
procedure we want to use.


// Now set up the command object
m_Command->ActiveConnection = m_Connection;
// enter the SP name
m_Command->CommandText = _bstr_t(SPName);

Lastly we have to run the Command.Execute method sending the parameters, then populate the Result we are sending
back.


m_Recordset = m_Command->Execute(&RecordsEffected,&ParameterString,adCmdStoredProc);
*Result = m_Recordset.Detach();

Lets take a look at this parameter list. The parameter we need to send is an array of VARIANTS (hmmm SAFEARRAY).
In VB it is really easy to do this.



(VB)

dim MySet as Object

MySet = MyADOObject.ADOExecuteSP("some_stored_procedure",Array("param_1",2,"param_3))




(VC)

SAFEARRAYBOUND MyBound[1];

MyBound[0].cElements = 2);

MyBound[0].lLbound = 0;



VARIANT ParamHolder;
VariantInit(ParamHolder);
ParamHolder->vt = VT_VARIANT | VT_ARRAY;
pVariant->parray = SafeArrayCreate(VT_VARIANT,2,MyBound);

VARIANT Param1(SysAllocString("123ABC");
VARIANT Param2(100);

int MyPosition;
MyPosition = 0;
SafeArrayPutElement(pVariant->parray,MyPosition,&Param1);
SafeArrayPutElement(pVariant->parray,lDimension,&Param2);

_RecordsetPtr MySet;
MyADOObject->ADOExecuteSP(_bstr_t("some_stored_procedure"),ParamHolder,&MySet);


Quite a bit more work from VC, but not too bad. The majority of what you have to do here is setting up the SAFEARRAY.
This is what I do not like about the SAFEARRAY, it is just way too much work to do something relatively simple.
Once you have the SAFEARRAY thought the call to run the Stored procedure is just as simple in VC as it is in VB.
Of course we have to have a sister method to this that will return a delimited string. It looks like this:


STDMETHODIMP CADO::ADOExecuteSPReturnDelimited(BSTR SPName, VARIANT ParameterArray, BSTR Delimiter, VARIANT *Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
*Result = _variant_t("");
try
{
_RecordsetPtr PopSet;
PopSet.CreateInstance(__uuidof(Recordset));
PopSet->CursorLocation = adUseClient;
if(m_Connection->State != adStateOpen)
{
*Result = _variant_t("VISP_COM_ERROR_CONNECTION_NOT_OPEN");
return S_FALSE;
}
// Now set up the command object
m_Command->ActiveConnection = m_Connection;
// enter the SP name
m_Command->CommandText = _bstr_t(SPName);
// Now execute it

PopSet = m_Command->Execute(&RecordsEffected,&ParameterArray,adCmdStoredProc);
if(!PopSet->adoEOF)
{
BSTR bstrResult = NULL;
_bstr_t btColDelim(Delimiter);
_bstr_t btRowDelim(L"rn");
_bstr_t btNullExp(L"");
PopSet->raw_GetString(adClipString,-1,btColDelim,btRowDelim,btNullExp,&bstrResult);
*Result = _variant_t(bstrResult);
}
else
*Result = _variant_t("");
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here

return S_OK;
}


Again you can see the similarity between the SQL execute methods and the Stored Procedure methods. To save a
ton of typing I am not going to walk through the code on this method. After looking at the code I am sure that
you can see what we are doing based on what I have already explained. Here is how you wold call this from your
clients.



(VB)

MyResult = MyADOObject.ADOExecuteSPReturnDelimited("some_stored_procedure",Array("param_1",2,"param_3),
",")




(VC)

SAFEARRAYBOUND MyBound[1];

MyBound[0].cElements = 2);

MyBound[0].lLbound = 0;


VARIANT ParamHolder;
VariantInit(ParamHolder);
ParamHolder->vt = VT_VARIANT | VT_ARRAY;
pVariant->parray = SafeArrayCreate(VT_VARIANT,2,MyBound);

VARIANT Param1(SysAllocString("123ABC");
VARIANT Param2(100);

int MyPosition;
MyPosition = 0;
SafeArrayPutElement(pVariant->parray,MyPosition,&Param1);
SafeArrayPutElement(pVariant->parray,lDimension,&Param2);
VARIANT MyResult;;
MyADOObject->ADOExecuteSPReturnDelimited(_bstr_t("some_stored_procedure"),ParamHolder,_bstr_t(","),&MyResult);


Ok, now lets take the last step. Lets make an ADO specific method that will return both a recordset and a delimited
string based on a stored procedure. You will see since we have created out ADO stored procedure wrappers, creating
a ADO specific function will be easy. We are going to create a ADO specific method that will run a stored procedure
that takes one parameter. the client application only sees a method that asks for a Rep key. It expects to get
a recordset containing all information about that employee. in our database we have created a stored procedure
called GetEmployeeInfo. It takes one parameter, that is the employee ID.


STDMETHODIMP CADO::GetEmployeeName(BSTR RepKey, _Recordset **Result)
{
SAFEARRAYBOUND MyBound[0];
MyBound[0].cElements = 1);
VARIANT ParamHolder;
VariantInit(ParamHolder);
ParamHolder->vt = VT_VARIANT | VT_ARRAY;
pVariant->parray = SafeArrayCreate(VT_VARIANT,1,MyBound);
VARIANT Param1(RepKey);
int MyPosition;
MyPosition = 0;
SafeArrayPutElement(pVariant->parray,MyPosition,&Param1);
ADOExecuteSP(_bstr_t("GetEmployeeInfo"),ParamHolder,Result);
}

Ok, what we have done here is created a SAFEARRAY of VARIANTS, added one item, (the BSTR sent to us from the
client) and used out ADOExecuteSP method we just created. Lets see the client code needed to make this work:



(VB)

dim MySet as Object

MySet = MyADOObject.GetEmployeeInfo("123ABC")



(VC)

_RecordsetPtr MySet;

MyADOObject->GetEmployeeInfo(_bstr_t("123ABC"),&MySet);




The client application has it really easy. It does not need to know the name of the stored procedure, The parameters
it takes, how to use the Command object, and in the case of the VC client, it does not need to know how to use
SAFE arrays. Quite a bit of time saved. You can wrap all of the stored procedures you want with methods like these.
The client application needs to know nothing. Of course we can also return a delimited string if we want to with
a similar method.


STDMETHODIMP CADO::sGetEmployeeName(BSTR RepKey, VARIANT *Result)
{
SAFEARRAYBOUND MyBound[0];
MyBound[0].cElements = 1);
VARIANT ParamHolder;
VariantInit(ParamHolder);
ParamHolder->vt = VT_VARIANT | VT_ARRAY;
pVariant->parray = SafeArrayCreate(VT_VARIANT,1,MyBound);
VARIANT Param1(RepKey);
int MyPosition;
MyPosition = 0;
SafeArrayPutElement(pVariant->parray,MyPosition,&Param1);
ADOExecuteSPReturnDelimited(_bstr_t("GetEmployeeInfo"),ParamHolder,_bstr_t(","),Result);
}

And the call from the clients:



(VB)

MyResult = MyADOObject.GetEmployeeInfo("123ABC")



(VC)

VARIANT MyResult;

MyADOObject->GetEmployeeInfo(_bstr_t("123ABC"),&MyResult);



Ok, lets look at what we now know.



1. Making an ATL COM Object is really simple. Basically follow the wizards and you are done.

2. We can add an interface through the wizard and add both Properties and Methods to the interface by following
some simple steps.

3. We can add ADO to our ATL project just as simply as we can add it to any application.

4. We know how to pass VARIANTS, BSTRs, and SAFEARRAYs to methods we create.

5. We know the difference in calling our methods from VB and VC.

6. We know how to use the SAFEARRAY, _bstr_t and _variant_t.

7. We know how to wrap the ADO execute method to return either a recordset or a delimited string based on a QUERY.

8. We know how to access stored procedures through the Command interface, and return either a recordset or a delimited
string.

9. We know how to use methods to hide the database from the client, eliminating the need for them to understand
anything about the database.



Now for some cool tricks and ideas!

Now I will show you one more really cool trick. We use our ADO COM Object all the time when developing ASP Web
pages. Also on our Web pages we use tables all the time. Now I will show you how to make a table based on any query
or any stored procedure! Check this out.


STDMETHODIMP CADO::ADOMakeWebTable(BSTR SQL, VARIANT *Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
BSTR bstrResult = NULL;
std::strstream Holder;
try
{
_RecordsetPtr PopSet;
PopSet.CreateInstance(__uuidof(Recordset));
PopSet->CursorLocation = adUseClient;
if(m_Connection->State != adStateOpen)
{
*Result = _variant_t("VISP_COM_ERROR_CONNECTION_NOT_OPEN");
return S_FALSE;
}
PopSet = m_Connection->Execute((char*)_bstr_t(SQL),&RecordsEffected,adCmdText);
if(!PopSet->adoEOF)
{
_bstr_t btColDelim(L"</TD><TD>");
_bstr_t btRowDelim(L"</TD</TR><TR><TD>");
_bstr_t btNullExp(L"");
PopSet->raw_GetString(adClipString,-1,btColDelim,btRowDelim,btNullExp,&bstrResult);
Holder.str("");
Holder<<"<TABLE BORDER="<<m_Border<<" BGCOLOR="<<(char*)m_BGColor<<" STYLE=’color="<<(char*)m_FGColor<<"’><TR><TD>"<<(char*)_bstr_t(bstrResult)<<"</TD></TR></TABLE>";
*Result = _variant_t(_bstr_t(Holder.str().c_str()));
}
else
*Result = _variant_t("");
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here

return S_OK;
}


Ok, what this method does is it allows the asp page to create a table based on any query. The call from ASP
is like this:



(ASP)

Response.write MyADOObject.ADOMakeWebTable("SELECT * FROM some_table")


By adding that one line of code you have made a table on your web page based on the SQL. There is nothing else
for you to do. We use this all the time! As a matter of fact we have a method that will allow us to change for
of the parameter of the WEB table.


STDMETHODIMP CADO::ADOSetWebTableInfo(int Border, BSTR FGColor, BSTR BGColor)
{
// This is where we set up the variables for the WEB tables
if(Border <0 || Border > 5)
m_Border = 0;
else
m_Border = Border;
m_FGColor = FGColor;
m_BGColor = BGColor;
return S_OK;
}

This method allows me to change the border size, foreground and background colors of the WEB table. you can
easily add more options to this if you like. Because you are using the Response.write method, any styles that you
are using will be carried over to the WEB table. I can not tell you how much time this little method can (and will)
save us! Here is the version for creating a WEB table from a stored procedure:


STDMETHODIMP CADO::ADOMakeSPWebTable(BSTR SPName, VARIANT ParamArray, VARIANT *Result)
{
VARIANT RecordsEffected;
RecordsEffected.vt = VT_INT;
BSTR bstrResult = NULL;
std::strstream Holder;
*Result = _variant_t("");
try
{
_RecordsetPtr PopSet;
PopSet.CreateInstance(__uuidof(Recordset));
PopSet->CursorLocation = adUseClient;
if(m_Connection->State != adStateOpen)
{
*Result = _variant_t("VISP_COM_ERROR_CONNECTION_NOT_OPEN");
return S_FALSE;
}
// Now set up the command object
m_Command->ActiveConnection = m_Connection;
// enter the SP name
m_Command->CommandText = _bstr_t(SPName);
// Now execute it

PopSet = m_Command->Execute(&RecordsEffected,&ParamArray,adCmdStoredProc);
if(!PopSet->adoEOF)
{
_bstr_t btColDelim(L"</TD><TD>");
_bstr_t btRowDelim(L"</TD</TR><TR><TD>");
_bstr_t btNullExp(L"");
PopSet->raw_GetString(adClipString,-1,btColDelim,btRowDelim,btNullExp,&bstrResult);
Holder.str("");
Holder<<"<TABLE BORDER="<<m_Border<<" BGCOLOR="<<(char*)m_BGColor<<" STYLE=’color="<<(char*)m_FGColor<<"’><TR><TD>"<<(char*)_bstr_t(bstrResult)<<"</TD></TR></TABLE>";
*Result = _variant_t(_bstr_t(Holder.str().c_str()));
}
else
*Result = _variant_t("");
}
catch(_com_error &e){} // error handling here
catch(…){}// all other exceptions here

return S_OK;
}


And of course, you can wrap these with application specific methods like this:


STDMETHODIMP CADO::GetEmployeeTable(VARIANT *Result)
{
ADOMakeSPWebTable(_bstr_t("EmployeeList"),m_vNull,Result);
return S_OK;
}

Now you have given the client application the ability to make a table on a WEB page with one call like this:



(ASP)

Response.Write MyADOObject.GetEmployeeTable()


Conclusion:

If you have made it this far I hope something in this article has helped you. I have tried to cover the basics
for you and hope that you will find ADO a little easier after reading this article. I wish there were some more
books on ADO, heck who knows maybe I will get off my butt and write one. But until that time have fun with ADO
and with ATL. Once you start to get an understanding of how they work you will find that they really are not that
bad. Good luck with your programming!

Downloads

Download source code – 87 KB

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read