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

减小字体 增大字体 作者:佚名  来源:本站整理  发布时间:2009-03-16 14:00:23
    本人曾遇到一个非常棘手的问题,在VC中用OLE DB读写SQL Server中的BLOB字段时,尝试了几乎所有的绑定方法,读是没有问题,但总无法成功地将数据写入BLOB中的字段中。后来在SQL ServerBooks 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,它的KeyMaterialID,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. ");

        return FALSE;

    }

    hr = pICommandProperties->SetProperties(cPropSets, rgPropSets);

    if (FAILED(hr))

    {

        TRACE0("Execute failed to set rowset properties. ");

        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 ");

        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. ");

        return FALSE;

    }

 

    hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,

                                    cBindings,

                                    rgBindings,

                                    sizeof(BLOBDATA),

                                    &hAccessor,

                                    rgBindStatus);

    if (FAILED(hr))

    {

        TRACE0("Failed to create an accessor. ");

        return FALSE;

    }

 

    hr = pIRowsetChange->QueryInterface(IID_IRowset,

                                        (void **)&pIRowset);

    if (FAILED(hr))

    {

        TRACE0("Failed to get IRowset interface. ");

        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. ");

        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();

&

Tags:

作者:佚名
分享到: 微信 更多