使用 IRow::GetColumns 提取列 (OLE DB)
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)
通过 IRow 接口可以直接访问结果集中某一行的列 。 因而，IRow 是一种从具有一行的结果集中检索列的有效方法 。
所提供的代码示例显示如何使用 IRow 提取单一行。 在本示例中，将一次从该行中检索一列。 此示例说明：
如何提取一组列（依次）。
如何两次访问某一列。 第一次获取实际列宽度，稍后访问实际数据。 在 DBCOLUMNACCESS 结构中，如果 pData 为 NULL 且 cbMaxLen 为 0，那么调用 IRow->GetColumns() 只会返回实际列长度 。 在这种情况下，可以再次对同一列调用 IRow->GetColumns()，以检索实际数据。
重要
请尽可能使用 Windows 身份验证。 如果 Windows 身份验证不可用，请在运行时提示用户输入其凭据。 不要将凭据存储在一个文件中。 如果必须保存凭据，应当用 Win32 crypto API（Win32 加密 API）加密它们。
使用 IRow::GetColumns 提取列
建立与数据源的连接。
执行命令（在以下示例中，将通过 IID_IRow 调用 ICommandExecute::Execute()）。
执行 IRow::GetColumns() 以提取结果行中的一列或多列。 如果您要在提取数据之前查找实际列大小，请将 DBCOLUMNACCESS 中的 pData 设置为 NULL。 对于 IRow::GetColumns() 的这一调用只返回列宽度。 再次调用 IRow::GetColumns() 将提取数据。
执行 IRow::GetColumns()，直到访问您需要的所有列。 必须依次访问这些列。
示例
此示例显示如何使用 IRow 接口实现对结果集中某一行的列的直接访问。 该示例演示：
如何依次提取一组列。
如何两次访问某列 - 第一次获取实际列宽，稍后访问实际数据。
在 DBCOLUMNACCESS 结构中，如果 pData 为 NULL 且 cbMaxLen 为 0，那么调用 IRow->GetColumns 只会返回实际列长度。 在这种情况下，可以再次对同一列调用 IRow->GetColumns，以检索实际数据。 IA64 平台不支持此示例。
本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。
第一个 (Transact-SQL) 代码列表创建该示例要使用的表。
使用 ole32.lib 和 oleaut32.lib 编译并执行第二个 (C++) 代码列表。 此应用程序连接到您的计算机上默认的 SQL Server 实例。 在某些 Windows 操作系统上，您需要将 (localhost) 或 (local) 更改为您的 SQL Server 实例的名称。 要连接到命名实例，请将连接字符串从 L"(local)" 更改为 L"(local)\\name"，其中 name 是命名实例。 默认情况下，SQL Server Express 安装在命名实例中。 请确保 INCLUDE 环境变量包括含有 msoledbsql.h 的目录。
第三个 (Transact-SQL) 代码列表删除该示例使用的表。
use AdventureWorks2022;
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
// compile with: ole32.lib oleaut32.lib
#define DBINITCONSTANTS
#define OLEDBVER 0x0250 // to include correct interfaces
#include <stdio.h>
#include <windows.h>
#include <iostream>
#include <oledb.h>
#include <msoledbsql.h>
using namespace std;
int 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;
DBCOLUMNINFO* pDBColumnInfo = NULL;
IAccessor* pIAccessor = NULL;
DBPROP InitProperties[4];
DBPROPSET rgInitPropSet[1];
ULONG i, j;
HRESULT hresult;
DBROWCOUNT cNumRows = 0;
ULONG lNumCols;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
ULONG ConsumerBufColOffset = 0;
HACCESSOR hAccessor;
ULONG lNumRowsRetrieved;
HROW hRows[10];
HROW* pRows = &hRows[0];
int main() {
ULONG iidx = 0;
WCHAR* wCmdString = OLESTR(" SELECT * FROM MyTable");
// Call a function to initialize and establish connection.
if (InitializeAndEstablishConnection() == -1) {
cout << "Failed to initialize and establish connection.\n";
// Insert your code for cleanup and error handling.
return -1;
}
// Create a session object.
if (FAILED(pIDBInitialize->QueryInterface (
IID_IDBCreateSession, (void**) &pIDBCreateSession))) {
cout << "Failed to obtain IDBCreateSession interface.\n";
// Insert your code for cleanup and error handling.
return -1;
}
if (FAILED(pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand))) {
cout << "pIDBCreateSession->CreateSession failed.\n";
// Insert your code for cleanup and error handling.
return -1;
}
// Access the ICommandText interface.
if (FAILED(pIDBCreateCommand->CreateCommand( NULL, IID_ICommandText, (IUnknown**) &pICommandText))) {
cout << "Failed to access ICommand interface.\n";
// Insert your code for cleanup and error handling.
return -1;
}
// Use SetCommandText() to specify the command text.
if (FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString))) {
cout << "Failed to set command text.\n";
// Insert your code for cleanup and error handling.
return -1;
}
// Fetch columns 1-5 and then 6-10 and display the contents
if (FAILED(hresult = pICommandText->Execute(NULL, IID_IRow, NULL, &cNumRows, (IUnknown **) &pIRow))) {
cout << "Failed to execute command.\n";
// Insert your code for cleanup and error handling.
return -1;
}
hresult = GetColumns(pIRow, 1, 5);
hresult = GetColumns(pIRow, 6, 10);
hresult = pIRow->Release();
// Execute the command.
if (FAILED(hresult = pICommandText->Execute(NULL, IID_IRow, NULL, &cNumRows, (IUnknown **) &pIRow))) {
cout << "Failed to execute command.\n";
// Insert your code for cleanup and error handling.
return -1;
}
// Get columns
for ( iidx = 1 ; iidx <= 10 ; iidx++ ) {
if (FAILED(hresult = GetColumnSize(pIRow, iidx))) {
cout << "Failed to get column size.\n";
// Insert your code for cleanup and error handling.
return -1;
}
hresult = GetColumns(pIRow, iidx, iidx);
}
pIRow->Release();
// Release memory.
pICommandText->Release();
pIDBCreateCommand->Release();
pIDBCreateSession->Release();
if (FAILED(pIDBInitialize->Uninitialize())) {
// Uninitialize not required, but fails if an interface has not been released. Can be used for debugging.
cout << "Problem uninitializing.\n";
}
pIDBInitialize->Release();
// Release the COM library.
CoUninitialize();
return 0;
};
//--------------------------------------------------------------------
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).
DBLENGTH 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
void * p = pCol->pData = new WCHAR[ulSize + 1];
if (!(p /* pCol->pData = new WCHAR[ulSize + 1] */ ))
return FALSE;
// 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
DBORDINAL 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
if (!(prgColumns= new DBCOLUMNACCESS[cUserCols])) { // cUserCols is only 1
hr = E_FAIL;
goto CLEANUP;
}
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 occurred\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;
DBORDINAL 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 occurred\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_s(pwszStatus, 255, L"DBSTATUS_S_OK");
break;
case DBSTATUS_E_UNAVAILABLE:
wcscpy_s(pwszStatus, 255, L"DBSTATUS_E_UNAVAILABLE");
break;
case DBSTATUS_S_TRUNCATED:
wcscpy_s(pwszStatus, 255, L"DBSTATUS_S_TRUNCATED");
break;
}
return TRUE;
}
ULONG PrintData(ULONG iCols, ULONG iStart, DBCOLUMNINFO* prgInfo, DBCOLUMNACCESS* prgColumns) {
WCHAR wszStatus[255];
DBCOLUMNINFO* pCurrInfo;
DBCOLUMNACCESS* pCurrCol;
ULONG iidx;
printf("No. Name Status Length Max Data\n");
for ( iidx = 0 ; iidx < iCols ; iidx++ ) {
pCurrInfo = prgInfo + iidx + iStart - 1;
pCurrCol = prgColumns + iidx;
GetStatus(pCurrCol->dwStatus, wszStatus);
// was the data successfully retrieved?
wprintf(L"%-3d %-*s %-20s %-3d %-3d %-20s\n", iStart+iidx,
10,
pCurrInfo->pwszName,
wszStatus,
pCurrCol->cbDataLen,
pCurrCol->cbMaxLen,
(WCHAR*) pCurrCol->pData);
}
wprintf(L"\n");
return iidx;
}
int InitializeAndEstablishConnection() {
// Initialize the COM library.
CoInitialize(NULL);
// Obtain access to the MSOLEDBSQL provider.
hresult = CoCreateInstance(CLSID_MSOLEDBSQL,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void **) &pIDBInitialize);
if (FAILED(hresult)) {
printf("Failed to get IDBInitialize interface.\n");
// Insert your code for cleanup and error handling.
return -1;
}
// 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"(local)");
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"AdventureWorks");
InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[1].colid = DB_NULLID;
InitProperties[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal= SysAllocString(L"SSPI");
InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[2].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.
hresult = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if (FAILED(hresult)) {
cout << "Failed to get IDBProperties interface.\n";
// Insert your code for cleanup and error handling.
return -1;
}
hresult = pIDBProperties->SetProperties(1, rgInitPropSet);
if (FAILED(hresult)) {
cout << "Failed to set initialization properties.\n";
// Insert your code for cleanup and error handling.
return -1;
}
pIDBProperties->Release();
// Now establish the connection to the data source.
if (FAILED(pIDBInitialize->Initialize()))
cout << "Problem establishing connection to the data source.\n";
return 0;
}
use AdventureWorks2022;
go
if exists (select name from sysobjects where name = 'MyTable')
drop table MyTable
go