Steve Hale
SQL Server Native Client Development Team
Microsoft Corporation
Recently there have been several requests for samples showing how to use IRowsetFastLoad to send varying BLOB data per row or how to stream data to SQL Server via SQLOLEDB or SQLNCLI. Most of the available samples are too simplistic and don’t demonstrate varying data length per row or using ISequentialStream.
In this sample, you'll see both techniques in one source file. By default, the sample shows how to use IRowsetFastLoad to send variable length BLOB data per row using in-line bindings. In this case, the in-line BLOB data must fit in available memory. This method has better performance when the BLOB data is a few bytes to a few MB, because there is no additional stream overhead. For larger data, especially where the data is not necessarily all available at once in a block, streaming is a better choice.
When you uncomment #define USE_ISEQSTREAM, the sample will use ISequentialStream. The stream implementation is defined in the sample, and can send any size BLOB data simply by changing the MAX_BLOB size. In this case, the stream data does not have to fit in available memory or be available in one block. The provider is called using IRowsetFastLoad::InsertRow, passing a pointer to the stream implementation in the data buffer (rgBinding.obValue offset) along with the amount of data available to read from the stream. Some providers may not require the length of the data to be known when binding occurs, and in that case the length may be omitted from the binding.
Note that the sample does not write data to the provider using the provider’s stream interface. Rather, the sample passes a pointer to the stream object that the provider will consume to read the data. Typically, SQLOLEDB and SQLNCLI will read data in 1024 byte chunks from our object until all the data has been processed. Neither SQLOLEDB nor SQLNCLI have full implementations for allowing the consumer to write data to the provider’s stream object. Only zero length data can be sent via the provider’s stream object.
The consumer-implemented ISequentialStream object can be used with rowset data (IRowsetChange::InsertRow, IRowsetChange::SetData) and with parameters by binding a parameter as DBTYPE_IUNKNOWN.
Since DBTYPE_IUNKNOWN is specified as the data type in the binding, it has to match the type of the column or parameter being targeted. There are no conversions possible when sending data via ISequentialStream from rowset interfaces. For parameters, you should avoid using ICommandWithParameters::SetParameterInfo and specify a different type to force a conversion. This is because it would require the provider to cache all the BLOB data locally in order to convert it prior to sending it to SQL Server. Caching a large BLOB and converting it locally does not give good performance.
Recommended reading:
http://msdn2.microsoft.com/en-us/library/ms131277.aspx (SQL Server 2007)
http://technet.microsoft.com/en-us/library/aa198331(SQL.80).aspx (SQL Server 2000)
Code Sample
You can compile this sample at the command line by including the following libraries:
You will also need to change MyServer in the connection string to the name of a server at your location.
You'll also need to run the following Transact SQL before you execute the program:
create table fltest(col1 int, col2 int, col3 image)
You can see the results of the sample by running this Transact SQL:
select top 50 * from fltest
//
// PROGRAM: FastLoadTest
// (C) Microsoft Corporation. All rights reserved
//
// PURPOSE: Insert variable size BLOB data into different rows with IRowsetFastLoad.
// Demonstrate sending BLOB data with stream binding (ISequentialStream)
//
// REQUIREMENTS: create table fltest(col1 int, col2 int, col3 image)
// VERIFICATION: select top 50 * from fltest
//
// #define USE_ISEQSTREAM
#include <windows.h>
#define DBINITCONSTANTS // Must be defined to initialize constants in oledb.h
#define INITGUID
#include <sqloledb.h>
#include <oledb.h>
#include <msdasc.h>
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>
#define MAX_BLOB 200 // For stream binding this can be any size, but for inline it must fit in memory
#define MAX_ROWS 100
#define SAFE_RELEASE(p) if (p) {(p)->Release();(p)=NULL;}
#define DROP_TABLE L"drop table fltest"
#define CREATE_TABLE L"create table fltest(col1 int, col2 int, col3 image)"
void usage(void);
#ifdef USE_ISEQSTREAM
// ISequentialStream implementation for streaming data
class MySequentialStream : public ISequentialStream {
private:
ULONG m_ulRefCount;
ULONG m_ulBufSize;
ULONG m_ulReadSize;
ULONG m_ulBytesLeft;
ULONG m_ulReadPos;
BYTE * m_pSrcData;
BYTE * m_pReadPtr;
BOOL m_fWasRead;
public:
MySequentialStream(void)
{
m_ulRefCount = 1;
m_ulBufSize = 0;
m_ulReadSize = 0;
m_ulBytesLeft = 0;
m_ulReadPos = 0;
m_pSrcData = NULL;
m_pReadPtr = NULL;
m_fWasRead = FALSE;
}
~MySequentialStream(void)
{
}
virtual ULONG STDMETHODCALLTYPE AddRef(void)
{
return ++m_ulRefCount;
}
virtual ULONG STDMETHODCALLTYPE Release(void)
{
--m_ulRefCount;
if (m_ulRefCount == 0)
{
delete this;
return 0;
}
return m_ulRefCount;
}
virtual HRESULT STDMETHODCALLTYPE QueryInterface(REFIID riid, void ** ppvObj)
{
// HRESULT hr = E_FAIL;
if (!ppvObj)
return E_INVALIDARG;
else
*ppvObj = NULL;
if (riid != IID_ISequentialStream && riid != IID_IUnknown)
return E_NOINTERFACE;
AddRef();
*ppvObj = this;
return S_OK;
}
HRESULT Init(const void * pSrcData, const ULONG ulBufSize, const ULONG ulReadSize)
{
// Must have a source
if (NULL == pSrcData)
return E_INVALIDARG;
// Data length must be non-zero
if (0 == ulBufSize)
return E_INVALIDARG;
m_ulBufSize = ulBufSize;
m_ulReadSize = ulReadSize;
m_pSrcData = (BYTE *)pSrcData;
m_pReadPtr = m_pSrcData;
m_ulBytesLeft = m_ulReadSize;
m_ulReadPos = 0;
m_fWasRead = FALSE;
return S_OK;
}
// SQL Server providers (SQLOLEDB/SQLNCLI) don't allow us to write our data to them. Instead,
// they read from our object.
virtual HRESULT STDMETHODCALLTYPE Write(const void *, ULONG, ULONG * )
{
return E_NOTIMPL;
}
// This implementation simply copies data from the source buffer in whatever size requested.
// But you can do anything here such as reading from a file, reading from a different rowset, stream, etc.
virtual HRESULT STDMETHODCALLTYPE Read(void * pv, ULONG cb, ULONG * pcbRead)
{
ULONG ulBytesWritten = 0;
ULONG ulCBToWrite = cb;
ULONG ulCBToCopy;
BYTE * pvb = (BYTE *)pv;
m_fWasRead = TRUE;
if (NULL == m_pSrcData)
return E_FAIL;
if (NULL == pv)
return STG_E_INVALIDPOINTER;
while (ulBytesWritten < ulCBToWrite && m_ulBytesLeft)
{
// Make sure we don't write more than our max read size or the size they asked for
ulCBToCopy = min(m_ulBytesLeft, cb);
// Make sure we don't read past the end of the internal buffer
ulCBToCopy = min(m_ulBufSize - m_ulReadPos, ulCBToCopy);
memcpy(pvb, m_pReadPtr + m_ulReadPos, ulCBToCopy);
pvb += ulCBToCopy;
ulBytesWritten += ulCBToCopy;
m_ulBytesLeft -= ulCBToCopy;
cb -= ulCBToCopy;
// Wrap reads around the src buffer
m_ulReadPos += ulCBToCopy;
if (m_ulReadPos >= m_ulBufSize)
m_ulReadPos = 0;
}
if (pcbRead)
*pcbRead = ulBytesWritten;
return S_OK;
}
};
#endif // USE_ISEQSTREAM
HRESULT SetFastLoadProperty(IDBInitialize * pIDBInitialize)
{
HRESULT hr = S_OK;
IDBProperties * pIDBProps = NULL;
DBPROP rgProps[1];
DBPROPSET PropSet;
VariantInit(&rgProps[0].vValue);
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
&nb