AOTBLOB Read/Writes BLOB Using OLE DB Consumer Template

ID: Q190958

The information in this article applies to:

Microsoft Visual C++, 32-bit Editions, version 6.0
ActiveX Data Objects (ADO), version 2.0
Microsoft Data Access Components version 2.1 SP2

SUMMARY

The AOTBLOB sample demonstrates reading and writing long binary data (BLOB) fields using the ATL OLE DB Consumer Template classes. The sample contains code to load and save a bitmap file and to load and save the bitmap image into a database table.

The sample also demonstrates how to prompt the user to select a datasource using the Microsoft Data Link dialog box, in the same manner as a browse connect in ODBC.

NOTE: The sample automatically creates a table named BLOB1234 on the target datasource for insertion and extraction of the BLOB field.

The sample code was tested with the following OLE DB providers:

Microsoft Jet 3.51 OLE DB Provider (Msjtor35.dll 3.52.1527.4)
Microsoft OLE DB Provider for Oracle (Msdaora.dll 02.00.3002.15)
Microsoft OLE DB Provider for SQL Server (Sqloledb.dll 07.00.0502)
Microsoft OLE DB Provider for ODBC Drivers (Msdasql.dll 02.00.3002.11)

The sample was also tested with the following ODBC drivers accessed via the Microsoft OLE DB Provider for ODBC Drivers:

Microsoft SQL Server ODBC Driver (Sqlsrv32.dll 3.60.0319).
Microsoft ODBC Driver for Oracle (Msora32.dll 2.573.292700).
Microsoft Access Driver (Odbcjt32.dll 3.51.1713)

MORE INFORMATION

The following files are available for download from the Microsoft Download Center. Click the file names below to download the files:

Aotblob.exe

For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address

http://www.microsoft.com/downloads/search.asp

and then click How to use the Microsoft Download Center.

Reading BLOB Data Using the ATL OLE DB Consumer Templates

The ATL OLE DB Consumer Template Wizard creates a fully functional read- only class that reads a long binary (BLOB) database field. If you want to write to the long binary field, you must make modifications to the wizard- generated class.

In a wizard-generated OLE DB consumer class, the provider automatically populates any BLOB field member variables with a ISequentialStream pointer for the current row. To read data from the BLOB field, you call the Read method of the ISequentialStream interface in a loop until no more data is sent by the OLE DB provider. Note that most providers don't provide you with the total length of the data prior to reading the data, so you must build a buffer dynamically to hold the data as you read it.

Writing BLOB Data Using the ATL OLE DB Consumer Templates

To write to a BLOB field, you must release the ISequentialStream pointer provided to you by the OLE DB provider and replace it with an ISequentialStream pointer that you implement. The provider then calls ISequentialStream::Read on your interface pointer until no more bytes are returned. This in a way reverses the role of the consumer and provider. The consumer provides the ISequentialStream pointer, and the provider calls ISequentialStream::Read. Note that certain OLE DB providers require that you indicate up front how many bytes are in the BLOB field when you write the BLOB data. To work with as many providers as possible, the sample provides the length information to the provider in every case.

To simplify the reading and writing of BLOB data with OLE DB, the AOTBLOB sample has a helper class called CISSHelper. The CISSHelper class implements the entire ISequentialStream interface. The class also has a built-in buffer, which is used to store and extract the BLOB data. Note that the CISSHelper does not destroy itself when its release count drops to zero, similar to a typical COM interface. The AddRef and Release are left in so the provider can call them, but this sample uses a stack allocated object and relies on the C++ destructor to clean up the object.

To allow writing to a BLOB field, you must modify the ATL OLE DB Consumer wizard-generated binding macro and add support for a field length and status indicator as below:


   // Before:

   BEGIN_COLUMN_MAP(...)
   // Various fields..
      BLOB_ENTRY(2, IID_ISequentialStream,STGM_READ, m_BLOBDATA)
   // Various fields...
   END_COLUMN_MAP()

   // After:
   ULONG m_BLOBDATA_LENGTH;
   ULONG m_BLOBDATA_STATUS;

   BEGIN_COLUMN_MAP(...)
   // Various fields...
      BLOB_ENTRY_LENGTH_STATUS(2, IID_ISequentialStream, \ 
                               STGM_READ, m_BLOBDATA, \ 
                m_BLOBDATA_LENGTH, m_BLOBDATA_STATUS)
   // Various fields...
   END_COLUMN_MAP()

The length indicator is needed to provide the length of the BLOB data to the OLE DB provider when you write to the BLOB. Note that the definition of the BLOB_ENTRY_LENGTH_STATUS macro is included in the AOTBLOB sample project--it is not included in the ATL header files for the ATL OLE DB consumer classes.

No additional modifications to the wizard-generated templates are needed.

To read from the BLOB field using a modified (length and status added) consumer template class, follow this basic formula:

Open your modified ATL OLE DB consumer class.

Move to the desired record.

Check the status of the BLOB field. If the BLOB field status indicator is not set to DBSTATUS_S_OK, you may have a null BLOB field. Do not use the provider-created ISequentialStream pointer if the status field is not set to DBSTATUS_S_OK; it may not be a valid pointer.

Loop and call ISequentialStream::Read in blocks until the returned bytes read parameter indicates all of the data is read. You can either process the incoming data in blocks inside the loop or combine the blocks into one large buffer for later use.

Call FreeRecordMemory() on the template class to release all field buffers.

Move to the next record, close the recordset, and so on.

To write to the BLOB field, follow these general steps:

Open your modified ATL OLE DB consumer class.

Move to the desired record.

Check the status of the BLOB field. If the BLOB field status indicator is not set to DBSTATUS_S_OK, you may have a null BLOB field. If the status is DBSTATUS_S_OK, you must call release on the IsequentialStream pointer given to you by the provider.

Copy the data you want to insert into the BLOB field into a CISSHelper class object. The object implements the ISequentialStream::Write method to assist you here.

Set the length indicator to the length of the data in the CISSHelper class object.

Set the status indicator to DBSTATUS_S_OK.

Set the BLOB member field (the ISequentialStream pointer) to point to the CISSHelper object.

Call the SetData method on the consumer class to trigger the update. At this point the provider calls ISequentialStream::Read on the CISSHelper class to load in all of the data.

Call FreeRecordMemory() on the consumer class to release all field buffers.

Move to next record, close recordset, and so on.

Note that if you wanted to add a new record rather than modify an existing record, just change step 7 above to call Insert() rather than SetData().

Additional query words:

Keywords : kbfile kbADO200 kbDatabase kbDriver kbODBC kbOracle kbProvider kbVC600 kbGrpMDAC
Version : WINDOWS:2.0,2.1 SP2; winnt:6.0
Platform : WINDOWS winnt
Issue type : kbhowto