Monday, September 22, 2008

Updating data

There are two ways to update data using OLE DB: using SQL DML commands (which I will look at in later posts), or through the IRowsetChange interface exposed through a base table IRowset. It supports the basic methods to insert new rows (InsertRow), update existing rows (SetData) and to delete rows (DeleteRows) and is only exposed when the consumer sets the DBPROP_IRowsetChange property to VARIANT_TRUE.

To illustrate this type of data update, I put up a sample that allows you to edit the "Suppliers" table from the Northwind sample database. The list view display code has been changed in order to support a load-on-demand cache of rows, stored in their native (CRowset's) format. The cache items are preloaded when the list view notifies the parent through the LVN_ODCACHEHINT notification message. Row bookmarks are stored along the row data, so we can now very quickly position the rowset cursor in any row so we can edit its data.

Note that when modifying any data, we have to make sure that both the bookmark and the row ID columns are not touched because both are managed by the engine (the row ID is an IDENTITY column that gets automatically filled in upon insert). Apart from this, setting data is just a matter of copying the new row value to the rowset and correctly setting the column status and length (optional for fixed-length types).

This code does not support BLOBs yet. I will look at these in my next post.

Sample project: EditRow.zip (1.29 MB)

6 comments:

James said...

I have created a static lib project called oledbcli.lib containing the table and database .cpp files and headers.

And used this standalone project lib to link the DB functions into the createsdf, rowcount, editrow, and opentable demo programs.

Works pretty well.

When trying to compile as a DLL using dllexport on the oledbcli classes I received some interesting errors (warnings?) about ATL classes needing to be exported, too.

Is creating a DLL even possible?

I'm asking because when creating a new project "SmartDevice Project", you can create a DLL or static lib. Microsoft seems to like ATL libraries linked in a DLL and not a static lib (if I read those checkboxes correctly).

So, is a static lib advisable? (It seems to work with your demo programs).

Will you be showing how to link this into an MFC project like you did with the ATL consumer templates? (I tried those with VC8..no luck)

So far, having #include "afx.h" and other MFC includes in stdafx.h causes the compile to fail (around the CString typedef??).

Also, WTL seems to fail on compile when "DeviceResolutionAware.h" is included. Not sure though if this is ever needed or if WTL is actually needed. (I'm very new newbie)

Anyway, thanks for this tutorial. It is very cool.

João Paulo Figueira said...

I have to confess that I never tried to compile the code as a DLL, but I will surely do so for the next post. There should be no special requirement for that.

You should also be able to compile the code with MFC although the CString dependencies should be investigated.

The only disadvantage I see on using a static lib is the size penalty your executable will pay. A DLL would be much leaner and reusable.

I'm not sure about why including the DeviceResolutionAware.h file should cause you problems. Are you using WTL 8.0?

James said...

Yep. Never heard of WTL before. I downloaded it and setup VC8 as a global system include to the WTL8 libraries/includes to C:\WTL80\include so I actually don't have to specify this in the project files.

Also the WTLHelper9.dll ... crashes (vista ultimate). regsrv32.exe crashed...and VC8 refuses to load it.

Perhaps a permissions problem?

now that I look at my IF statement, it looks wrong from the target EXE to include the header correctly...oops....I guess I should fix that (as _WINDLL wont be defined for the target EXE...errg)...see...I'm a newbie

By the way, I added the following to the top of oledbclih

#ifndef __OLEDBCLI_H__
#define __OLEDBCLI_H__

#include "atlbase.h"

#ifdef _WINDLL
#ifdef OLEDBCLI_EXPORTS
#define OLEDBCLI_API __declspec(dllexport)
#else
#define OLEDBCLI_API __declspec(dllimport)
#endif
#else
#define OLEDBCLI_API
#endif

and included OLEDBCLI_EXPORTS in my project defs....and added OLEDBCLI_API to all/most of the class definitions.

compiles OK (with warnings) but I actually haven't tried to use it as a DLL. I've tried a static lib.. works well.

James said...

now that I look at this IF statement a second time, looks wrong for the client EXE that will load this DLL but not have the _WINDLL define. Oh, well. I tried. I'll have to fix this.

James said...

I changed the if statement to

#if defined(_WINDLL) || defined(OLEDDBCLI_DLL)
#ifdef OLEDBCLI_EXPORTS
#define OLEDBCLI_API __declspec(dllexport)
#pragma message(">>>> Exporting OLEDB")
#else
#define OLEDBCLI_API __declspec(dllimport)
#pragma message(">>>> Importing OLEDB")
#endif
#else
#define OLEDBCLI_API
#endif

Well, due to the "Warnings" (I guess) I get the following on the compile:

EditRow.obj : error LNK2019: unresolved external symbol "__declspec(dllimport) public: __cdecl OLEDBCLI::CRowset::CRowset(void)" (__imp_??0CRowset@OLEDBCLI@@QAA@XZ) referenced in function "public: __cdecl CEditRowFrame::CEditRowFrame(void)" (??0CEditRowFrame@@QAA@XZ)

Oh, well, so much for my attempt!

I guess I'll wait for the expert! ( have another project that I created a DLL with and I don't remember doing anything special in the linker )

João Paulo Figueira said...

You don't need the WTL Helper, but it's a nice helper tool. I promise to post the updated sample with the DLL asap.