How to fetch rows from a result set (OLE DB)

How to Install SQL Server 2000

How To

How to fetch rows from a result set (OLE DB)

To fetch rows from a result set

/*
    Example shows how to fetch rows from a result set.
*/
void InitializeAndEstablishConnection();
void ProcessResultSet();

#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID

#include <stdio.h>
#include <tchar.h>
#include <stddef.h>
#include <windows.h>
#include <iostream.h>
#include <oledb.h>
#include <SQLOLEDB.h>

IDBInitialize*       pIDBInitialize     = NULL;
IDBProperties*       pIDBProperties     = NULL;
IDBCreateSession*    pIDBCreateSession  = NULL;
IDBCreateCommand*    pIDBCreateCommand  = NULL;
ICommandText*        pICommandText      = NULL;
IRowset*             pIRowset           = NULL;
IColumnsInfo*        pIColumnsInfo      = NULL;
DBCOLUMNINFO*        pDBColumnInfo      = NULL;
IAccessor*           pIAccessor        =  NULL;
DBPROP               InitProperties[4];
DBPROPSET            rgInitPropSet[1];
ULONG                i, j;
HRESULT              hr;
LONG                 cNumRows = 0;
ULONG                lNumCols;
WCHAR*               pStringsBuffer;
DBBINDING*           pBindings;
ULONG                ConsumerBufColOffset = 0;
HACCESSOR            hAccessor;
ULONG                lNumRowsRetrieved;
HROW                 hRows[10];
HROW*                pRows = &hRows[0];
BYTE*                pBuffer;

