Click here to Skip to main content
15,390,470 members
Articles / Desktop Programming / WTL
Posted 6 Sep 2002


32 bookmarked

Using Blobs with WTL OLE DB Database Applications

Rate me:
Please Sign up or sign in to vote.
4.80/5 (4 votes)
6 Sep 20026 min read
Describes an extension to DDX that can read and write blobs using OLE DB providers. Includes sample WTL OLE DB database projects for SQL Server "Pubs" and MS Access "Northwind"


Figure 1. SQL Server Pubs.pub_info sample project


Figure 2. MS Access Northwind.Categories sample


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.

   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)

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

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

// 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( 0, true )
      COLUMN_ENTRY(1, m_pubid)
      COLUMN_ENTRY(3, m_prinfo)
   BEGIN_ACCESSOR( 1, false )
      BLOB_ENTRY_LENGTH_STATUS(2, IID_ISequentialStream, STGM_READ, m_logo,
			       m_logoLength, m_logoStatus)

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( 0, true )
      COLUMN_ENTRY(1, m_pubid)
   BEGIN_ACCESSOR( 1, false )
      BLOB_ENTRY_LENGTH_STATUS(2, IID_ISequentialStream, STGM_READ, m_logo,
			       m_logoLength, m_logoStatus)
   BEGIN_ACCESSOR( 2, false )
      BLOB_ENTRY_LENGTH_STATUS(3, IID_ISequentialStream, STGM_READ, m_prinfo,
			       m_prinfoLength, m_prinfoStatus)

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

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

      // 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;
      // release the database stream

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


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 article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Ed Gadziemski
Founder Choycer
United States United States
Ed has over 40 years experience in computer technology and a bachelor's degree in Business Administration. He's currently a marketing technology consultant. During his career, he's led software development departments and created software still in use in the communications and healthcare industries. Ed is a veteran of the United States Army. He lives in Arizona in the United States.

Find Ed on Linkedin.

This material is copyright 2019 by Ed Gadziemski. Unauthorized use is strictly prohibited. All rights reserved.

Comments and Discussions

QuestionAccessing Multiple LOBs Pin
thomasoatman13-Mar-17 17:47
Memberthomasoatman13-Mar-17 17:47 
Generalrestore msword doc from database Pin
imation7429-Apr-11 6:20
Memberimation7429-Apr-11 6:20 
GeneralUpdating BLOB Accessors - remember! Pin
NeWi27-Sep-05 10:24
MemberNeWi27-Sep-05 10:24 
GeneralGreat! Pin
NeWi27-Sep-05 8:12
MemberNeWi27-Sep-05 8:12 
GeneralRotating an Image Pin
Alex Evans4-Jan-05 10:30
MemberAlex Evans4-Jan-05 10:30 
Generaldatabase file Pin
nomicism7-Dec-04 5:42
Membernomicism7-Dec-04 5:42 
QuestionCan I use multiple accessors to run different SQL in database? Pin
Angus Comber20-Oct-04 6:34
MemberAngus Comber20-Oct-04 6:34 
AnswerRe: Can I use multiple accessors to run different SQL in database? Pin
NeWi27-Sep-05 10:32
MemberNeWi27-Sep-05 10:32 
GeneralRun error Pin
guylee6-Sep-04 16:20
Memberguylee6-Sep-04 16:20 
Questionsupport SQL Server 2000? Pin
DeXian30-May-03 6:02
MemberDeXian30-May-03 6:02 
AnswerRe: support SQL Server 2000? Pin
Ed Gadziemski30-May-03 8:49
professionalEd Gadziemski30-May-03 8:49 
Generalfile atlres.h Pin
Nick Solodkov5-Nov-02 18:27
sussNick Solodkov5-Nov-02 18:27 
GeneralRe: file atlres.h Pin
Ed Gadziemski6-Nov-02 2:16
professionalEd Gadziemski6-Nov-02 2:16 
GeneralNice and useful! Pin
Yasuhiko Yoshimura7-Sep-02 18:49
MemberYasuhiko Yoshimura7-Sep-02 18:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.