在VC中用OLE DB读写SQL Server中的BLOB字段 (转)

在VC中用OLE DB读写SQL Server中的BLOB字段 (转)[@more@]  本人曾遇到一个非常棘手的问题，在VC中用OLE db读写 sql SERVER中的BLOB字段时，尝试了几乎所有的绑定方法，读是没有问题，但总无法成功地将数据写入BLOB中的字段中。后来在 SQL SERVER的Books OnLines中找到一些提示，经多次实验，终于成功地解决问题。在这里提供 源代码供大家参考。

首先，应当从ISequentialStream派生一个类，其头文件如下（SeqStream.h）：

//SeqStream.h

#if !defined (CSEQSTREAM_H)

#define CSEQSTREAM_H

 XML:namespace prefix = o ns = "urn:schemas-microsoft-com:Office: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(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 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(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, '', 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语句即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10752043/viewspace-988409/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10752043/viewspace-988409/