Click here to Skip to main content
Click here to Skip to main content

Using Blobs with WTL OLE DB Database Applications

, 6 Sep 2002
Rate this:
Please Sign up or sign in to vote.
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"

WTL OLE DB DDX Sample 2

Figure 1. SQL Server Pubs.pub_info sample project

WTL OLE DB DDX Sample 3

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]. 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_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);

      // 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.

License

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 KwikiVac
United States United States
Ed has over 40 years experience in computer technology and a bachelor's degree in Business Administration. He currently owns an online retail business and previously owned a software consulting firm. During his career, he has 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.
 
This material is copyright 2014 by Ed Gadziemski. Unauthorized use is strictly prohibited. All rights reserved.

Comments and Discussions

 
Generalrestore msword doc from database Pinmemberimation7429-Apr-11 6:20 
GeneralUpdating BLOB Accessors - remember! PinmemberNeWi27-Sep-05 10:24 
GeneralGreat! PinmemberNeWi27-Sep-05 8:12 
GeneralRotating an Image PinmemberAlex Evans4-Jan-05 10:30 
Generaldatabase file Pinmembernomicism7-Dec-04 5:42 
QuestionCan I use multiple accessors to run different SQL in database? PinmemberAngus Comber20-Oct-04 6:34 
AnswerRe: Can I use multiple accessors to run different SQL in database? PinmemberNeWi27-Sep-05 10:32 
GeneralRun error Pinmemberguylee6-Sep-04 16:20 
Questionsupport SQL Server 2000? PinmemberDeXian30-May-03 6:02 
AnswerRe: support SQL Server 2000? PinmemberEd Gadziemski30-May-03 8:49 
Generalfile atlres.h PinsussNick Solodkov5-Nov-02 18:27 
GeneralRe: file atlres.h PinmemberEd Gadziemski6-Nov-02 2:16 
GeneralNice and useful! PinmemberYasuhiko Yoshimura7-Sep-02 18:49 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 7 Sep 2002
Article Copyright 2002 by Ed Gadziemski
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid