OLE DB access to BLOB-type data

Modern database systems in addition to supporting a number of standard common data types, most also support a kind of data called blob type.
BLOBs are all called big large object bytes, a sophomore, and this type of data is typically used to store documents, pictures, audio, and other files, which are generally large in size and can be easily managed and retrieved by saving these files. The BLOB data types that are common in MS SQL Server are text, ntext (n for Unicode), image, nvarchar, varchar, varbinary, and so on. Where image basically can be used to save all binary files, such as Word, Excel, audio, video and so on type.
OLE DB also provides support for BLOB-type data

Pros and cons of using blob-type data

General database on the BLOB type of data has a special processing, such as compression, etc., in the database storage BLOB data can be easily retrieved, displayed, backup and other operations. However, because the BLOB type data itself is large, the amount of data is too large to slow down the database performance, so the general argument is to try not to store this kind of information in the database. Especially pictures, audio and video. The general practice for this type of file is to keep it in a path clock in the system and store the corresponding path in the database

General methods for manipulating BLOB-type data

It is generally not possible for blobs to operate like normal data, and requires some special action, in OLE DB, by setting some special values in the binding structure to eventually specify a ISequentialStream interface pointer to get the BLOB type data, This interface will eventually read and write blob-type data.

Determine if a column is blob-type data

Judging whether a column is blob-type data generally passes the following two conditions:

1. Pcolumninfo[i].wtype = = Dbtype_iunknow: The wtype value of the Dbcolumnsinfo struct object containing the column information is Dbtype_iunknow, and the column is of type Dbtype_iunknow, This condition is also known as column type determination
2. Pcolumninfo[i].dwflags & Dbcolumnflags_islong: When the Dwflag value in the column information is Dbcolumnflags_islong, that means the column's identity contains DBCOLUMNFLAGS_ The Islong attribute, which is also called the column identification decision
   When one of these two conditions is established, we can conclude that this column is a blob-type data

Binding of Blog-type data

There are special requirements for the binding of BLOB-type data, mainly in the following points:

1. The cbmaxlength of the binding structure needs to be set to 0
2. The wtype of the binding structure is set to Dbtype_iunknow
3. Allocates memory for the pobject pointer of the structure, size equal to the size of the dbobject structure
4. Specify the members of the Pobject
   POBJECT->IID = Iid_isequentialstream
   Pobject->dwflags = Stgm_read
5. Adds the length of a IStream pointer to the row buffer length, at which point the data source no longer provides the queried data and provides an interface pointer, which is used by subsequent operations on the BLOB data
   After the last use, remember to release the memory space that pobject points to

Reading BLOB data

By creating the binding structure as previously described, and assigning a value to the binding structure, you can eventually get a ISequentialStream interface pointer from the result set. The Read method of the calling interface can read the data in the BLOB column, and the length of the BLOB data is stored at the data-length memory offset specified by the binding, which is the same as the length of the normal column, and the average BLOB data is longer, which requires a segmented read.
One of the things to be aware of when manipulating BLOB data with the ISequentialStream interface is that some databases do not support accessing multiple BLOB data columns in one accessor. General BLOB data columns and their consumption resources, and the database encourages us to design a database table structure with only one column of BLOB data, so many databases do not support reading multiple BLOB data in one accessor.
To determine whether a database supports reading multiple BLOB data in an accessor, you can get the Dbprop_multiplestorageobjects property, which belongs to the property set Dbpropset_rowset, which is a read-only property. If the value of this property is true for support, false indicates not supported.
The following is an example of reading a BLOB type of data in which the table structure in the database is: ID (int), text (image), PNG (image), JPG (image)

