在VC中用OLE DB讀寫SQL Server中的BLOB欄位 (轉)[@more@] 本人曾遇到一個非常棘手的問題，在VC中用OLE 讀寫 SERVER中的BLOB欄位時，嘗試了幾乎所有的繫結方法，讀是沒有問題，但總無法成功地將資料寫入BLOB中的欄位中。後來在的Books OnLines中找到一些提示，經多次實驗，終於成功地解決問題。在這裡提供供大家參考。

首先，應當從ISequentialStream派生一個類，其頭如下（SeqStream.h）：

//SeqStream.h

#if !defined (CSEQSTREAM_H)

#define CSEQSTREAM_H

:namespace prefix = o ns = "urn:schemas--com::office" />

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(iP== 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)

{

DB ObjectStruct;

ObjectStruct.dwFlags = STGM_READ;

ObjectStruct.iid = IID_ISequentialStream;

struct BLOBDATA

{

DBSTATUS dwStatus;

D 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(" 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 commandn");

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(CCESSOR_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("Provr 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, '', 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);

if (FAILED(hr))

TRACE0("Failed to release rows.n");

return TRUE;

}

你可以直接Copy以下程式碼，只需改動一下SQL語句即可。

在VC中用OLE DB讀寫SQL Server中的BLOB欄位 (轉)

相關文章