Figure 1. SQL Server Pubs.pub_info sample project
Figure 2. MS Access Northwind.Categories sample
Introduction
This article describes blobddx.h, an extension to atlddx.h
that works with OLE DB consumer applications to exchange image and text blob data
with databases. The extension contains code that enables reading and writing of
MS SQL Server text and MS Access memo fields via streams. It also allows reading
and writing of non-OLE image blobs. Finally, it provides read-only support for
OLE Object bitmap images, such as those in the Northwind database.
There are two sample projects available for download. The first is a standard
WTL dialog-based application that uses edit controls and a static picture control
for data display. It acquires data from the MS SQL Server "Pubs" database via the
OLE DB for SQL Server provider. You must have access to SQL Server
to use the sample.
The second sample acquires data from the MS Access "Northwind" database via
the OLE DB for Jet 4.0 provider. The images are read-only since they
are OLE Objects. You must have the Northwind.mdb file from the Microsoft Office
97 or 2000 samples directory.
Using Blob DDX
blobddx.h contains source code that extends atlddx.h
by providing DDX (Dynamic Data eXchange) for text and image blobs. It contains
these macros/methods:
- DDX_IMAGE - supports exchange of bitmap, gif, and jpg images from SQL Server
image columns for display in a static picture control. It also provides read-only
support for MS Access OLE Object bitmap images
- DDX_EDIT - exchanges text blobs of up to 64K between an edit control and SQL
Server text columns or MS Access memo columns
- DDX_RICHEDIT - supports text blobs of up to 2GB with a RichEdit control
Both DDX_EDIT and DDX_RICHEDIT provide limited
Unicode support when the Unicode flag is set to TRUE. A RichEdit
2.0 control is required to use Unicode with DDX_RICHEDIT.
You can enable RichEdit 2.0 for VC6 dialog applications by manually editing
the .rc file. Change "RICHEDIT" to "RichEdit20a" for any RichEdit
controls that were added with the resource editor. Also, you must change the
RICHEDIT_VER define in stdafx.h to 0x0200.
Blob DDX also contains a WTL port of the CISSHelper class and
a copy of the blob length macro, both from the AOTBLOB MSDN sample. In addition,
it provides a helper method to strip the OLE header off Access bitmap images and
helpers that can load (bmp, gif, jpg) and save (bmp only) diskfile images.
Sample DDX Map
Here is the DDX_MAP from Oledb2View.h of the SQL
Server sample project. It shows the syntax required for the
DDX_IMAGE macro.
BEGIN_DDX_MAP(COledb2View)
DDX_TEXT(IDC_PUBID, m_pub.m_pubid)
DDX_TEXT(IDC_PRINFO, m_pub.m_prinfo)
DDX_IMAGE(IDC_LOGO, m_pub.m_logo, m_pub.m_logoLength, m_pub.m_logoStatus)
END_DDX_MAP()
The pr_info column is defined as a SQL Server text column. It
is actually a blob, but the ATL OLE DB wizard defines it as TCHAR
[1024]. Since the actual data is longer than that, the sample project's
variable was changed to TCHAR [65536]. In theory, with SQL Server 7
or later, you could define two blob streams, one for the text blob and one for
the image blob.
Unicode Support
To support Unicode in a RichEdit 2.0 control with the pr_info
column, you would make the following changes (of course, column datatype would
have to be changed to ISequentialStream* and length and status
variables would have to be defined):
DDX_TEXT(IDC_PRINFO, m_pub.m_prinfo)
DDX_RICHEDIT(IDC_PRINFO, m_pub.m_prinfo, m_pub.m_prinfoLength,
m_pub.m_prinfoStatus, TRUE)
OLE DB Accessors
An accessor is a data structure created by the consumer
(defined in pubinfo.h in the sample project) that describes how row
or parameter data from the data store is to be laid out in the consumer's data
buffer. The provider uses this accessor to determine how to transfer data to and
from the consumer.
The OLE DB consumer wizard creates a COLUMN_MAP (a single accessor
version of an ACCESSOR_MAP) that contains all of the columns from
the selected table. In some cases, a COLUMN_MAP is sufficient, such
as for read-only or non-blob data, especially with Jet 4.0.
A consumer may, however, require multiple accessors. For example, the following
ACCESSOR_MAP, from the SQL Server sample project's pubinfo.h,
contains accessor 0, which accesses data for the two text columns, and accessor 1,
which accesses the image blob data. This accessor map is used to read and write
the Pubs.pub_info table from SQL Server.
BEGIN_ACCESSOR_MAP(CPubInfoAccessor, 2)
BEGIN_ACCESSOR( 0, true )
COLUMN_ENTRY(1, m_pubid)
COLUMN_ENTRY(3, m_prinfo)
END_ACCESSOR()
BEGIN_ACCESSOR( 1, false )
BLOB_ENTRY_LENGTH_STATUS(2, IID_ISequentialStream, STGM_READ, m_logo,
m_logoLength, m_logoStatus)
END_ACCESSOR()
END_ACCESSOR_MAP()
Note the use of the BLOB_ENTRY_LENGTH_STATUS macro for the Logo
column. This macro accesses column 2 using an ISequentialStream for reading and
provides data, length of data, and column status. Several column macros are defined
in atldbcli.h but this macro was copied to blobddx.h
from the MSDN sample AOTBLOB.
Here is how the map from above would be defined to support two blob columns.
SQL Server 7 and above support multiple streams (if DBPROP_MULTIPLESTORAGEOBJECTS
is true).
BEGIN_ACCESSOR_MAP(CPubInfoAccessor, 3)
BEGIN_ACCESSOR( 0, true )
COLUMN_ENTRY(1, m_pubid)
END_ACCESSOR()
BEGIN_ACCESSOR( 1, false )
BLOB_ENTRY_LENGTH_STATUS(2, IID_ISequentialStream, STGM_READ, m_logo,
m_logoLength, m_logoStatus)
END_ACCESSOR()
BEGIN_ACCESSOR( 2, false )
BLOB_ENTRY_LENGTH_STATUS(3, IID_ISequentialStream, STGM_READ, m_prinfo,
m_prinfoLength, m_prinfoStatus)
END_ACCESSOR()
END_ACCESSOR_MAP()
Handling Images
Blob DDX uses an IPicture object to hold images. IPicture can handle
several image formats, including bitmap, gif, and jpeg. Blob DDX uses various
streams and buffers to move image data between the database and the IPicture
and it uses a static picture control to display the image on the dialog.
The following code snippet shows the read code from the method that supports
DDX_IMAGE. The only tricky part is that the OLE DB provider is an
ISequentialStream while IPicture wants an IStream. Therefore, an intervening
buffer is used.
BOOL DDX_Image(UINT nID, ISequentialStream* &pStream, BOOL bSave,
ULONG& ulLength, ULONG& ulStatus)
{
T* pT = static_cast< T* >(this);
if (!bSave)
{
IPicture* pIPicture;
IStream* pIStream = NULL;
void* buffer = ::CoTaskMemAlloc(ulLength);
if (pStream->Read(buffer, ulLength, NULL) == S_OK)
{
::CreateStreamOnHGlobal(buffer, TRUE, &pIStream);
if (::OleLoadPicture(pIStream, 0, FALSE, IID_IPicture,
(LPVOID*)&pIPicture) == S_OK)
{
OLE_HANDLE handle;
pIPicture->get_Handle(&handle);
CStatic(pT->GetDlgItem(nID)).SetBitmap((HBITMAP)handle);
}
pStream->Release();
}
::CoTaskMemFree(buffer);
}
}
Blob DDX also provides helpers to load and save diskfile images. Reading is
handled by the LoadImageFromFile() method and writing is handled by
SaveImageToFile(). Both methods launch standard File dialogs to
prompt users for file names. After an image is loaded from file, it is placed in
the IPicture object and can be subsequently saved to the database (keeping in
mind that saving of OLE Objects is not supported).
Known Issues
- MS Access OLE Object bitmap images are read-only. Future versions of
blobddx.h may allow writes
SaveImageToFile() saves in bitmap format only. (Unisys LZW
patent issue, not a technical issue)OleSavePictureFile(), used in SaveImageToFile(),
may crash with large images (> 5MB)OleLoadPicturePath(), used in LoadImageFromFile(),
may allocate and not release resourcesDDX_EDIT Unicode support is from the perspective of converting
a Unicode data source, such as an MS Access memo field, to ANSI for display in
an ANSI edit control. It may not work with Unicode edit controls
Acknowledgements
Some information was derived from Microsoft Knowledge Base Article Q190958
"AOTBLOB Read/Writes BLOB Using OLE DB Consumer Template". Two macros were
copied and the ISSHelper class was ported from the MSDN sample AOTBLOB.
Terms Of Use
The sample project available with this article is free. SoftGee retains
copyright to the code in blobddx.h except for those portions from
the AOTBLOB sample which are copyrighted to Microsoft.
Please note that this code is capable of permanently deleting records from a
live database. USE AT YOUR OWN RISK AND ALWAYS BACK UP YOUR DATA!
THIS SOFTWARE IS DISTRIBUTED AS-IS, WITHOUT WARRANTIES OF ANY KIND.
