Ole Db Consumer Templates The Best Software For Your
The OLE DB header files provide the very basic declarations that allow your application to use the OLE DB interfaces and consequently the Consumer Templates. As I showed previously, the default MFC header files are prepared to prevent OLE DB usage. I have started with the ATL OLE DB provider wizard. After fixing an apparent issue with the COM_INTERFACE_ENTRY2 macro I managed to get the default code to compile, but so far I'm not having much success using it.
Introduction
The article reveals a very critical bug in the CCommand ATL OLEDB object. The article explains why it's happening and provides a solution for it.
Background
Microsoft Knowledge Base http://support.microsoft.com/kb/271926 describes a well-known memory leak problem and the way to fix it. However, there is one major memory leak problem not seen reported anywhere. Most likely, nobody uses ATL OLEDB this way. However, it is a typical use scenario in my project as performance is critical.
Who would run the same command for a million times as in this example? I will. The idea is to prepare the command with parameters and run it with different parameters. We use it to write trading orders during the day, real time to a SQL Server. This way, we will have the best performance as you don't need to generate the SQL string again and again.
If you copy the code in your project and run it (please use the correct user and password for your DB environment), you will find in the Task Manager that the memory usage increases in 100Ks a second.
The Bug
CCommand::Open() calls CCommand::ExecuteAndBind and then CCommand::Execute(). This is the implementation of CCommand::Execute():
The line in bold is the source of the problem.
This code doesn't check if GetMultiplePrtAddress, in this case ComPtr<IMultipleResults> m_spMultipleResults, is previously set or not. If it's set previously, this interface pointer should be released first before being assigning with the new interface. Although the interface is protected in a smart pointer ComPtr, all previous interface pointers are lost except the last interface pointer. So, in the end, the last interface is correctly released.
Solution
Release this interface explicitly every time after the command closes.
To make it nice, you can override CCommand::Close() and put the fix in as an extra.
Introduction
This article is the first of a series OLE DB articles that cover database access using the OLE DB Templates. This is aimed primarily at beginners while later articles will cover the intermeadiate and advanced aspects of using the OLE DB Consumers. The code for these articles is developed on Win 2k under Developer Studio 6 and Developer Studio 7. It will be stated and in some cases obvious which version of developer studio the code was written in. The code for this article was written in Developer Studio 6.
First of all an ODBC Driver needs to be specified on the system. To do this open the Control Panel and double click on the ODBC Data Sources. This is located in the Control Panel on Windows 98 and in Control PanelAdministrative Tools in Windows 2000. Click on the System DSN tab and click on the add button. If you are following this using the Microsoft NWind database supplied select a Microsoft Access Driver ( *.mdb ) and call the Driver 'AccessNWind' in the DataSource Name box and click O.K. What this does is create an ODBC DataSource that uses the access driver to access the database. All this will work transparently behind the scenes once we create the OLE DB Consumer class.
To Insert an OLE DB Consumer class in the project first of all create a standard MFC project. I've created a simple dialog project but in a real working environment you'd probably want a better display system, that is, if you wanted a display system at all. Once you have your project go to the Insert menu and clin the 'New ATL Object' menu. You will be asked if you want to add ATL support to your MFC project. Answer yes to this and the main ATL dialog box will open.
Select the Consumer and click next.
The image above shows the completed dialog for now yours wont look too much like this so Click on the Select Datasource button and we'll set it up.
Although the picture above shows the Microsoft OLE DB Provider for ODBC Drivers it's better to select one of the Microsoft Jet drivers as the Microsoft OLE for ODBC drivers is no longer supported in the beta release ( 2 ) of Developer Studio 7. I don't know if this will be the case in the final release but presume it will.. Click on the Next button,
Select the ODBC Driver for the AccessNWind Database that was set up earlier in the use datasource name option. The next two pages of the dialog are simple so I'll go through them quickly here. Click the Test Connection button just to make sure that the connection to the Database is working correctly. As long as you've set up the ODBC driver properly there shouldn't be any problem. If you click on the Advanced tab the page presents you with the options that you require for accessing the database. Note that these options are to tell the driver the options that you require and at this point they are not going to be reflected in your codes access to the the database. For this example it will be fine if you select read/write access to the database. The final page in the dialog displays the results of what you have selected.
Once you click on the O.K. button you will be given the dialog below
This dialog gives you the tables that are available in this database so that the required code can be generated to access the database from your code. You dont have to choose the Suppliers Database here but it might help if you want to follow the examples coming up later.
Once you have selected the database table you will be taken back to the starting dialog. which will be filled in and almost ready to finish and get on with the code.
On the dialog you should select the table type. The two types will in fact make little difference within this example but I tend to stick to the guideline that tables are for when you want to go through the entire set of data within the table on a row by row basis and the Command type is for when you want to customize the data that you get back from the table. There will be a lot more on commands in later examples as these will will be primarily what we will use.
The final part of this dialog is the Support that we are going to add to the database. In this example I've clicked them all but you should always remember at this point that there's no point specifying a delete option here if you didn't tell the driver to have write access to the database. At this point if you've forgotten something you can change it easily.
Once you're happy with the dialog and the settings click on O.K.
The Generated Code
Ole Db Consumer Templates The Best Software For Your Home
You should now have a Suppliers.h file added to your project and this is the code that will do all your database access for you. If you look at the file you will see there are two classes generated the first is the Accessor class and the second is the CSuppliers class. If you knew to OLE DB the accessor class comes as something of a surprise because the first you know about it is when you open the generated header file.
The Accessor Class
An accessor class is well, basically an accessor to the database. It defines the structure of the database for your database class, which in this case is
The class above is map that the code will use for a generated table. As you can see it is nothing more than a copy of the columns in the database and once it is initialised you will be able to move through the database using CRowset::MoveNext();. The only thing that stands out here is the macros
END_COLUMN_MAP(). These macros are used for the positioning of the member variables in a Command Accessor. If when we were at the dialogs we had clicked on the Command radio button our class would have looked like this, You can see hear that the Accessor has filled in the BEGIN_COLUMN_MAP,
COLUMN_ENTRY( column, variable ) macros that bind the data in the rows when it is retrieved using the SQL statement which is a new part of the class that is part of the macro, At the moment as I said earlier it doesn't matter if you use the Table or the Command Accessor at this point. I'll be highlighting the power of the Command Accessor in the next essay. The CSuppliers Class
The CSupplier class is the class that you will actually use in the code. The supplier class inherits the accessor class that is defined above.
As can be seen from the class declaration
The SupplierAccessor is wrapped by the CAccessor class which in turn is wrapped by the CTable class. and the whole lot is then inherited by the CSuppliers class which has very little functionality of its own. The CSupplier class gets its functionality through the Accessor, Rowset and Table classes that it inherits from. What the CSupplier class does do is open the connection to the database through the CSuppliers::Open function. This function calls the remaining two functions declared in the class.
The CSuppliers::OpenDataSource function sets up the properties for opening the database by declaring and then Adding the properties to a CDBPropSet class object. It also creates a CDataSource object and opens the database using the
CDBPropSet object that has been set up. The CSuppliers::OpenRowSet function then sets up the properties for opening the rowset by declaring another CDBPropSet object and initialising it with the
What Is The Best Software
CTable::Open function. The Sample Code
The sample application uses the created files to load the data from the database and to display it in a simple listbox format. which uses a header control so that the view of each column can be spaced in order to be able to read it properly.
To get the data from the database simply click on the Get Data button in the top left hand corner of the dialog box.
The CSuppliers class is used by including it in the Suppliers.h header file in stdafx.h and then declaring an instance of the CSupplier class as a member of the COLEDBConsumer1Dlg class. The class is used in the OnbnClickedButton1 function and one the files have been generated it's use in a project like this couldn't be easier.
First of all the open function is called followed by the move first function. That's all there is too it. All we have to do from here on in is load the data into the list box like so.
And that's how you set up and access an OLE DB database using OLE DB Templates. In the next article we'll look at saving and modifying data and how to customize queries using the Command class.