voidReadblob (IRowset *pirowset) {com_declare_interface (IColumnsInfo);    Com_declare_interface (IAccessor); Dbordinal CColumns =0;    dbcolumninfo* rgcolumnsinfo = NULL;    Lpolestr lpszcolumnsname = NULL;    dbbinding* rgbindings = NULL; dbbinding** ppbindings = NULL;//Binding structure arrayDWORD *pudatalen = NULL;//Current accessor required memory sizeDWORD *pulcolcnt = NULL;//items contained in the current accessorULONG ulbindcnt =0;//number of accessorsULONG Ublob =0;//How much BLOB data is currentlyhaccessor* phaccessor = NULL;    hrow* hrow = NULL; Dbcountitem ulgetrows =0; ULONG Ucols =0; PVOID pData1 = NULL;buffering of data in//1th accessorPVOID pData2 = NULL;buffering of data in//2nd accessorPVOID pData3 = NULL;buffering of data in//3rd accessorHRESULT hres = Pirowset->queryinterface (Iid_icolumnsinfo, (void* *) &picolumnsinfo); Com_success (hres, _t ("The query interface Picolumnsinfo failed with the error code:%08x\n"), hres);    hres = Picolumnsinfo->getcolumninfo (&ccolumns, &rgcolumnsinfo, &lpszcolumnsname); Com_success (hres, _t ("Get result set column information failed with error code:%08x\n"), hres); Ppbindings = (dbbinding**) com_alloc (sizeof(dbbinding*)); Rgbindings = (dbbinding*) com_alloc (sizeof(dbbinding) * ccolumns); pulcolcnt = (dword*) com_alloc (sizeof(DWORD)); Pudatalen = (dword*) com_alloc (sizeof(DWORD)); for(inti =0; i < CColumns; i++) {//If the current accessor corresponds to an array of binding structures whose first address is empty, the current binding structure pointer is used as the first address of the binding structure array        if(NULL = = ppbindings[ulbindcnt])        {PPBINDINGS[ULBINDCNT] = &rgBindings[i];        } ++pulcolcnt[ulbindcnt];        Rgbindings[i].bprecision = rgcolumnsinfo[i].bprecision;        Rgbindings[i].bscale = Rgbindings[i].bscale; Rgbindings[i].cbmaxlen =Ten*sizeof(WCHAR);        Rgbindings[i].dwmemowner = dbmemowner_clientowned; Rgbindings[i].dwpart = Dbpart_length | Dbpart_status |        Dbpart_value;        Rgbindings[i].eparamio = Dbparamio_notparam;        Rgbindings[i].iordinal = rgcolumnsinfo[i].iordinal;        Rgbindings[i].obstatus = pudatalen[ulbindcnt]; Rgbindings[i].oblength = pudatalen[ulbindcnt] +sizeof(Dbstatus); Rgbindings[i].obvalue = Rgbindings[i].oblength +sizeof(ULONG); Rgbindings[i].wtype = DBTYPE_WSTR;if(Rgcolumnsinfo[i].wtype = = Dbtype_iunknown | | Rgcolumnsinfo[i].dwflags & dbcolumnflags_islong) {Rgbindings[i].cbmaxlen =0;            Rgbindings[i].wtype = Dbtype_iunknown; Rgbindings[i].pobject = (dbobject*) com_alloc (sizeof(DBObject));            Rgbindings[i].pobject->iid = Iid_isequentialstream;            Rgbindings[i].pobject->dwflags = Stgm_read;        ublob++; }//Record the size of memory required for each accessorPUDATALEN[ULBINDCNT] = Rgbindings[i].obvalue + Rgbindings[i].cbmaxlen;if(Rgbindings[i].wtype = = Dbtype_iunknown) {PUDATALEN[ULBINDCNT] = Rgbindings[i].obvalue +sizeof(isequentialstream*); } pudatalen[ulbindcnt] = Upground (pudatalen[ulbindcnt]);//Determine if a separate accessor is currently required to be created        if((Ublob | | rgbindings[i].iordinal = =0)) {ulbindcnt++; Ppbindings = (dbbinding**) com_realloc (ppbindings,sizeof(dbbinding*) * (ulbindcnt +1)); Pudatalen = (dword*) com_realloc (Pudatalen,sizeof(DWORD) * (ulbindcnt +1)); pulcolcnt = (dword*) com_realloc (pulcolcnt,sizeof(DWORD) * (ulbindcnt +1)); }    }//Create accessorsPhaccessor = (haccessor*) com_alloc ((ulbindcnt +1) *sizeof(Haccessor)); hres = Pirowset->queryinterface (Iid_iaccessor, (void* *) &piaccessor); Com_success (hres, _t ("The query IAccessor interface failed with the error code:%08x\n"), hres); for(inti =0; i < ulbindcnt; i++) {hres = Piaccessor->createaccessor (Dbaccessor_rowdata, Pulcolcnt[i], ppbindings[i],0, &phaccessor[i], NULL); Com_success (hres, _t ("Create accessor failed with error code:%08x\n"), hres); }//read one row of datahres = Pirowset->getnextrows (Db_null_hchapter,0,1, &ulgetrows, &hrow); Com_success (hres, _t ("Failed to read row data with error code:%08x\n"), hres);//Read the information in the first binding structurePData1 = Com_alloc (pudatalen[0]); hres = Pirowset->getdata (hrow[0], phaccessor[0], pData1); for(inti =0; I < pulcolcnt[0]; i++) {if(ppbindings[0][i].wtype = = dbtype_iunknown) {Dbstatus dbstatus = * (dbstatus*) ((byte*) pData1 + ppbindings[0][i].obstatus);if(Dbstatus = = DBSTATUS_S_OK) {ULONG Ufilelen = * (ulong*) ((byte*) pData1 + ppbindings[0][i].oblength);if(Ufilelen >0) {DWORD dwreaded =0;                    PVOID pfiledata = Com_alloc (Ufilelen);                    ZeroMemory (Pfiledata, Ufilelen); ISequentialStream *pseqstream = * (isequentialstream**) ((byte*) pData1 + ppbindings[0][i].obvalue);                    Pseqstream->read (Pfiledata, Ufilelen, &dwreaded); Writefiledata (_t ("1.txt"), Pfiledata, dwreaded); }            }        }    }//The subsequent sections are no longer written, similar to the code abovePirowset->releaserows (1, hrow, NULL, NULL, or null); __CLEAR_UP://followed by clean code

Since we know the structure of the data table beforehand, it has 3 blob data, so there is a direct definition of 3 buffers to receive 3 blob-type data. To facilitate detection, we have also written a function that writes the read BLOB data to a file and then tests the code to see if the file is correctly displayed.
First, as before, get the structure of the data table, and then bind, note that because of the use of SQL Server, it does not support an accessor to access multiple blobs, so there is no way to determine the direct binding of different accessors.
Using ulbindcnt as the number of current accessors at bind time, there is a judgment in the loop (Ublob | | rgbindings[i].iordinal = = 0) && (ulbindcnt! = cColumns-1) The number of accessors is added by 1 when the condition is established, which indicates that the BLOB data was previously available (the SQL does not support an accessor to access multiple blobs before, if the BLOB data was already present, it would need to be created separately) or is currently line No. 0 (because line No. 0 allows only read, So it is treated as BLOB data, and when these conditions are set, a new accessor is added, and as the accessors increase, the elements in the ppbindings array need to be changed, and the array stores the pointers that the accessor's corresponding binding structure begins. The array Pudatalen represents the size of the memory required by the current accessor, pulcolcnt indicates how many columns are present in the current accessor, and the contents of these structures are, for this table, roughly as follows:

After the binding is done, the corresponding accessors are created based on the contents of the array, then the data is bound, read, and, for the BLOB data, we get the interface pointer from the corresponding buffered Obvalue offset, then the Read method of the interface is called, and the file is finally written

Write to BLOB data:

The ISequentialStream interface is also required to write to BLOB data, but unlike the Write method, which can be used directly with the interface, the object written must derive itself from the ISequentialStream interface and specify a memory as a buffer. To be used as a data buffer when the OLE DB component calls the Write method. This buffer must be guaranteed to be allocated on the COM heap, that is, to allocate memory using Cotaskmemory.
The main objects involved are IStream, ISequentialStream, IStorage, ILockBytes, as well, not all data sources support these 4 types of objects, specifically support what can be queried dbpropset_ The Dbprop_structuredstorage attribute in the DataSourceInfo attribute set determines that the ISequentialStream interface is currently supported in SQL Server.
Although we can implement read-write BLOBs in this way, each data source supports a different degree of support, and some data sources do not even support this approach, and in order to query to what extent the read-write BLOB data is supported, you can query the dbpropset_ The Dbprop_oleobjects property of the DataSourceInfo property collection to determine
Typically there are several support methods (the value of the Dbprop_oleobjects property, the bitwise setting):

1. Dbpropval_oo_blob: This is the interface that was introduced before, the way to read and write BLOB data using interface
   Dbpropval_oo_directbind: Can be directly bound in the line, through the row accessor like a normal column access, that is, it does not need to get a special pointer to operate, he can just like the normal data, the allocation of the corresponding memory can be accessed, but pay attention to the size of allocating memory, The difference in the data length of the blob in the corresponding column in each row may be obvious, for example, some may be a 2-hour movie file, and some may be a short video, the gap between them may reach up to G, and according to the smallest may occur truncation, according to maximum allocation may occur up to several g of memory waste
   Dbpropval_oo_ipersist: Persist object access via IPersistStream, IPersistStreamInit, or IPersistStorage three interfaces
   Dbpropval_oo_rowobject: Supports the entire row as an object to access, through the Igetrow interface of the result set object to obtain the row object, but this mode will break the third paradigm, so the general database does not support
   Dbpropval_oo_scoped: Exposes a Row object through the IScopedOperations interface, which exposes a tree-shaped result set object
   Dbpropval_oo_singleton: Open Line objects directly through ICommand::Execute and IOpenRowset::OpenRowset
   Here is an instance of inserting BLOB data
   "' CPP
   Customize a
   Class Cseqstream:public ISequentialStream
   {
   Public
   Constructors
   Cseqstream ();
   Virtual ~cseqstream ();
   Public
   Virtual BOOL Seek (ULONG iPos); Offsets the current memory pointer to the specified position
   Virtual BOOL comparedata (void* pbuffer); Compare values in two segments of memory
   Virtual ULONG Length ()
   {
   return m_cbufsize;
   };
   Virtual operator void* Const ()
   {
   return m_pbuffer;
   };
   Public
   Stdmethodimp_ (ULONG) AddRef (void);
   Stdmethodimp_ (ULONG) Release (void);
   STDMETHODIMP QueryInterface (REFIID riid, lpvoid *PPV);

Read-Write memory operations, these are functions that must be implemented
STDMETHODIMP Read (
/* [out] */void __rpc_far PV,
/ [in] /ULONG CB,
/ [out] */ULONG __rpc_far *pcbread);

STDMETHODIMP Write(    /* [in] */ const void __RPC_FAR *pv,    /* [in] */ ULONG cb,    /* [out]*/ ULONG __RPC_FAR *pcbWritten);

Private
ULONG M_cref; Reference count
void* M_pbuffer; Buffer
ULONG m_cbufsize; Buffer size
ULONG M_ipos; Current index position in the buffer
};
```

//Insert data first column BLOB data    //This is because the data structure of each column is known beforehand, so a lazy approach is used, and a row of rows is insertedPData1 = HeapAlloc (GetProcessHeap (), Heap_zero_memory, Pdwdatalen[ncol]); for(inti =0; i < Pulcolcnt[ncol]; i++) {if(Dbtype_iunknown = = Ppbindings[ncol][i].wtype)            {* (dbstatus*) ((byte*) pData1 + ppbindings[ncol][i].obstatus) = DBSTATUS_S_OK; Cseqstream *pseqstream =NewCseqstream (); Getfiledata (_t ("Test.txt"), Dwfilelen, Pfiledata);            Pseqstream->write (Pfiledata, Dwfilelen, &dwwritten); Pseqstream->seek (0);//write this operation offsets the cached pointer to the end, which needs to be adjusted so that the OLE DB component reads from the cache when the BLOB data is insertedHeapFree (GetProcessHeap (),0, Pfiledata);            * (ulong*) ((byte*) pData1 + ppbindings[ncol][i].oblength) = Dwfilelen; * (isequentialstream**) ((byte*) pData1 + ppbindings[ncol][i].obvalue) = Pseqstream;//Here No release pseqstream,com components are automatically released}Else{//According to the database definition, this should be the ID* (ulong*) ((byte*) pData1 + ppbindings[ncol][i].oblength) =Ten;if(DBTYPE_WSTR = = Ppbindings[ncol][i].wtype) {StringCchCopy ((LPOLESTR) ((byte*) pData1 + ppbindings[ncol][i].obvalue),Ten, SysAllocString (OLESTR ("1")));    }}} hres = Pirowsetchange->insertrow (Db_null_hchapter, Phaccessor[ncol], pData1, &hnewrow); Com_success (hres, _t ("Inserting the 1th column BLOB data failed with the error code:%08x\n"), hres);

In the preceding code, you first define a derived class that is used to read and write BLOB data, and then demonstrates how to use it in the following code
In the later section of the code, the basic steps are the same as before, after connecting the data source, creating a Reply object, opening the table, then binding, getting the row accessor, because the code is basically the same, in order to save space, so omit them, only the most important part.
In the inserted code, you first look for the properties of the individual columns in the accessor, and if the BLOB data is inserted using BLOB data, the insertion method of the general data is used. When inserting BLOB data, first create an object of a derived class, and note that the stack memory cannot be used because of subsequent calls to the OLE DB component. We first call the class's write method to write memory to the corresponding buffer, and then call the Seek function to offset the memory pointer to the first address of the buffer, this pointer is equivalent to the file pointer, the COM component when the corresponding function is called to insert it into the database will use this memory pointer. So it must be placed at the first address. Let the object's pointer into the corresponding obvalues offset, set the corresponding data size to the size of the BLOB data, and finally as long as the normal data type to call the corresponding Update method to implement BLOB data insertion

The last two examples of the detailed code address
Example 1:blob reading of data
Example 2:blob insertion of data

OLE DB access to BLOB-type data