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 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));

    }

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.