How To
Fetching Columns Using IRow::GetColumns (OLE DB)
The IRow interface allows direct access to columns of a single row in the result set. Thus, IRow is an efficient way to retrieve columns from a result set with one row.
To fetch columns using IRow::GetColumns
- Establish a connection to the data source.
- Execute the command (in the following example, IcommandExecute::Execute() is called with IID_IRow).
- Execute IRow::GetColumns() to fetch one or more columns in the resulting row. If you want to find the actual column size before fetching data, set the pData in DBCOLUMNACCESS to NULL. The call to IRow::GetColumns() will return only the column width. Another call the IRow::GetColumns() will fetch the data.
- Execute IRow::GetColumns() until all the columns you need are accessed. The columns must be accessed in sequence.
This example shows how to fetch a single row using IRow. It also illustrates two ways to access columns in the row:
- Fetching columns in groups, and
- Obtaining the column width first and then fetch the column data.
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
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:
/*
This example shows how to fetch a single row using IRow. In this
example one column at a time is retrieved from the row. The example
shows 2 things
1) How to fetch a group of columns (in sequence)
2) How to access a column twice - the first time the actual column
width is obtained and then later the actual data is accessed. In the
DBCOLUMNACCESS structure, if pData is NULL and cbMaxLen is 0, the
call to IRow->GetColumns() returns only the actual column length. In
this case IRow->GetColumns() can be called again on the
same column to retrieve the actual data.
*/
#define DBINITCONSTANTS
#include <stdio.h>
#include <windows.h>
#include <iostream.h>
#include <oledb.h>
#include <sqloledb.h>
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);
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";
}
/*
Fetch columns 1-5 and then 6-10 and display the contents
*/
if(FAILED(hr = pICommandText->Execute(NULL,
IID_IRow,
NULL,
&cNumRows,
(IUnknown **) &pIRow)))
{
cout << "Failed to execute command.\n";
}
hr = GetColumns(pIRow, 1, 5);
hr = GetColumns(pIRow, 6, 10);
hr = pIRow->Release();
//Execute the command.
if(FAILED(hr = pICommandText->Execute(NULL,
IID_IRow,
NULL,
&cNumRows,
(IUnknown **) &pIRow)))
{
cout << "Failed to execute command.\n";
}
//Get columns
for(iidx=1; iidx <=10; iidx++)
{
hr=GetColumnSize(pIRow, iidx);
hr=GetColumns(pIRow, iidx, iidx);
}
pIRow->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();
};
//--------------------------------------------------------------------
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 DBCOLUMNACCESS elements is set
//(pData and cbMaxLen)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 no.
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.
//--------------------------------------------------------------------