How To
Fetching Columns Using IRow::GetColumns (or IRow::Open) and ISequentialStream
Large data can be bound or retrieved using the IsequentialStream interface. For bound columns, the status flag indicates if the data is truncated by setting DBSTATUS_S_TRUNCATED.
To fetch columns using IRow::GetColumns (or IRow::Open) and ISequentialStream
- Establish a connection to the data source.
- Execute the command (in this example, IcommandExecute::Execute() is called with IID_IRow).
- The column data can be fetched using IRow::Open() or IRow::GetColumns().
- IRow::Open() can be used to open an IsequentialStream on the row. Specify DBGUID_STREAM to indicate that the column contains a stream of binary data (IStream or ISequentialStream can then be used to read the data from the column).
- If IRow::GetColumns() is used, then the pData element of DBCOLUMNACCESS structure is set to point to a stream object.
- IRow::Open() can be used to open an IsequentialStream on the row. Specify DBGUID_STREAM to indicate that the column contains a stream of binary data (IStream or ISequentialStream can then be used to read the data from the column).
- IsequentialStream::Read() is used repeatedly to read the specified number of bytes into the consumer buffer.
Here is the sample table used by the application:
use pubs
go
if exists (select name from sysobjects where name = 'MyTable')
drop table MyTable
go
create table MyTable
(
col1 int,
col2 varchar(50),
col3 char(50),
col4 datetime,
col5 float,
col6 money,
col7 sql_variant,
col8 binary(50),
col9 text,
col10 image
)
go
/* Enter data */
insert into MyTable
values
(
10,
'abcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'11/1/1999 11:52 AM',
3.14,
99.95,
convert(nchar(50), N'AbCdEfGhIjKlMnOpQrStUvWxYz'),
0x123456789,
replicate('AAAAABBBBB', 500),
replicate(0x123456789, 500)
)
go
Here is the sample code:
/*
Example shows how to fetch a single row using IRow. In this example
one column at a time is retrieved from the row. This example illustrate
the use of IRow::Open() as well as IRow::GetColumns(). To read the
column data, the example uses ISequentialStream::Read.
*/
#define DBINITCONSTANTS
#define INITGUID
#include <stdio.h>
#include <windows.h>
#include <iostream.h>
#include <oledb.h>
#include <sqloledb.h>
//constants
const int kMaxBuff = 50;
void InitializeAndEstablishConnection();
HRESULT GetColumnSize(IRow* pUnkRow, ULONG iCol);
ULONG PrintData(ULONG iCols, ULONG iStart, DBCOLUMNINFO* prgInfo,
DBCOLUMNACCESS* prgColumns);
HRESULT GetColumns(IRow* pUnkRow, ULONG iStart, ULONG iEnd);
HRESULT GetSequentialColumn(IRow* pUnkRow, ULONG iCol, BOOL fOpen = TRUE);
IDBInitialize* pIDBInitialize = NULL;
IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;
IRow * pIRow = 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()
{
ULONG iidx = 0;
WCHAR* wCmdString
= OLESTR(" SELECT * FROM MyTable ");
// 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_IRow,
NULL,
&cNumRows,
(IUnknown **) &pIRow)))
{
cout << "Failed to execute command.\n";
}
//Get columns (one at a time) using ISequentialStream and Open
for(iidx = 1; iidx <= 10; iidx++)
//the 3rd parameter is by default TRUE indicating use of ISequentialStream
//and Open.
hr = GetSequentialColumn(pIRow, iidx);
//Release the Row object.
pIRow->Release();
//Execute the command again.
if(FAILED(hr = pICommandText->Execute(NULL,
IID_IRow,
NULL,
&cNumRows,
(IUnknown **) &pIRow)))
{
cout << "Failed to execute command.\n";
}
/*
Now get columns (one at a time) using ISequentialStream and
GetColumns. The 3rd parameter is by default TRUE indicating use
of ISequentialStream and GetColumns.
*/
for(iidx = 1; iidx <= 10; iidx++)
hr = GetSequentialColumn(pIRow, iidx, FALSE);
//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();
};
//--------------------------------------------------------------------
HRESULT GetSequentialColumn(IRow* pUnkRow, ULONG iCol, BOOL fOpen)
{
HRESULT hr = NOERROR;
ULONG cbRead = 0;
ULONG cbTotal = 0;
ULONG cColumns = 0;
ULONG cReads = 0;
ISequentialStream* pIStream = NULL;
WCHAR* pBuffer[kMaxBuff];//50 chars read by ISequentialStream::Read()
DBCOLUMNINFO* prgInfo;
OLECHAR* pColNames;
IColumnsInfo* pIColumnsInfo;
DBID columnid;
DBCOLUMNACCESS column;
wprintf(TEXT("[RETRIEVING COLUMN %d SEQUENTIALLY]\n"), iCol);
//Get column information (basically get column id)
hr = pUnkRow->QueryInterface(IID_IColumnsInfo,
(void**) &pIColumnsInfo);
if(FAILED(hr))
goto CLEANUP;
hr = pIColumnsInfo->GetColumnInfo(&cColumns, &prgInfo, &pColNames);
if (FAILED(hr))
goto CLEANUP;
//Get Column ID
columnid = (prgInfo + (iCol - 1))->columnid;
if (fOpen) //Get columns using Open and ISequentialStream.
{
wprintf(TEXT("[RETRIEVING COLUMNS USING "));
wprintf(TEXT(" ISequentialSteam and Open]\n"));
//Open sequential stream
hr = pUnkRow->Open(NULL,
&columnid,
DBGUID_STREAM,
0,
IID_ISequentialStream,
(LPUNKNOWN *)&pIStream);
if (FAILED(hr))
{
wprintf(TEXT("Unable to get ISequentialStream interface.\n"));
goto CLEANUP;
}
}
else //Get Columns using GetColumns and ISequentialStream.
{
IUnknown* pUnkStream = NULL;
ZeroMemory(&column, sizeof(column));
column.columnid = prgInfo[iCol - 1].columnid;
column.wType = DBTYPE_IUNKNOWN;
column.pData = (LPVOID*) &pUnkStream;
hr = pUnkRow->GetColumns(1, &column);
if (FAILED(hr))
{
wprintf(TEXT("Error executing IRow::GetColumns.\n"));
goto CLEANUP;
}
hr = pUnkStream->QueryInterface(IID_ISequentialStream,
(LPVOID*) &pIStream);
if (FAILED(hr))
{
wprintf(TEXT("Unable to get ISequentialStream interface "));
wprintf(TEXT("via IRow::GetColumns.\n"));
goto CLEANUP;
}
pUnkStream->Release();
}
ZeroMemory(pBuffer, kMaxBuff * sizeof(WCHAR));
//Read 50 chars at a time until no more data.
do
{
hr = pIStream->Read(pBuffer, kMaxBuff, &cbRead);
if(FAILED(hr))
{
wprintf(TEXT("Error reading data.\n"));
goto CLEANUP;
}
cbTotal = cbTotal + cbRead;
//Print the data
wprintf(TEXT("READ #%d: %-*S\n"), ++cReads, kMaxBuff, pBuffer);
} while(cbRead > 0);
wprintf(TEXT("[READ %d bytes for column %d.\n"), cbTotal, iCol);
CLEANUP:
if(pIColumnsInfo)
pIColumnsInfo->Release();
if(pIStream)
pIStream->Release();
return hr;
}
//--------------------------------------------------------------------
BOOL InitColumn(DBCOLUMNACCESS* pCol, DBCOLUMNINFO* pInfo)
{
/*
If text or image column is being read, in which case the max possible
length of a value is the column is hugh, we will limit that size to
512 bytes (for illustration purposes).
*/
ULONG ulSize=
(pInfo->ulColumnSize < 0x7fffffff) ? pInfo->ulColumnSize : 512;
//Verify dta buffer is large enough.
if(pCol->cbMaxLen < (ulSize + 1))
{
if(pCol->pData)
{
delete [] pCol->pData;
pCol->pData = NULL;
}
//Allocate data buffer
pCol->pData = new WCHAR[ulSize + 1];
//set the max length of caller-initialized memory.
pCol->cbMaxLen = sizeof(WCHAR) * (ulSize + 1);
/*
In the above 2 steps, pData is pointing to memory (it is not NULL)
and cbMaxLen has a value (not 0), so next call to IRow->GetData()
will read the data from the column.
*/
}
//Clear memory buffer
ZeroMemory((void*) pCol->pData, pCol->cbMaxLen);
//Set properties.
pCol->wType = DBTYPE_WSTR;
pCol->columnid = pInfo->columnid;
pCol->cbDataLen = 0;
pCol->dwStatus = 0;
pCol->dwReserved = 0;
pCol->bPrecision = 0;
pCol->bScale = 0;
return TRUE;
}
//--------------------------------------------------------------------
HRESULT GetColumns(IRow* pUnkRow, ULONG iStart, ULONG iEnd)
//Start and end are same. Thus, get only one column.
{
HRESULT hr;
ULONG iidx; //loop counter
ULONG cColumns; //Count of columns
ULONG cUserCols; //Count of user columns
DBCOLUMNINFO* prgInfo; //Column of info. array
OLECHAR* pColNames; //Array of column names
DBCOLUMNACCESS* prgColumns; //Ptr to column access structures array
DBCOLUMNINFO* pCurrInfo;
DBCOLUMNACCESS* pCurrCol;
IColumnsInfo* pIColumnsInfo = NULL;
//Initialize
cColumns = 0;
prgInfo = NULL;
pColNames = NULL;
prgColumns = NULL;
printf("Retrieving data\n");
//Get column info to build column access array
hr=pUnkRow->QueryInterface(IID_IColumnsInfo, (void**)&pIColumnsInfo);
if(FAILED(hr))
goto CLEANUP;
hr=pIColumnsInfo->GetColumnInfo(&cColumns, &prgInfo, &pColNames);
if(FAILED(hr))
goto CLEANUP;
printf("In GetColumns(), Columns= %d\n", cColumns);
/*
Determine no. of columns to retrieve.
Since iEnd and iStart is same, this is redundent step.
cUserCols will always be 1.
*/
cUserCols = iEnd - iStart + 1;
//Walk list of columns and setup a DBCOLUMNACCESS structure
prgColumns= new DBCOLUMNACCESS[cUserCols]; //cUserCols is only 1
ZeroMemory((void*) prgColumns, sizeof(DBCOLUMNACCESS) * cUserCols);
for(iidx=0; iidx < cUserCols; iidx++)
{
pCurrInfo = prgInfo + iidx + iStart - 1;
pCurrCol = prgColumns + iidx;
//Here the values of pData and cbMaxLen elements of
//DBCOLUMNACCESS elements is set. Thus IRow->GetColumns()
//will return actual data.
if(InitColumn(pCurrCol, pCurrInfo) == FALSE)
goto CLEANUP;
}
hr = pUnkRow->GetColumns(cUserCols, prgColumns); //cUserCols=1
if(FAILED(hr))
{
printf("Error occured\n");
}
//Show data.
PrintData(cUserCols, iStart, prgInfo, prgColumns);
CLEANUP:
if(pIColumnsInfo)
pIColumnsInfo->Release();
if(prgColumns)
delete [] prgColumns;
return hr;
}
//--------------------------------------------------------------------
/*
This function returns the actual width of the data in the column
(not the columnwidth in DBCOLUMNFO structure which is the width of the
column)
*/
HRESULT GetColumnSize(IRow* pUnkRow, ULONG iCol)
{
HRESULT hr = NOERROR;
ULONG iidx = 0; //Loop counter
ULONG cColumns = 0; //Count the columns
DBCOLUMNINFO* prgInfo; //Column info array
OLECHAR* pColNames;
DBCOLUMNACCESS column;
DBCOLUMNINFO* pCurrInfo;
IColumnsInfo* pIColumnsInfo = NULL;
//Initialize
prgInfo = NULL;
pColNames = NULL;
printf("Checking column size\n");
//Get column info to build column access array
hr=pUnkRow->QueryInterface(IID_IColumnsInfo, (void**) &pIColumnsInfo);
if (FAILED(hr))
goto CLEANUP;
hr=pIColumnsInfo->GetColumnInfo(&cColumns, &prgInfo, &pColNames);
if (FAILED(hr))
goto CLEANUP;
printf("Value of cColumns is %d\n", cColumns);
/*
Setup a DBCOLUMNACCESS structure: Here pData is set to NULL and
cbMaxLen is set to 0. Thus IRow->GetColumns() returns only the actual
column length in cbDataLen member of DBCOLUMNACCESS structure.In this
case you can call IRow->GetColumns() again for the same column to
retrieve actual data in the second call.
*/
ZeroMemory((void*) &column, sizeof(DBCOLUMNACCESS));
column.pData=NULL;
pCurrInfo = prgInfo + iCol - 1;
//Get the column id in DBCOLUMNACCESS structure.
//It is then used in GetColumn().
column.columnid = pCurrInfo->columnid;
printf("column.columnid value is %d\n", column.columnid);
//We know which column to get.
//The column.columnid gives the column number.
hr = pUnkRow->GetColumns(1, &column);
if (FAILED(hr))
{
printf("Errors occured\n");
}
//Show data
PrintData(1, iCol, prgInfo, &column);
CLEANUP:
if (pIColumnsInfo)
pIColumnsInfo->Release();
return hr;
}
//--------------------------------------------------------------------
BOOL GetStatus(DWORD dwStatus, WCHAR* pwszStatus)
{
switch (dwStatus)
{
case DBSTATUS_S_OK:
wcscpy(pwszStatus, TEXT("DBSTATUS_S_OK"));
break;
case DBSTATUS_E_UNAVAILABLE:
wcscpy(pwszStatus, TEXT("DBSTATUS_E_UNAVAILABLE"));
break;
case DBSTATUS_S_TRUNCATED:
wcscpy(pwszStatus, TEXT("DBSTATUS_S_TRUNCATED"));
break;
}
return TRUE;
}
//--------------------------------------------------------------------
ULONG PrintData(ULONG iCols,
ULONG iStart,
DBCOLUMNINFO* prgInfo,
DBCOLUMNACCESS* prgColumns)
{
WCHAR wszStatus[255];
DBCOLUMNINFO* pCurrInfo;
DBCOLUMNACCESS* pCurrCol;
printf("No. Name Status Length Max Data\n");
for(ULONG iidx=0; iidx < iCols; iidx++)
{
pCurrInfo=prgInfo + iidx + iStart - 1;
pCurrCol=prgColumns+iidx;
GetStatus(pCurrCol->dwStatus, wszStatus);
//was the data successfully retrieved?
wprintf(TEXT("%-3d %-*s %-20s %-3d %-3d %-20s\n"),
iStart+iidx,
10,
pCurrInfo->pwszName,
wszStatus,
pCurrCol->cbDataLen,
pCurrCol->cbMaxLen,
(WCHAR*) pCurrCol->pData);
}
wprintf(TEXT("\n"));
return iidx;
}
//--------------------------------------------------------------------
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"mohanv1");
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"pubs");
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"sa");
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"");
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 establishing connection to the data source.\n";
}
} //end of InitializeAndEstablishConnection.
//--------------------------------------------------------------------