void main() {

    //Here is the command to execute.
    WCHAR* wCmdString 
        = OLESTR(" SELECT title, price FROM titles WHERE royalty > 14");
  // Call a function to initialize and establish connection. 
    InitializeAndEstablishConnection();

    //Create a session object.
    if(FAILED(pIDBInitialize->QueryInterface(
                                IID_IDBCreateSession,
                                (void**) &pIDBCreateSession)))
    {
        cout << "Failed to obtain IDBCreateSession interface.\n";
    }

    if(FAILED(pIDBCreateSession->CreateSession(
                                     NULL, 
                                     IID_IDBCreateCommand, 
                                     (IUnknown**) &pIDBCreateCommand)))
    {
        cout << "pIDBCreateSession->CreateSession failed.\n";
    }

    //Access the ICommandText interface.
    if(FAILED(pIDBCreateCommand->CreateCommand(
                                    NULL, 
                                    IID_ICommandText, 
                                    (IUnknown**) &pICommandText)))
    {
        cout << "Failed to access ICommand interface.\n";
    }
    
    //Use SetCommandText() to specify the command text.
    if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString)))
    {
        cout << "Failed to set command text.\n";
    }

    //Execute the command.
    if(FAILED(hr = pICommandText->Execute(NULL, 
                                    IID_IRowset, 
                                    NULL, 
                                    &cNumRows, 
                                    (IUnknown **) &pIRowset)))
    {
        cout << "Failed to execute command.\n";
    }

    //Process the result set.
    ProcessResultSet(); 
                        
    pIRowset->Release();

    //Free up memory.
    pICommandText->Release();
    pIDBCreateCommand->Release();
    pIDBCreateSession->Release();
    
    if(FAILED(pIDBInitialize->Uninitialize()))
    {
        /*Uninitialize is not required, but it fails if an interface
        has not been released.  This can be used for debugging.
        cout << "Problem uninitializing.\n"; */
    } //endif.
    pIDBInitialize->Release();
    
    //Release the COM library.
    CoUninitialize();
};
//--------------------------------------------------------------------
void InitializeAndEstablishConnection()
{    
    //Initialize the COM library.
    CoInitialize(NULL);

    //Obtain access to the SQLOLEDB provider.
    hr = CoCreateInstance(CLSID_SQLOLEDB, 
                          NULL, 
                          CLSCTX_INPROC_SERVER,
                          IID_IDBInitialize, 
                          (void **) &pIDBInitialize);
    if(FAILED(hr))
    {
        printf("Failed to get IDBInitialize interface.\n");
    } //end if

    /*
    Initialize the property values needed 
    to establish the connection.
    */
    for(i = 0; i < 4; i++) 
        VariantInit(&InitProperties[i].vValue);
    

    //Server name.
    InitProperties[0].dwPropertyID  = DBPROP_INIT_DATASOURCE;
    InitProperties[0].vValue.vt     = VT_BSTR;
    InitProperties[0].vValue.bstrVal= 
                            SysAllocString(L"server");
    InitProperties[0].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[0].colid         = DB_NULLID;

//Database.
    InitProperties[1].dwPropertyID  = DBPROP_INIT_CATALOG;
    InitProperties[1].vValue.vt     = VT_BSTR;
    InitProperties[1].vValue.bstrVal= SysAllocString(L"database");
    InitProperties[1].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[1].colid         = DB_NULLID;

//Username (login).
    InitProperties[2].dwPropertyID  = DBPROP_AUTH_USERID; 
    InitProperties[2].vValue.vt     = VT_BSTR;
    InitProperties[2].vValue.bstrVal= SysAllocString(L"login");
    InitProperties[2].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[2].colid         = DB_NULLID;

//Password.
    InitProperties[3].dwPropertyID  = DBPROP_AUTH_PASSWORD;
    InitProperties[3].vValue.vt     = VT_BSTR;
    InitProperties[3].vValue.bstrVal= SysAllocString(L"Password");
    InitProperties[3].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[3].colid         = DB_NULLID;

    /*
    Now that the properties are set, construct the DBPROPSET structure
    (rgInitPropSet). The DBPROPSET structure is used to pass an array 
    of DBPROP structures (InitProperties) to the SetProperties method.
    */
    rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
    rgInitPropSet[0].cProperties    = 4;
    rgInitPropSet[0].rgProperties   = InitProperties;

    //Set initialization properties.
    hr = pIDBInitialize->QueryInterface(IID_IDBProperties, 
                                   (void **)&pIDBProperties);
    if(FAILED(hr))
    {
        cout << "Failed to get IDBProperties interface.\n";
    }

    hr = pIDBProperties->SetProperties(1, rgInitPropSet); 
    if(FAILED(hr))
    {
        cout << "Failed to set initialization properties.\n";
    } //end if

    pIDBProperties->Release();

    //Now establish the connection to the data source.
    if(FAILED(pIDBInitialize->Initialize()))
    {
        cout << "Problem in establishing connection to the data
        source.\n";
    }
} //end of InitializeAndEstablishConnection.
//--------------------------------------------------------------------
//Retrieve and display data resulting from a query.
void ProcessResultSet()
{
    //Obtain access to the IColumnInfo interface, from the Rowset
    object.
    hr = pIRowset->QueryInterface(IID_IColumnsInfo, 
                                 (void **)&pIColumnsInfo);
    if(FAILED(hr))
    {
        cout << "Failed to get IColumnsInfo interface.\n";
    } //end if

    //Retrieve the column information.
    pIColumnsInfo->GetColumnInfo(&lNumCols, 
                                 &pDBColumnInfo, 
                                 &pStringsBuffer);

    //Free the column information interface.
    pIColumnsInfo->Release();

    //Create a DBBINDING array.
    pBindings = new DBBINDING[lNumCols];

    //Using the ColumnInfo structure, fill out the pBindings array.
    for(j=0; j<lNumCols; j++) {
        pBindings[j].iOrdinal = j+1;
        pBindings[j].obValue = ConsumerBufColOffset;
        pBindings[j].pTypeInfo = NULL;
        pBindings[j].pObject = NULL;
        pBindings[j].pBindExt = NULL;
        pBindings[j].dwPart = DBPART_VALUE;
        pBindings[j].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        pBindings[j].eParamIO = DBPARAMIO_NOTPARAM;
        pBindings[j].cbMaxLen = pDBColumnInfo[j].ulColumnSize;
        pBindings[j].dwFlags = 0;
        pBindings[j].wType = pDBColumnInfo[j].wType;
        pBindings[j].bPrecision = pDBColumnInfo[j].bPrecision;
        pBindings[j].bScale = pDBColumnInfo[j].bScale;
        
        //Compute the next buffer offset.
        ConsumerBufColOffset = ConsumerBufColOffset + 
                               pDBColumnInfo[j].ulColumnSize;
    };
    //Get the IAccessor interface.
    hr = pIRowset->QueryInterface(IID_IAccessor, (void **) &pIAccessor);
    if(FAILED(hr))
    {
        cout << "Failed to obtain IAccessor interface.\n";
    }
//Create an accessor from the set of bindings (pBindings).
    pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
                                lNumCols,
                                pBindings,
                                0,
                                &hAccessor,
                                NULL);
                                               
    //Print column names.
    for(j=0; j<lNumCols; j++) {
        printf("%-40S", pDBColumnInfo[j].pwszName);
    };
    //Get a set of 10 rows.
    pIRowset->GetNextRows(
                        NULL,
                        0,
                        10,
                        &lNumRowsRetrieved,
                        &pRows);

    //Allocate space for the row buffer.
    pBuffer = new BYTE[ConsumerBufColOffset];

    //Display the rows.
    while(lNumRowsRetrieved > 0) {
        //For each row, print the column data.
        for(j=0; j<lNumRowsRetrieved; j++) {
            //Clear the buffer.
            memset(pBuffer, 0, ConsumerBufColOffset);
            //Get the row data values.
            pIRowset->GetData(hRows[j], hAccessor, pBuffer);
            //Print title and price values.
            printf("%-40s%f\n", &pBuffer[pBindings[0].obValue],
                                (FLOAT) pBuffer[pBindings[0].obValue]);
        
        }; //for.

        //Release the rows retrieved.
        pIRowset->ReleaseRows(lNumRowsRetrieved, 
                              hRows, 
                              NULL, 
                              NULL, 
                              NULL);
        //Get the next set of 10 rows.
        pIRowset->GetNextRows(NULL,
                              0,
                              10,
                              &lNumRowsRetrieved,
                              &pRows);
    };  //while lNumRowsRetrieved > 0.

    //Free up all allocated memory.
    delete [] pBuffer;
    pIAccessor->ReleaseAccessor(hAccessor, NULL);
    pIAccessor->Release();
    delete [] pBindings;
} //ProcessResultSet.