Introduction
In the previous article on using OLE DB Consumers in C++ I went through how to
set up a database and then read data from it. This article expands on the
previous article by allowing movement through the database table, both
backwards and forwards and making changes to the entries in the database by
using the update, insert and delete functions. This will be done using both
tables and commands with a final section showing how to run a query on a
database table that. It Should be noted that although the previous article used
the nwind demonstration database provided by Microsoft as a sample this article
will use a small Books database built in access and as such is not using a
specifically set up ODBC Driver but is using the Jet 4.0 driver and is
accessing the database file directly. If you do not have this driver and need
to use another see the earlier C++ article in this series about setting up the
driver.
The Code for this project was developed under Windows 2000 using Developer
Studio 6 and 7.
Using a Table Accessor
In the previous article I mentioned the differences between the table accessor
to a database and the command accessor to a database. This article will make
the different capabilities of the two types Alot clearer. First off the main
functionality of the table is provided in the CRowset class that the table
inherits
class CBooks : public CTable< CAccessor< CBooksAccessor > >
The missing bit in the line above is that the definition of CTable is
CTable< TAccessor, TRowset >
So if you don't specify a rowset type you get the standard CRowset thrown in
for free. The standard CRowset class provides all of the moving
functionality
HRESULT hResult = m_Books.MoveNext();
HRESULT hResult = m_Books.MovePrev();
And all of the functionality to change the database
HRESULT hResult = m_Books.SetData();
HRESULT hResult = m_Books.Delete();
HRESULT hResult = m_Books.Insert();
It should be noted that there are no restrictions on the insert function so you
could just sit there pressing the insert button for a while and it will insert
an entry with the same entry data apart from the auto-numbered id value for as
long as you want.
The main focus of attention here is on the accessors and the way in which they
can be used to customize the information returned from the database. In the
previous article we used the standard table binding methods in the accessor.
The standard accessor for this article is
BEGIN_COLUMN_MAP( CBooksAccessor )
COLUMN_ENTRY(1, m_ID )
COLUMN_ENTRY(2, m_Title )
COLUMN_ENTRY(3, m_AuthorsFirstName )
COLUMN_ENTRY(4, m_AuthorsLastName )
COLUMN_ENTRY(5, m_Category )
COLUMN_ENTRY(6, m_price )
END_COLUMN_MAP()
This is the standard definition for binding the columns to the data members in
the class, for each row in the table. But it turns out that the definition of
the macro BEGIN_COLUMN_MAP is
BEGIN_ACCESSOR_MAP(x, 1 )
BEGIN_ACCESSOR( 0, true )
Where the x stands for the class name and the 1 stands for the number of
accessors that are being defined in the current list. The BEGIN_ACCESSOR macro
takes two variables as well the first being the current accessor definitions
number and the second being the flag for if it is an autoaccessor. Which in
English means which accessor is used if there is no accessor specifed. So for
the table accessor we are going to want three different accessors 1, for the
standard access to the table, 2, So that we can view the authors and 3 so that
we can view the categories. These can be specified using BEGIN_ACCESSOR,
END_ACCESSOR pairs.
BEGIN_ACCESSOR_MAP( CBooksAccessor, 3 )
BEGIN_ACCESSOR( 0, true )
COLUMN_ENTRY( 1, m_ID )
COLUMN_ENTRY( 2, m_Title )
COLUMN_ENTRY( 3, m_AuthorFirstName )
COLUMN_ENTRY( 4, m_AuthorLastName )
COLUMN_ENTRY( 5, m_Category )
COLUMN_ENTRY( 6, m_price )
END_ACCESSOR()
BEGIN_ACCESSOR( 1, false )
COLUMN_ENTRY( 1, m_AuthorsFirstName )
COLUMN_ENTRY( 2, m_AuthorsLastName )
END_ACCESSOR()
BEGIN_ACCESSOR( 2, false )
COLUMN_ENTRY( 1, m_Category )
END_ACCESSOR()
END_ACCESSOR_MAP()
The above is what the CTable Accessor looks like. The accessor map has three
entries designed to be used as described above. Although in this example we are
getting the same data in the additional accessors we could set up the accessor
so that we only had one bind to each member of the class although this would
make more work in using the class as we'd have to keep switching accessors to
get the full rowset data.
To switch between the accessors within the code you call CAccessor::GetData( x
) where x equals the number of the accessor that you wish to used but you
should note that the numbering of the accessors in the example above is zero
based and so is the number passed to get data. In the current example if you
changed any value passed to GetData as 3 or higher then you will run into an
ATLASSERT that checks that the number you are passing in the call to
GetData is
not higher than the number passed to BEGIN_ACCESSOR_MAP.
Once you have used the GetData function you must be careful about how you
continue. For a start if you have used the MoveNext function until it returned
a value that was not equal to S_OK. You are technically past the end of the
rowset which means that any calls to the accessor will fail because at this
moment in time the accessor is not pointing to a valid record so you need to do
m_Books.MoveFirst();
m_Books.GetData( 0 );
m_Books.MoveFirst();
Using a Command Accessor
The CCommand Accessor is almost identical to the table accessor and in this
case the bindings in the COLUMN_MAP are left entirely as standard. The command
accessor has access to exactly the same CRowset functions as the Table. This
means that the CCommand Accessor will behave in exactly the same way in the
demo app as the table with the notable exception of the way I get the author
data and the category data. This is done by adding some thinge to the
CCommandBooks class itself.
CCommand< CDynamicAccessor > m_QueryCommand;
HRESULT Query( BSTR bstrSQL )
{
CString strString( bstrSQL );
HRESULT hResult = m_QueryCommand.Open( m_session, strString );
if( FAILED( hResult ) )
return hResult;
hResult = m_QueryCommand.MoveFirst();
if( FAILED( hResult ) )
return hResult;
return S_OK;
}
The separate CCommand variable declared inside the class takes a
CDynamicAccessor which means that there will be no bindings to the column in
the database already mapped, instead these will have to be retrieved using the
CDynamicAccessor functions.
The Query Function takes an SQL string and uses the string to open a new
command on the database session. Notice that it uses the class session object
to access the database and does not try to set up its own session. The function
then moves to the first rowset in that was returned from the query.
The two functions that use the Query function are
COLEDBConsumer2Dlg::CommandViewCategories and COLEDBConsumer2Dlg::CommandViewAuthors. All the functions that view the authors
and the categories are written the same way, first they get the data, then the
put the data in a object list which is a member of the CAccessorDlg class. The
CAccessorDlg class will then use the object list to fill its list box when it
initialises in the call to DoModal().
CComBSTR bstrQuery( _T( "SELECT distinct Books.[Author Last Name], Books.[Author First Name] FROM Books" );
HRESULT hResult = m_CommandBooks.Query( bstrQuery.m_str );
The call to the query function is very simple the one thing that makes using
the Command Query method over the table method is that by using the distinct
keyword before the authors last name the SQL statement will only select a
single version of an author even if they have three or four entries in the
database.
There is a slight advatage at the moment because we know that the when querying
the database for Authors first and last names we are only ever going to get
strings returned in the columns.
bstrTemp = static_cast< BSTR >( m_CommandBooks.m_QueryCommand.GetValue( 1 ) );
The CDynamicAccessor::GetValue function can take as its parameter either the
column number which is used here or the name of the column. This will then
return the data as a void pointer which is then cast to a BSTR which remember
is an OLECHAR pointer anyway so there is no problem with this cast it just
looks like its a pointer to type being cast to a type. Once the value in the
column has been cast to a string we can use it any way we want.
Finally we close the m_CommandBooks.m_QueryCommand object and return to the
main dialog.
The Demo Application
The dialog below shows a picture of the Accessor Dialog showing the results of
pressing the View Authors button when using the CCommand Accessor.
The Demo Application ( pictured at the top of the page ) contains a small check
box, If you step through the code you will notice that it acts as a simple
flag. If the box is checked the application will call the Command Accessor
version of the function instead of the Table Accessor version. This is to show
that the methods between the two accessors are basically the same. The main
difference comes through when the CCommand Accessor views the categories and
authors through the use of an SQL statement which allows the Command Accessor
to specify conditions within the SQL that give the resulting table a much
better look to it than the one achieved with the Table Accessor.
Both methods allow routine maintenance of the tables such as modifying,
deleting and inserting data, with each operation provided as a seperate
function.
Because it is so small the testbooks.mdb file is included in the project zip
file rather than as a seperate download.
The picture above shows the results of pressing the View Categories button when
using the Table Accessor.
Finally
When using a CCommand Accessor it is also possible to change the column
bindings in the same way as the it is with the CTable Accessor although with
the added ability to use raw SQL commands with the CCommand Accessor I tend to
just use the ACCESSOR_MAP macros with just the CTable Accessors and use SQL
with the CCommand Accessors.
Also there are other techniques for accessing data through an SQL statement
with the Command class the main one being the use of the CCommand::Create
function which takes a CSession Object and an SQL Statement. The idea is that
you call Create and then call CCommand::Prepare before calling the
CCommand::Open function, I had problems with this in that I kept running into
Rowset pointers that should be equal to NULL and weren't or Rowset pointers
that were equal to NULL where the code thought they should not have
been. There is an example of this method in the HOWTO article
ExecuteParameterized Command Multiple Times with ATL OLEDB Consumer Templates
which it should be noted despite the title does not use the consumer templates
as generated by the wizard, the code creates an accessor and then bypasses the
class that the wizard generates altogether and does everything manually.
Another way of achieving the same effect is to drop down to the interface level
and use the ICommand::Execute function. This works fine but the work load
required to retrieve the data from the IRowset interface is not something you'd
really want to write for a small application. An example of this technique can
be found in the article OLE DB for the ODBC Programmer under the sub heading
OLE DB Rowsets. The example is almost one hundred lines long.
In closing the reason for the change of database between this article and the
last two was due to inbuilt relationships within the access table that would
have required me to deal with multiple table access before I was ready
to. Multiple table access will be encountered in the next C++
article.
