Using Blobs with WTL OLE DB Database Applications

• Download SQL Server project (includes source) - 16 Kb
• Download MS Access project (includes source) - 16 Kb
• Download Blob DDX source code only - 5 Kb
• Download SQL Server executable - 30 Kb
• Download MS Access executable - 30 Kb

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:

1. 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
2. DDX_EDIT - exchanges text blobs of up to 64K between an edit control and SQL Server text columns or MS Access memo columns
3. 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]

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):

// old DDX entry
   DDX_TEXT(IDC_PRINFO, m_pub.m_prinfo)

// replace with
   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

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);

      // read the provider supplied ISequential stream
      if (pStream->Read(buffer, ulLength, NULL) == S_OK)
      {
	 // create an IStream on the buffer. TRUE means that
	 // the stream will be automatically released
	 ::CreateStreamOnHGlobal(buffer, TRUE, &pIStream);

	 // load the IPicture from the IStream
	 if (::OleLoadPicture(pIStream, 0, FALSE, IID_IPicture,
			      (LPVOID*)&pIPicture) == S_OK)
	 {
	    // display the image in the static control
	    OLE_HANDLE handle;
	    pIPicture->get_Handle(&handle);
	    CStatic(pT->GetDlgItem(nID)).SetBitmap((HBITMAP)handle);
	 }
      // release the database stream
      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

1. MS Access OLE Object bitmap images are read-only. Future versions of blobddx.h may allow writes
2. SaveImageToFile() saves in bitmap format only. (Unisys LZW patent issue, not a technical issue)
3. OleSavePictureFile(), used in SaveImageToFile(), may crash with large images (> 5MB)
4. OleLoadPicturePath(), used in LoadImageFromFile(), may allocate and not release resources
5. DDX_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.