通过

使用 IRow::GetColumns 提取列 (OLE DB)

  • 项目

通过 IRow 接口可以直接访问结果集中某一行的列。 因而，IRow 是一种从具有一行的结果集中检索列的有效方法。

所提供的代码示例显示如何使用 IRow 提取单一行。 在本示例中，将一次从该行中检索一列。 此示例说明：

  • 如何提取一组列（依次）。

  • 如何两次访问某一列。 第一次获取实际列宽度，稍后访问实际数据。 在 DBCOLUMNACCESS 结构中，如果 pData 为 NULL 且 cbMaxLen 为 0，则对于 IRow->GetColumns() 的调用只返回实际列长度。 在这种情况下，可以再次对同一列调用 IRow->GetColumns()，以检索实际数据。

安全说明安全说明

请尽可能使用 Windows 身份验证。 如果 Windows 身份验证不可用，请在运行时提示用户输入其凭据。 不要将凭据存储在一个文件中。 如果必须保存凭据，应当用 Win32 crypto API（Win32 加密 API）加密它们。

使用 IRow::GetColumns 提取列

  1. 建立与数据源的连接。

  2. 执行命令（在以下示例中，将通过 IID_IRow 调用 ICommandExecute::Execute()）。

  3. 执行 IRow::GetColumns() 以提取结果行中的一列或多列。 如果您要在提取数据之前查找实际列大小，请将 DBCOLUMNACCESS 中的 pData 设置为 NULL。 对于 IRow::GetColumns() 的这一调用只返回列宽度。 再次调用 IRow::GetColumns() 将提取数据。

  4. 执行 IRow::GetColumns()，直到访问您需要的所有列。 必须依次访问这些列。

示例

此示例显示如何使用 IRow 接口实现对结果集中某一行的列的直接访问。 该示例演示：

  • 如何依次提取一组列。

  • 如何两次访问某列 - 第一次获取实际列宽，稍后访问实际数据。

在 DBCOLUMNACCESS 结构中，如果 pData 为 NULL 且 cbMaxLen 为 0，则对于 IRow->GetColumns 的调用只返回实际列长度。 在这种情况下，可以再次对同一列调用 IRow->GetColumns 以检索实际数据。 IA64 平台不支持此示例。

此示例要求使用 AdventureWorks 示例数据库，可以从 Microsoft SQL Server Samples and Community Projects（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 环境变量包括含有 sqlncli.h 的目录。

第三个 (Transact-SQL) 代码列表删除该示例使用的表。

use AdventureWorks
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 <sqlncli.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 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;
   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 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_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 SQLNCLI provider.
   hresult = CoCreateInstance(CLSID_SQLNCLI11,
                              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 AdventureWorks
go

if exists (select name from sysobjects where name = 'MyTable')
     drop table MyTable
go

请参阅

其他资源

OLE DB 操作指南主题