MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server
The consumer notifies SQLOLEDB of its need for bulk copying by setting the SQLOLEDB provider-specific property SSPROP_ENABLEFASTLOAD to VARIANT_TRUE. With the property set on the data source, the consumer creates a SQLOLEDB session. The new session allows the consumer access to IRowsetFastLoad.
To bulk copy data into a SQL Server table
The following example illustrates the use of IRowsetFastLoad for bulk copying of the records into a table. In this example, 10 records will be added to the table IRFLTable. You need to create the table IRFLTable in the database.
CREATE TABLE IRFLTable (col_vchar varchar(30))
#define DBINITCONSTANTS
#include <oledb.h>
#include <oledberr.h>
#include <stdio.h>
#include <stddef.h> // For offset of
#include <sqloledb.h>
/* @type UWORD | 2 byte unsigned integer. */
typedef unsigned short UWORD;
/* @type SDWORD | 4 byte signed integer. */
typedef signed long SDWORD;
WCHAR g_wszTable[] = L"IRFLTable";
WCHAR g_strTestLOC[100] = L"server ";
WCHAR g_strTestDSN[] = L"database";
WCHAR g_strTestUID[] = L"login";
WCHAR g_strTestPWD[] = L"password";
const UWORD g_cOPTION = 4;
const UWORD MAXPROPERTIES = 5;
const ULONG DEFAULT_CBMAXLENGTH = 20;
IMalloc* g_pIMalloc = NULL;
IDBInitialize* g_pIDBInitialize = NULL;
/*
Given an ICommand pointer, properties, and query, a
rowset pointer is returned.
*/
HRESULT CreateSessionCommand
(
DBPROPSET* rgPropertySets,
ULONG ulcPropCount,
CLSID clsidProv
);
// Use to set properties and execute a given query.
HRESULT ExecuteQuery
(
IDBCreateCommand* pIDBCreateCommand,
WCHAR* pwszQuery,
DBPROPSET* rgPropertySets,
ULONG ulcPropCount,
LONG* pcRowsAffected,
IRowset** ppIRowset,
BOOL fSuccessOnly = TRUE
);
// Use to set up options for call to IDBInitialize::Initialize.
void SetupOption
(
DBPROPID PropID,
WCHAR* wszVal,
DBPROP* pDBProp
);
// Sets fast-load property on/off for session.
HRESULT SetFastLoadProperty(BOOL fSet);
// IRowsetFastLoad inserting data.
HRESULT FastLoadData(void);
// How to lay out each column in memory.
struct COLUMNDATA
{
SDWORD dwLength; // Length of data (not space allocated).
DWORD dwStatus; // Status of column.
BYTE bData[1]; // Store data here as a variant.
};
#define COLUMN_ALIGNVAL 8
#define ROUND_UP(Size, Amount)(((DWORD)(Size) + ((Amount)-1)) &
~((Amount)-1))
int main()
{
HRESULT hr = NOERROR;
HRESULT hr2 = NOERROR;
BOOL fResults = FALSE;
// OLE initialized?
BOOL fInitialized = FALSE;
// One property set for initializing.
DBPROPSET rgPropertySets[1];
// Properties within above property set.
DBPROP rgDBProperties[g_cOPTION];
// Property count.
ULONG ulPropCount = 0;
// Number of initialization properties.
ULONG cOptions = g_cOPTION;
IDBCreateCommand* pIDBCreateCommand = NULL;
IRowset* pIRowset = NULL;
DBPROPSET* rgProperties = NULL;
IAccessor* pIAccessor = NULL;
// Basic initialization.
if(FAILED(CoInitialize(NULL)))
goto cleanup;
else
fInitialized = TRUE;
hr = CoGetMalloc(MEMCTX_TASK, &g_pIMalloc);
if((!g_pIMalloc) || FAILED(hr))
{
goto cleanup;
}
/*
Set up property set for call to IDBInitialize
in CreateSessionCommand.
*/
rgPropertySets[0].rgProperties = rgDBProperties;
rgPropertySets[0].cProperties = g_cOPTION;
rgPropertySets[0].guidPropertySet = DBPROPSET_DBINIT;
SetupOption(DBPROP_INIT_CATALOG, g_strTestDSN, &rgDBProperties[0]);
SetupOption(DBPROP_AUTH_USERID, g_strTestUID, &rgDBProperties[1]);
SetupOption(DBPROP_AUTH_PASSWORD, g_strTestPWD, &rgDBProperties[2]);
SetupOption(DBPROP_INIT_DATASOURCE, g_strTestLOC, &rgDBProperties[3]);
if(!SUCCEEDED(hr=CreateSessionCommand(rgPropertySets, 1,
CLSID_SQLOLEDB)))
goto cleanup;
// Get IRowsetFastLoad and insert data into IRFLTable.
if(FAILED(hr = FastLoadData()))
goto cleanup;
cleanup:
// Free memory.
if(rgProperties && rgProperties->rgProperties)
delete [](rgProperties->rgProperties);
if(rgProperties)
delete []rgProperties;
if(pIDBCreateCommand)
pIDBCreateCommand->Release();
if(pIAccessor)
pIAccessor->Release();
if(pIRowset)
pIRowset->Release();
if(g_pIMalloc)
g_pIMalloc->Release();
if(g_pIDBInitialize)
{
hr2 = g_pIDBInitialize->Uninitialize();
if(FAILED(hr2))
printf("Uninitialize failed\n");
}
if(fInitialized)
CoUninitialize();
if(SUCCEEDED(hr))
printf("Test completed successfully.\n\n");
else
printf("Test failed.\n\n");
return(0);
}
//--------------------------------------------------------------
HRESULT FastLoadData(void)
{
HRESULT hr = E_FAIL;
HRESULT hr2 = E_FAIL;
DBID TableID;
IDBCreateSession* pIDBCreateSession = NULL;
IOpenRowset* pIOpenRowsetFL = NULL;
IRowsetFastLoad* pIFastLoad = NULL;
IAccessor* pIAccessor = NULL;
HACCESSOR hAccessor = 0;
DBBINDSTATUS oneStatus = 0;
DBBINDING oneBinding;
ULONG ulOffset = 0;
TableID.uName.pwszName = NULL;
LONG i = 0;
void* pData = NULL;
COLUMNDATA* pcolData = NULL;
CHAR strData[] = "Show me the money!";
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = new WCHAR[wcslen(g_wszTable)+2];
wcscpy(TableID.uName.pwszName, g_wszTable);
// Get the fast-load pointer.
if(FAILED(hr = SetFastLoadProperty(TRUE)))
goto cleanup;
if( FAILED( hr = g_pIDBInitialize->QueryInterface(
IID_IDBCreateSession, (void **) &pIDBCreateSession )))
goto cleanup;
if( FAILED( hr = pIDBCreateSession->CreateSession( NULL,
IID_IOpenRowset, (IUnknown **) &pIOpenRowsetFL )))
goto cleanup;
// Get IRowsetFastLoad initialized to use the test table.
if(FAILED(hr = pIOpenRowsetFL->OpenRowset(NULL, &TableID,
NULL, IID_IRowsetFastLoad, 0, NULL, (LPUNKNOWN *)&pIFastLoad)))
goto cleanup;
// Next set up an accessor for the data.
// Set up custom bindings.
oneBinding.dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
oneBinding.iOrdinal = 1;
oneBinding.pTypeInfo = NULL;
oneBinding.obValue = ulOffset + offsetof(COLUMNDATA,bData);
oneBinding.obLength = ulOffset + offsetof(COLUMNDATA,dwLength);
oneBinding.obStatus = ulOffset + offsetof(COLUMNDATA,dwStatus);
oneBinding.cbMaxLen = 30; //Size of varchar column.
oneBinding.pTypeInfo = NULL;
oneBinding.pObject = NULL;
oneBinding.pBindExt = NULL;
oneBinding.dwFlags = 0;
oneBinding.eParamIO = DBPARAMIO_NOTPARAM;
oneBinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
oneBinding.bPrecision = 0;
oneBinding.bScale = 0;
oneBinding.wType = DBTYPE_STR;
ulOffset = oneBinding.cbMaxLen + offsetof(COLUMNDATA,
bData);
ulOffset = ROUND_UP( ulOffset, COLUMN_ALIGNVAL );
if( FAILED( hr = pIFastLoad->QueryInterface(IID_IAccessor,
(void **) &pIAccessor)))
return hr;
if(FAILED(hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1,
&oneBinding, ulOffset, &hAccessor, &oneStatus)))
return hr;
// Set up memory buffer.
pData = new BYTE[40];
pcolData = (COLUMNDATA*)pData;
pcolData->dwLength = strlen("Show the data") + 1;
pcolData->dwStatus = 0;
memcpy(&(pcolData->bData), "Show the data",
strlen("Show me data") + 1);
for(i=0; i<10; i++)
{
if(FAILED(hr = pIFastLoad->InsertRow(hAccessor, pData)))
goto cleanup;
}
if(FAILED(hr = pIFastLoad->Commit(TRUE)))
printf("Error on IRFL::Commit\n");
cleanup:
if(FAILED(hr2 = SetFastLoadProperty(FALSE)))
{
printf("SetFastLoadProperty(FALSE) failed with %x", hr2);
}
if(pIAccessor && hAccessor)
{
if(FAILED(pIAccessor->ReleaseAccessor(hAccessor, NULL)))
hr = E_FAIL;
}
if(pIAccessor)
pIAccessor->Release();
if(pIFastLoad)
pIFastLoad->Release();
if(pIOpenRowsetFL)
pIOpenRowsetFL->Release();
if(pIDBCreateSession)
pIDBCreateSession->Release();
if(TableID.uName.pwszName)
delete []TableID.uName.pwszName;
return hr;
}
//--------------------------------------------------------------
HRESULT SetFastLoadProperty(BOOL fSet)
{
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;
rgProps[0].vValue.vt = VT_BOOL;
rgProps[0].dwPropertyID = SSPROP_ENABLEFASTLOAD;
if(fSet == TRUE)
rgProps[0].vValue.boolVal = VARIANT_TRUE;
else
rgProps[0].vValue.boolVal = VARIANT_FALSE;
PropSet.rgProperties = rgProps;
PropSet.cProperties = 1;
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDATASOURCE;
if(SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties,
(LPVOID *)&pIDBProps)))
{
hr = pIDBProps->SetProperties(1, &PropSet);
}
VariantClear(&rgProps[0].vValue);
if(pIDBProps)
pIDBProps->Release();
return hr;
}
//--------------------------------------------------------------
HRESULT CreateSessionCommand
(
DBPROPSET* rgPropertySets, // @parm [in] property sets.
ULONG ulcPropCount, // @parm [in] count of prop sets.
CLSID clsidProv // @parm [in] Provider CLSID.
)
{
HRESULT hr = NOERROR;
IDBCreateSession* pIDBCreateSession = NULL;
IDBProperties* pIDBProperties = NULL;
UWORD i=0, j=0; // indexes.
if(ulcPropCount && !rgPropertySets)
{
hr = E_INVALIDARG;
return hr;
}
if (!SUCCEEDED(hr = CoCreateInstance(clsidProv,
NULL,CLSCTX_INPROC_SERVER, IID_IDBInitialize,
(void **)&g_pIDBInitialize)))
{
goto CLEANUP;
}
if (!SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(
IID_IDBProperties, (void **)&pIDBProperties)))
{
goto CLEANUP;
}
if (!SUCCEEDED(hr = pIDBProperties->SetProperties( ulcPropCount,
rgPropertySets)))
{
goto CLEANUP;
}
if (!SUCCEEDED(hr = g_pIDBInitialize->Initialize()))
{
printf("Call to initialize failed.\n");
goto CLEANUP;
}
CLEANUP:
if(pIDBProperties)
pIDBProperties->Release();
if(pIDBCreateSession)
pIDBCreateSession->Release();
for(i = 0; i < ulcPropCount; i++)
for(j = 0; j < rgPropertySets[i].cProperties; j++)
{
VariantClear(&(rgPropertySets[i].rgProperties[j]).vValue);
}
return hr;
}
//--------------------------------------------------------------
void SetupOption
(
DBPROPID PropID,
WCHAR *wszVal,
DBPROP * pDBProp
)
{
pDBProp->dwPropertyID = PropID;
pDBProp->dwOptions = DBPROPOPTIONS_REQUIRED;
pDBProp->colid = DB_NULLID;
pDBProp->vValue.vt = VT_BSTR;
pDBProp->vValue.bstrVal = SysAllocStringLen(wszVal,
wcslen(wszVal));
}