How to bulk copy data using IRowsetFastLoad (OLE DB)

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

1. Establish a connection to the data source.
2. Set the SQLOLEDB provider-specific data source property SSPROP_ENABLEFASTLOAD to VARIANT_TRUE. With this property set to VARIANT_TRUE, the newly created session allows the consumer access to IRowsetFastLoad.
3. Create a session requesting the IOpenRowset interface.
4. Call IOpenRowset::OpenRowset to open a rowset that includes all the rows from the table (in which data is to be copied using bulk-copy operation).
5. Do the necessary bindings and create an accessor using IAccessor::CreateAccessor.
6. Set up the memory buffer from which the data will be copied to the table.
7. Call IRowsetFastLoad::InsertRow to bulk copy the data in to the 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 tableIRFLTable 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 offsetof #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 rowsetpointer 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 fastload 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; //# 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 fastload 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)); }

Source: http://doc.ddart.net/mssql/sql2000/html/howtosql/ht_olehowt1_7nll.htm