OLEdbProNet

======================================================================== MICROSOFT FOUNDATION CLASS LIBRARY : AOTBLOB ======================================================================== Sample Note: 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, in the same manner as a browse connect in ODBC. The sample will automatically create 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) Overview: The ATL OLE DB Consumer Template Wizard will create a fully functional read-only class that reads a long binary (BLOB) database field. If you wish 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 will automatically populate 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 send by the OLE DB provider. Note that most providers will not 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. 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. In order 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 it's release count drops to zero like 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. In order 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 you follow this basic formula: 1. Open your "modified" ATL OLE DB consumer class. 2. Move to desired record. 3. Check status of 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. 4. 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. 5. Call FreeRecordMemory() on the template class to release all field buffers. 6. Move to next record, close recordset, etc... To write to the BLOB field, follow these general steps: 1. Open your "modified" ATL OLE DB consumer class. 2. Move to desired record. 3. Check status of 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. 4. 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. 5. Set the length indicator to the length of the data in the CISSHelper class object. 6. Set the status indicator to DBSTATUS_S_OK. 7. Set the BLOB member field (the ISequentialStream pointer) to point to the CISSHelper object. 7. Call the SetData method on the consumer class to trigger the update. At this point the provider will call ISequentialStream::Read on the CISSHelper class to load in all of the data. 8. Call FreeRecordMemory() on the consumer class to release all field buffers. 9. Move to next record, close recordset, etc... Note that if you wanted to add a new record rather than modify an existing record, all you would change is step 7 to call Insert() rather than SetData().