本人曾遇到一个非常棘手的问题，在VC中用OLE DB读写SQL Server中的BLOB字段时，尝试了几乎所有的绑定方法，读是没有问题，但总无法成功地将数据写入BLOB中的字段中。后来在SQL Server的Books OnLines中找到一些提示，经多次实验，终于成功地解决问题。在这里提供源代码供大家参考。
首先，应当从ISequentialStream派生一个类，其头文件如下（SeqStream.h）：
//SeqStream.h
#if !defined (CSEQSTREAM_H)
#define CSEQSTREAM_H
class CSeqStream : public ISequentialStream
{
public:
//Constructors
CSeqStream();
virtual ~CSeqStream();
virtual BOOL Seek(ULONG iPos);
virtual BOOL Clear();
virtual BOOL CompareData(void* pBuffer);
virtual ULONG Length() { return m_cBufSize; };
virtual operator void* const() { return m_pBuffer; };
STDMETHODIMP_(ULONG) AddRef(void);
STDMETHODIMP_(ULONG) Release(void);
STDMETHODIMP QueryInterface(REFIID riid, LPVOID *ppv);
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);
void ResetPosition();
protected:
//Data
private:
ULONG m_cRef; // reference count
void* m_pBuffer; // buffer
ULONG m_cBufSize; // buffer size
ULONG m_iPos; // current index position in the buffer
};
#endif
实现文件如下（SeqStream.cpp）：
#include "stdafx.h"
#include "SeqStream.h "
CSeqStream::CSeqStream()
{
m_iPos = 0;
m_cRef = 0;
m_pBuffer = NULL;
m_cBufSize = 0;
//The constructor AddRef's
AddRef();
}
CSeqStream::~CSeqStream()
{
//Shouldn't have any references left
// ASSERT(m_cRef == 0);
CoTaskMemFree(m_pBuffer);
}
ULONG CSeqStream::AddRef(void)
{
return ++m_cRef;
}
ULONG CSeqStream::Release(void)
{
// ASSERT(m_cRef);
if(--m_cRef)
return m_cRef;
delete this;
return 0;
}
HRESULT CSeqStream::QueryInterface(REFIID riid, void** ppv)
{
// ASSERT(ppv);
*ppv = NULL;
if (riid == IID_IUnknown)
*ppv = this;
if (riid == IID_ISequentialStream)
*ppv = this;
if(*ppv)
{
((IUnknown*)*ppv)->AddRef();
return S_OK;
}
return E_NOINTERFACE;
}
BOOL CSeqStream::Seek(ULONG iPos)
{
//Make sure the desired position is within the buffer
// ASSERT(iPos == 0 || iPos < m_cBufSize);
//Reset the current buffer position
m_iPos = iPos;
return TRUE;
}
BOOL CSeqStream::Clear()
{
//Frees the buffer
m_iPos = 0;
m_cBufSize = 0;
CoTaskMemFree(m_pBuffer);
m_pBuffer = NULL;
return TRUE;
}
BOOL CSeqStream::CompareData(void* pBuffer)
{
// ASSERT(pBuffer);
//Quick and easy way to compare user buffer with the stream
return memcmp(pBuffer, m_pBuffer, m_cBufSize)==0;
}
HRESULT CSeqStream::Read(void *pv, ULONG cb, ULONG* pcbRead)
{
//Parameter checking
if(pcbRead)
*pcbRead = 0;
if(!pv)
return STG_E_INVALIDPOINTER;
if(cb == 0)
return S_OK;
//Actual code
ULONG cBytesLeft = m_cBufSize - m_iPos;
ULONG cBytesRead = cb > cBytesLeft ? cBytesLeft : cb;
//if no more bytes to retrieve return
if(cBytesLeft == 0)
return S_FALSE;
//Copy to users buffer the number of bytes requested or remaining
memcpy(pv, (void*)((BYTE*)m_pBuffer + m_iPos), cBytesRead);
m_iPos += cBytesRead;
if(pcbRead)
*pcbRead = cBytesRead;
if(cb != cBytesRead)
return S_FALSE;
return S_OK;
}
HRESULT CSeqStream::Write(const void *pv, ULONG cb, ULONG* pcbWritten)
{
//Parameter checking
if(!pv)
return STG_E_INVALIDPOINTER;
if(pcbWritten)
*pcbWritten = 0;
if(cb == 0)
return S_OK;
//Enlarge the current buffer
m_cBufSize += cb;
//Need to append to the end of the stream
m_pBuffer = CoTaskMemRealloc(m_pBuffer, m_cBufSize);
memcpy((void*)((BYTE*)m_pBuffer + m_iPos), pv, cb);
m_iPos += cb;
if(pcbWritten)
*pcbWritten = cb;
return S_OK;
}
void CSeqStream::ResetPosition()
{
m_iPos=0;
}
设要从一个文件读数据写入到数据库中的一个BLOB字段，在SQL Server中的Table名为tMaterials，它的Key为MaterialID,BLOB字段名为Stream。 写入BLOB字段数据时可用如下的方法：
BOOL SetBLOBData(WCHAR *awcname,CString strFile)
{
DBOBJECT ObjectStruct;
ObjectStruct.dwFlags = STGM_READ;
ObjectStruct.iid = IID_ISequentialStream;
struct BLOBDATA
{
DBSTATUS dwStatus;
DWORD dwLength;
ISequentialStream* pISeqStream;
};
BLOBDATA BLOBGetData;
BLOBDATA BLOBSetData;
const ULONG cBindings = 1;
DBBINDING rgBindings[cBindings];
HRESULT hr = S_OK;
IAccessor* pIAccessor = NULL;
ICommandProperties* pICommandProperties = NULL;
IRowsetChange* pIRowsetChange = NULL;
IRowset* pIRowset = NULL;
CSeqStream* pMySeqStream = NULL;
ULONG cRowsObtained = 0;
HACCESSOR hAccessor = DB_NULL_HACCESSOR;
DBBINDSTATUS rgBindStatus[cBindings];
HROW* rghRows = NULL;
const ULONG cPropSets = 1;
DBPROPSET rgPropSets[cPropSets];
const ULONG cProperties = 1;
DBPROP rgProperties[cProperties];
rgPropSets[0].guidPropertySet = DBPROPSET_ROWSET;
rgPropSets[0].cProperties = cProperties;
rgPropSets[0].rgProperties = rgProperties;
rgPropSets[0].rgProperties[0].dwPropertyID = DBPROP_UPDATABILITY;
rgPropSets[0].rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgPropSets[0].rgProperties[0].dwStatus = DBPROPSTATUS_OK;
rgPropSets[0].rgProperties[0].colid = DB_NULLID;
rgPropSets[0].rgProperties[0].vValue.vt = VT_I4;
V_I4(&rgPropSets[0].rgProperties[0].vValue) = DBPROPVAL_UP_CHANGE;
//设置Rowset属性
hr = g_pICommandText->QueryInterface(IID_ICommandProperties,
(void **)&pICommandProperties);
if (FAILED(hr))
{
TRACE0("Failed to get ICommandProperties to set rowset properties.\n");
return FALSE;
}
hr = pICommandProperties->SetProperties(cPropSets, rgPropSets);
if (FAILED(hr))
{
TRACE0("Execute failed to set rowset properties.\n");
return FALSE;
}
//执行命令
CString strSql;
strSql.Format("SELECT Stream FROM tMaterials WHERE MaterialID = %s ",gToQuote(awcname));
int nlen=strSql.GetLength();
wchar_t *pSql=(wchar_t*)malloc(nlen*sizeof(wchar_t));
mbstowcs(pSql,strSql.GetBuffer(MAX_PATH),nlen);
if (FAILED(hr = g_pICommandText->SetCommandText(DBGUID_DBSQL,
pSql)))
{
TRACE0("failed");
return FALSE;
}
hr = g_pICommandText->Execute(NULL, IID_IRowsetChange, NULL, NULL,
(IUnknown**)&pIRowsetChange);
if (FAILED(hr))
{
TRACE0("Failed to execute the command\n");
return FALSE;
}
rgBindings[0].iOrdinal = 1; //你的BLOB字段的位置（从1开始），改为你所需要的
rgBindings[0].obValue = offsetof(BLOBDATA, pISeqStream);
rgBindings[0].obLength = offsetof(BLOBDATA, dwLength);
rgBindings[0].obStatus = offsetof(BLOBDATA, dwStatus);
rgBindings[0].pTypeInfo = NULL;
rgBindings[0].pObject = &ObjectStruct;
rgBindings[0].pBindExt = NULL;
rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[0].eParamIO = DBPARAMIO_NOTPARAM;
rgBindings[0].cbMaxLen = 0;
rgBindings[0].dwFlags = 0;
rgBindings[0].wType = DBTYPE_IUNKNOWN;
rgBindings[0].bPrecision = 0;
rgBindings[0].bScale = 0;
hr = pIRowsetChange->QueryInterface(IID_IAccessor,
(void**)&pIAccessor);
if (FAILED(hr))
{
TRACE0("Failed to get IAccessor interface.\n");
return FALSE;
}
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
cBindings,
rgBindings,
sizeof(BLOBDATA),
&hAccessor,
rgBindStatus);
if (FAILED(hr))
{
TRACE0("Failed to create an accessor.\n");
return FALSE;
}
hr = pIRowsetChange->QueryInterface(IID_IRowset,
(void **)&pIRowset);
if (FAILED(hr))
{
TRACE0("Failed to get IRowset interface.\n");
return FALSE;
}
hr = pIRowset->GetNextRows(NULL,0, 1,&cRowsObtained,&rghRows);
hr = pIRowset->GetData(rghRows[0],
hAccessor,
&BLOBGetData);
if (BLOBGetData.dwStatus == DBSTATUS_S_ISNULL)
//在数据库的当前字段为NULL
TRACE0("Provider returned a null value.\n");
else if(BLOBGetData.dwStatus == DBSTATUS_S_OK)
{
//在这里，从服务端为你分配的ISequentialStream接口读入BLOB数据
//BLOBGetData.pISeqStream->Read(pBuffer,cBytes,&cBytesRead);
//无论你是否有读数据，
SAFE_RELEASE(BLOBGetData.pISeqStream);
}
//生成一个新的Stream.
pMySeqStream = new CSeqStream();
//开始从文件中读数据写入数据库
CFile fle;
if (fle.Open(strFile,CFile::modeRead))
{
const ULONG cBytes = 4096;
BYTE pWriteData[cBytes]; //从这个数组写入BLOB数据
memset(pWriteData, '\0', cBytes);
UINT nRead=0;
nRead=fle.Read(pWriteData,cBytes);
while (nRead>0)
{
pMySeqStream->Write(pWriteData,nRead,NULL);
nRead=fle.Read(pWriteData,cBytes);
}
fle.Close();
//一定要执行此句，重设Stream当前位置到它的最开始处
pMySeqStream->ResetPosition();
BLOBSetData.pISeqStream = (ISequentialStream*)pMySeqStream;
BLOBSetData.dwStatus = DBSTATUS_S_OK;
BLOBSetData.dwLength = pMySeqStream->Length();
//将BLOB数据写入到数据库.
hr = pIRowsetChange->SetData(rghRows[0],
hAccessor,
&BLOBSetData);
if (FAILED(hr))
{
TRACE0("Failed to set data.\n");
return FALSE;
}
}
else
{
AfxMessageBox(strFile+"不能打开文件！",MB_OK | MB_ICONEXCLAMATION);
return FALSE;
}
hr = pIAccessor->ReleaseAccessor(hAccessor, NULL);
if (FAILED(hr))
{
TRACE0("Failed to release accessor.\n");
return TRUE;
}
hr = pIRowset->ReleaseRows(cRowsObtained,
rghRows,
NULL,
NULL,
NULL);
if (FAILED(hr))
TRACE0("Failed to release rows.\n");
return TRUE;
}
你可以直接Copy以下代码，只需改动一下SQL语句即可。
