從結果集擷取資料列 （Native Client OLE DB Provider）
適用於：
SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)
此範例會示範如何從結果集提取資料列。 IA64 不支援此範例。
本文中的 Transact-SQL 程式代碼範例會使用AdventureWorks2022範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案首頁下載。
重要
盡可能使用 Windows 驗證。 如果無法使用 Windows 驗證，請提示使用者在執行階段輸入認證。 請避免將認證儲存在檔案中。 如果您必須保存認證，則應該用 Win32 crypto API 加密這些認證。
範例
描述
使用 ole32.lib oleaut32.lib 編譯並執行下列 C++ 程式碼清單。 此應用程式會連線至電腦的預設 SQL Server 執行個體。 在某些 Windows 作業系統上，您必須將 (localhost) 或 (local) 變更為 SQL Server 執行個體的名稱。 若要連線到具名執行個體，請將連接字串從 L"(local)" 變更為 L"(local)\\name"，其中 name 是具名執行個體。 根據預設，SQL Server Express 會安裝至具名執行個體。 請確定您的 INCLUDE 環境變數包含包含 sqlncli.h 的目錄。
程式碼
// compile with: ole32.lib oleaut32.lib
int InitializeAndEstablishConnection();
int ProcessResultSet();
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID
#define OLEDBVER 0x0250 // to include correct interfaces
#include <stdio.h>
#include <tchar.h>
#include <stddef.h>
#include <windows.h>
#include <iostream>
#include <oledb.h>
#include <sqlncli.h>
#include <msdadc.h> // for IDataConvert
#include <msdaguid.h> // for IDataConvert
using namespace std;
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;
DBROWCOUNT cNumRows = 0;
DBORDINAL lNumCols;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
DBLENGTH ConsumerBufColOffset = 0;
HACCESSOR hAccessor;
DBCOUNTITEM lNumRowsRetrieved;
HROW hRows[10];
HROW* pRows = &hRows[0];
int main() {
// The command to execute.
WCHAR* wCmdString = OLESTR("SELECT StandardCost, ListPrice FROM Production.Product WHERE ListPrice > 14.00");
// Call a function to initialize and establish connection.
if (InitializeAndEstablishConnection() == -1) {
// Handle error.
cout << "Failed to initialize and connect to the server.\n";
return -1;
}
// Create a session object.
if (FAILED(pIDBInitialize->QueryInterface( IID_IDBCreateSession,
(void**) &pIDBCreateSession))) {
cout << "Failed to obtain IDBCreateSession interface.\n";
// Handle error.
return -1;
}
if (FAILED(pIDBCreateSession->CreateSession( NULL,
IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand))) {
cout << "pIDBCreateSession->CreateSession failed.\n";
// Handle error.
return -1;
}
// Access the ICommandText interface.
if (FAILED(pIDBCreateCommand->CreateCommand( NULL,
IID_ICommandText,
(IUnknown**) &pICommandText))) {
cout << "Failed to access ICommand interface.\n";
// Handle error.
return -1;
}
// Use SetCommandText() to specify the command text.
if (FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString))) {
cout << "Failed to set command text.\n";
// Handle error.
return -1;
}
// Execute the command.
if (FAILED(hr = pICommandText->Execute( NULL,
IID_IRowset,
NULL,
&cNumRows,
(IUnknown **) &pIRowset))) {
cout << "Failed to execute command.\n";
// Handle error.
return -1;
}
// 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";
}
pIDBInitialize->Release();
CoUninitialize();
};
int InitializeAndEstablishConnection() {
CoInitialize(NULL);
// Obtain access to the SQLNCLI provider.
hr = CoCreateInstance( CLSID_SQLNCLI11,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void **) &pIDBInitialize);
if (FAILED(hr)) {
printf("Failed to get IDBInitialize interface.\n");
// Handle errors here.
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;
// Properties are set, now construct the DBPROPSET structure (rgInitPropSet) 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";
// Handle errors here.
return -1;
}
hr = pIDBProperties->SetProperties(1, rgInitPropSet);
if (FAILED(hr)) {
cout << "Failed to set initialization properties.\n";
// Handle errors here.
return -1;
}
pIDBProperties->Release();
// Now establish the connection to the data source.
if (FAILED(pIDBInitialize->Initialize())) {
cout << "Problem in establishing connection to the data"
"source.\n";
// Handle errors here.
return -1;
}
return 0;
}
// Retrieve and display data resulting from a query.
int 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";
// Handle errors here.
return -1;
}
// Retrieve the column information.
pIColumnsInfo->GetColumnInfo(&lNumCols, &pDBColumnInfo, &pStringsBuffer);
// Free the column information interface.
pIColumnsInfo->Release();
// Create a DBBINDING array.
DBBINDING * p = (pBindings = new DBBINDING[lNumCols]);
if (!(p /* pBindings = new DBBINDING[lNumCols] */ ))
return -1;
// 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].wType == DBTYPE_WSTR) ? pDBColumnInfo[j].ulColumnSize * 2 : 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 + pBindings[j].cbMaxLen;
};
// Get the IAccessor interface.
hr = pIRowset->QueryInterface(IID_IAccessor, (void **) &pIAccessor);
if (FAILED(hr)) {
cout << "Failed to obtain IAccessor interface.\n";
// Handle errors here.
return -1;
}
// 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);
printf("\n"); // new line after the column names
// Get a set of 10 rows.
pIRowset->GetNextRows( NULL, 0, 10, &lNumRowsRetrieved, &pRows);
// Allocate space for the row buffer.
BYTE * pBuffer = new BYTE[ConsumerBufColOffset];
if (!(pBuffer /* = new BYTE[ConsumerBufColOffset] */ )) {
// Free up all allocated memory.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pBindings;
return 0;
}
// Create an instance of the data conversion library to convert DBTYPE_CY to string for display
IDataConvert* pIDataConvert;
CoCreateInstance(CLSID_OLEDB_CONVERSIONLIBRARY,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDataConvert,
(void**)&pIDataConvert);
// variables used in DataConvert
DBLENGTH cbDstLength;
DBSTATUS dbsStatus;
char strCurrency0[25];
char strCurrency1[25];
// 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);
// Convert DBTYPE_CY values to string
pIDataConvert->DataConvert(DBTYPE_CY, // wSrcType
DBTYPE_STR, // wDstType
sizeof(LARGE_INTEGER), // cbSrcLength
&cbDstLength, // pcbDstLength
&pBuffer[pBindings[0].obValue], // pSrc
strCurrency0, // pDst
sizeof(strCurrency0), //cbDstMaxLength
DBSTATUS_S_OK, // dbsSrcStatus
&dbsStatus, // pdbsStatus
0, // bPrecision (used for DBTYPE_NUMERIC only)
0, // bScale (used for DBTYPE_NUMERIC only)
DBDATACONVERT_DEFAULT); // dwFlags
pIDataConvert->DataConvert(DBTYPE_CY, // wSrcType
DBTYPE_STR, // wDstType
sizeof(LARGE_INTEGER), // cbSrcLength
&cbDstLength, // pcbDstLength
&pBuffer[pBindings[1].obValue], // pSrc
strCurrency1, // pDst
sizeof(strCurrency1), // cbDstMaxLength
DBSTATUS_S_OK, // dbsSrcStatus
&dbsStatus, // pdbsStatus
0, // bPrecision (used for DBTYPE_NUMERIC only)
0, // bScale (used for DBTYPE_NUMERIC only)
DBDATACONVERT_DEFAULT); // dwFlags
// Print cost and price values.
printf("%-40s%s\n", strCurrency0, strCurrency1); //sparra
};
// 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);
}
// Free up all allocated memory.
delete [] pBuffer;
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pBindings;
return 0;
}
另請參閱
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應