设置大型数据 (OLE DB)
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)
此示例显示如何设置 BLOB 数据、创建表、添加示例记录、从行集中提取该记录，然后设置该 BLOB 字段的值。 IA64 平台不支持此示例。
为了将指针传递给它自己的存储对象，使用者创建一个绑定 BLOB 列的值的取值函数，然后调用 IRowsetChange::SetData 或 IRowsetChange::InsertRow 方法 。
本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。
重要
请尽可能使用 Windows 身份验证。 如果 Windows 身份验证不可用，请在运行时提示用户输入其凭据。 不要将凭据存储在一个文件中。 如果必须保存凭据，应当用 Win32 crypto API（Win32 加密 API）加密它们。
过程
设置 BLOB 数据
创建一个描述应如何访问 BLOB 列的 DBOBJECT 结构。 将 DBOBJECT 结构的 dwFlag 元素设置为 STGM_READ，并将 iid 元素设置为 IID_ISequentialStream（要公开的接口） 。
设置 DBPROPSET_ROWSET 属性组中的属性，以使行集可更新。
通过使用 DBBINDING 结构数组创建一组绑定（每列一个）。 将 DBBINDING 结构中的 wType 元素设置为 DBTYPE_IUNKNOWN，并将 pObject 元素设置为指向创建的 DBOBJECT 结构 。
使用 DBBINDINGS 结构数组中的绑定信息创建取值函数。
调用 GetNextRows 将后续的行提取到行集中 。 调用 GetData 以读取行集中的数据 。
若要设置数据，创建一个存储对象（其中包含数据以及长度指示器），然后通过绑定 BLOB 列的取值函数调用 IRowsetChange::SetData（或 IRowsetChange::InsertRow） 。
示例
说明
使用 ole32.lib 和 oleaut32.lib 编译并执行以下 C++ 代码列表。 此应用程序连接到您的计算机上默认的 SQL Server 实例。 在某些 Windows 操作系统上，您需要将 (localhost) 或 (local) 更改为您的 SQL Server 实例的名称。 要连接到命名实例，请将连接字符串从 L"(local)" 更改为 L"(local)\\name"，其中 name 是命名实例。 默认情况下，SQL Server Express 安装在命名实例中。 请确保 INCLUDE 环境变量包括含有 msoledbsql.h 的目录。
代码
// compile with: ole32.lib oleaut32.lib
#define UNICODE
#define DBINITCONSTANTS
#define INITGID
#define OLEDBVER 0x0250 // to include correct interfaces
#include <windows.h>
#include <stdio.h>
#include <stddef.h>
#include <iostream>
#include <oledb.h>
#include <oledberr.h>
#include <msoledbsql.h>
using namespace std;
#define SAFE_RELEASE(pIUnknown) if(pIUnknown) (pIUnknown)->Release();
HRESULT GetCommandObject(REFIID riid, IUnknown** ppIUnknown);
HRESULT CreateTable(ICommandText* pICommandText);
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);
private:
ULONG m_cRef; // reference count
void* m_pBuffer; // buffer
ULONG m_cBufSize; // buffer size
ULONG m_iPos; // current index position in the buffer
};
// class implementation
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() {
return ++m_cRef;
}
ULONG CSeqStream::Release() {
// 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;
}
int main() {
CoInitialize(NULL);
DBOBJECT ObjectStruct;
ObjectStruct.dwFlags = STGM_READ;
ObjectStruct.iid = IID_ISequentialStream;
struct BLOBDATA {
DBSTATUS dwStatus;
DBLENGTH dwLength;
ISequentialStream* pISeqStream;
};
BLOBDATA BLOBGetData;
BLOBDATA BLOBSetData;
const ULONG cBindings = 1;
DBBINDING rgBindings[cBindings];
HRESULT hr = S_OK;
IAccessor* pIAccessor = NULL;
ICommandText* pICommandText = NULL;
ICommandProperties* pICommandProperties = NULL;
IRowsetChange* pIRowsetChange = NULL;
IRowset* pIRowset = NULL;
CSeqStream* pMySeqStream = NULL;
DBCOUNTITEM 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];
const ULONG cBytes = 10;
BYTE pBuffer[cBytes];
ULONG cBytesRead = 0;
BYTE pReadData[cBytes]; // read BLOB data in this array
memset(pReadData, 0xAA, cBytes);
BYTE pWriteData[cBytes]; // write BLOB data from this array
memset(pWriteData, 'D', cBytes);
// Get Command object
hr = GetCommandObject(IID_ICommandText, (IUnknown**)&pICommandText);
if (FAILED(hr)) {
printf("Failed to get ICommandText interface.\n");
// Release any references and return.
goto Exit;
}
// Create table with image column and index
hr = CreateTable(pICommandText);
if (FAILED(hr)) {
printf("Failed to create table.\n");
// Release any references and return.
goto Exit;
}
// Set the DBPROPSET structure. It is used to pass an array
// of DBPROP structures to SetProperties().
rgPropSets[0].guidPropertySet = DBPROPSET_ROWSET;
rgPropSets[0].cProperties = cProperties;
rgPropSets[0].rgProperties = rgProperties;
// Now set properties in the property group (DBPROPSET_ROWSET)
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;
// Set the rowset properties
hr = pICommandText->QueryInterface(IID_ICommandProperties,
(void **)&pICommandProperties);
if (FAILED(hr)) {
printf("Failed to get ICommandProperties to set rowset properties.\n");
// Release any references and return.
goto Exit;
}
hr = pICommandProperties->SetProperties(cPropSets, rgPropSets);
if (FAILED(hr)) {
printf("Execute failed to set rowset properties.\n");
// Release any references and return.
goto Exit;
}
// Execute a command (SELECT * FROM TestISeqStream)
hr = pICommandText->SetCommandText(DBGUID_DBSQL, L"SELECT * FROM TestISeqStream");
if (FAILED(hr)) {
printf("Failed to set command text SELECT * FROM.\n");
// Release any references and return.
goto Exit;
}
hr = pICommandText->Execute(NULL,
IID_IRowsetChange,
NULL,
NULL,
(IUnknown**)&pIRowsetChange);
if (FAILED(hr)) {
printf("Failed to execute the command SELECT * FROM.\n");
// Release any references and return.
goto Exit;
}
// Fill the DBBINDINGS array.
rgBindings[0].iOrdinal = 2; // ordinal position
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;
// Create an accessor using the binding information.
hr = pIRowsetChange->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
if (FAILED(hr)) {
printf("Failed to get IAccessor interface.\n");
// Release any references and return.
goto Exit;
}
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
cBindings,
rgBindings,
sizeof(BLOBDATA),
&hAccessor,
rgBindStatus);
if (FAILED(hr)) {
printf("Failed to create an accessor.\n");
// Release any references and return.
goto Exit;
}
// get the first row.
hr = pIRowsetChange->QueryInterface(IID_IRowset, (void **)&pIRowset);
if (FAILED(hr)) {
printf("Failed to get IRowset interface.\n");
// Release any references and return.
goto Exit;
}
hr = pIRowset->GetNextRows(NULL, 0, 1, &cRowsObtained, &rghRows);
hr = pIRowset->GetData(rghRows[0], hAccessor, &BLOBGetData);
// Verify the retrieved data, only if data is not null.
if (BLOBGetData.dwStatus == DBSTATUS_S_ISNULL) {
// Process null data
printf("Provider returned a null value.\n");
}
else if(BLOBGetData.dwStatus == DBSTATUS_S_OK) {
// Provider returned a nonNULL value
BLOBGetData.pISeqStream->Read( pBuffer, cBytes, &cBytesRead);
if (memcmp(pBuffer, pReadData, cBytes) != 0) {
// cleanup
}
SAFE_RELEASE(BLOBGetData.pISeqStream);
}
// Set up data for SetData.
pMySeqStream = new CSeqStream();
// Put data into ISequentialStream object for the provider to write.
pMySeqStream->Write(pWriteData, cBytes, NULL);
BLOBSetData.pISeqStream = (ISequentialStream*)pMySeqStream;
BLOBSetData.dwStatus = DBSTATUS_S_OK;
BLOBSetData.dwLength = pMySeqStream->Length();
// Set the data.
hr = pIRowsetChange->SetData(rghRows[0], hAccessor, &BLOBSetData);
if (FAILED(hr)) {
printf("Failed to set data.\n");
// Release any references and return.
goto Exit;
}
hr = pIAccessor->ReleaseAccessor(hAccessor, NULL);
if (FAILED(hr)) {
printf("Failed to release accessor.\n");
// Release any references and return.
goto Exit;
}
hr = pIRowset->ReleaseRows(cRowsObtained, rghRows, NULL, NULL, NULL);
if (FAILED(hr)) {
printf("Failed to release rows.\n");
// Release any references and return.
goto Exit;
}
Exit:
// Release all allocated memory and release interface pointers.
CoUninitialize();
}
HRESULT GetCommandObject(REFIID riid, IUnknown** ppIUnknown) {
HRESULT hr = S_OK;
// Local interface pointers, until a connection is made.
IDBInitialize* pIDBInitialize = NULL;
IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
const ULONG cPropSets = 1;
DBPROPSET rgPropSets[cPropSets];
const ULONG cProperties = 4;
DBPROP rgProperties[cProperties];
// Initialize property values needed to establish connection.
for ( ULONG i = 0 ; i < 4 ; i++ )
VariantInit(&rgProperties[i].vValue);
// Server name.
rgProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties[0].vValue.vt = VT_BSTR;
rgProperties[0].vValue.bstrVal = SysAllocString(L"(local)");
rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[0].colid = DB_NULLID;
// Database.
rgProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
rgProperties[1].vValue.vt = VT_BSTR;
rgProperties[1].vValue.bstrVal = SysAllocString(L"AdventureWorks");
rgProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[1].colid = DB_NULLID;
rgProperties[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
rgProperties[2].vValue.vt = VT_BSTR;
rgProperties[2].vValue.bstrVal = SysAllocString(L"SSPI");
rgProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[2].colid = DB_NULLID;
// Properties are now set. Next, construct DBPROPSET structure (rgInitPropSet) used to pass an
// array of DBPROP structures (InitProperties) to the SetProperties method.
rgPropSets[0].guidPropertySet = DBPROPSET_DBINIT;
rgPropSets[0].cProperties = cProperties;
rgPropSets[0].rgProperties = rgProperties;
// Get the IDBInitialize interface.
hr = CoCreateInstance(CLSID_MSOLEDBSQL,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void**)&pIDBInitialize);
if (FAILED(hr)) {
printf("Failed to get IDBInitialize interface.\n");
// Release any references and return.
goto Exit;
}
// Set initialization properties.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if (FAILED(hr)) {
printf("Failed to get IDBProperties interface.\n");
// Release any references and return.
goto Exit;
}
hr = pIDBProperties->SetProperties(cPropSets, rgPropSets);
if (FAILED(hr)) {
printf("Failed to set properties for DBPROPSET_DBINIT.\n");
// Release any references and return.
goto Exit;
}
hr = pIDBInitialize->Initialize();
if (FAILED(hr)) {
printf("Failed to initialize.\n");
// Release any references and return.
goto Exit;
}
// Create a session object.
hr = pIDBInitialize->QueryInterface( IID_IDBCreateSession,
(void **)&pIDBCreateSession);
if (FAILED(hr)) {
printf("Failed to get pIDBCreateSession interface.\n");
// Release any references and return.
goto Exit;
}
hr = pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, (IUnknown**)&pIDBCreateCommand);
if (FAILED(hr)) {
printf("Failed to create session object.\n");
// Release any references and return.
goto Exit;
}
// Get CommandText object
hr = pIDBCreateCommand->CreateCommand( NULL, riid, (IUnknown**)ppIUnknown);
if (FAILED(hr)) {
printf("Failed to create CommandText object.\n");
// Release any references and return.
goto Exit;
}
return hr;
Exit:
// Free up all allocated memory and release interface pointers.
return hr;
}
HRESULT CreateTable(ICommandText* pICommandText) {
HRESULT hr = S_OK;
// drop the table "TestISeqStream" if the table exists.
hr = pICommandText->SetCommandText( DBGUID_DBSQL, L"if object_id('TestISeqStream') is not null drop table TestISeqStream");
if (FAILED(hr)) {
printf("Failed to set command text DROP TABLE.\n");
// Release any references and return.
goto Exit;
}
hr = pICommandText->Execute(NULL, IID_NULL, NULL, NULL, NULL);
if (FAILED(hr)) {
printf("Failed to drop the table.\n");
// Release any references and return.
goto Exit;
}
// Create a new table.
hr = pICommandText->SetCommandText(DBGUID_DBSQL,
L"CREATE TABLE TestISeqStream (col1 int,col2 image)");
if (FAILED(hr)) {
printf("Failed to set command text CREATE TABLE.\n");
// Release any references and return.
goto Exit;
}
hr = pICommandText->Execute(NULL, IID_NULL, NULL, NULL, NULL);
if (FAILED(hr)) {
printf("Failed to create table.\n");
// Release any references and return.
goto Exit;
}
// Insert one row into table.
hr = pICommandText->SetCommandText(DBGUID_DBSQL,
L"INSERT INTO TestISeqStream(col1,col2) VALUES (1,0xAAAAAAAAAAAAAAAAA)");
if (FAILED(hr)) {
printf("Failed to set command text INSERT INTO.\n");
// Release any references and return.
goto Exit;
}
hr = pICommandText->Execute(NULL, IID_NULL, NULL, NULL, NULL);
if (FAILED(hr)) {
printf("Failed to insert record in the table.\n");
// Release any references and return.
goto Exit;
}
Exit:
// Release all allocated memory and release interface pointers.
return hr;
}