How to bulk copy data using IRowsetFastLoad (OLE DB)
From How to Install SQL Server 2000
How To
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
- Establish a connection to the data source.
- 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.
- Create a session requesting the IOpenRowset interface.
- 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).
- Do the necessary bindings and create an accessor using IAccessor::CreateAccessor.
- Set up the memory buffer from which the data will be copied to the table.
- 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 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 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));
}