Intermeadiate OLE DB Consumer in C++

• Download source files - 100 Kb

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(); /// update
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 ) /// update the number of accessors
	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(); /// get the accessor back to a valid point in the rowset
m_Books.GetData( 0 ); /// go back to using the main accessor
m_Books.MoveFirst(); /// Move to the first row using this accessor previous GetData call
                   /// postioned you before the first row.

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.