获取 FAST_FORWARD 游标
适用于：
SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)
若要获取只进只读游标，请将行集属性 DBPROP_SERVERCURSOR、DBPROP_OTHERINSERT、DBPROP_OTHERUPDATEDELETE、DBPROP_OWNINSERT 和 DBPROP_OWNUPDATEDELETE 设置为 VARIANT_TRUE。
本文提供了一个完整示例，说明如何通过设置行集属性来获取 FAST_FORWARD 游标。 设置属性后，执行 SELECT 语句，以检索并显示 AdventureWorks 数据库中 Purchasing.Vendor 表的 Name 列 。
重要
请尽可能使用 Windows 身份验证。 如果 Windows 身份验证不可用，请在运行时提示用户输入其凭据。 不要将凭据存储在一个文件中。 如果必须保存凭据，应当用 Win32 crypto API（Win32 加密 API）加密它们。
获取 FAST_FORWARD 游标
建立与数据源的连接。
将行集属性 DBPROP_SERVERCURSOR、DBPROP_OTHERINSERT、DBPROP_OTHERUPDATEDELETE、DBPROP_OWNINSERT 和 DBPROP_OWNUPDATEDELETE 设置为 VARIANT_TRUE。
执行命令。
示例
以下示例显示如何通过设置行集属性来获取 FAST_FORWARD 游标。 设置完属性后，将执行 SELECT 语句以检索并显示 AdventureWorks 数据库中 Purchasing.Vendor 表的 Name 列。 IA64 平台不支持此示例。
本文中的 Transact-SQL 代码示例使用AdventureWorks2022示例数据库，可以从 Microsoft SQL Server 示例和社区项目主页下载该数据库。
使用 ole32.lib 和 oleaut32.lib 编译并执行以下 C++ 代码列表。 此应用程序连接到您的计算机上默认的 SQL Server 实例。 在某些 Windows 操作系统上，您需要将 (localhost) 或 (local) 更改为您的 SQL Server 实例的名称。 若要连接到命名实例，请将连接字符串从 L"(local)" 更改为 L"(local)\\name"，其中 name 是命名实例。 默认情况下，SQL Server Express 安装在命名实例中。 请确保 INCLUDE 环境变量包括含有 msoledbsql.h 的目录。
// compile with: ole32.lib oleaut32.lib
#define INITGUID
#define DBINITCONSTANTS
#define OLEDBVER 0x0250 // to include correct interfaces
#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <msoledbsql.h>
#include <oledberr.h>
IDBInitialize* pIDBInitialize = NULL;
ICommandText* pICommandText = NULL;
// Connect to the server and create a command object.
int InitializeAndConnect();
// Set the properties to get a FAST_FORWARD cursor.
int SetRowsetProperties();
// This function executes a command and displays the results.
int ExecuteAndDisplay();
// Release memory.
void Cleanup();
int main() {
if (InitializeAndConnect() == -1) {
// Handle error.
printf("Failed to initialize and connect to the server.\n");
return -1;
}
// Set the row properties to FAST_FORWARD cursor.
if (SetRowsetProperties() == -1) {
// Handle error.
printf("Failed to set the rowset properties.\n");
return -1;
}
// Execute a command and display the results.
if (ExecuteAndDisplay() == -1) {
// Handle error.
printf("Failed to execute a command and display the results.\n");
return -1;
}
Cleanup();
}
int InitializeAndConnect() {
HRESULT hr = S_OK;
IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
DBPROPSET dbPropSet;
DBPROP dbProp[4];
int iRetVal = 0;
// Initialize OLE
if ( FAILED( hr = OleInitialize( NULL ) ) ) {
// Handle errors here.
return -1;
}
// Create an instance of Microsoft OLE DB Driver for SQL Server.
if ( FAILED( hr =
CoCreateInstance(CLSID_MSOLEDBSQL, NULL, CLSCTX_INPROC_SERVER, IID_IDBProperties, (void **) &pIDBProperties ))) {
// Handle errors here.
return -1;
}
// Set up the connection properties.
dbProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[0].vValue)) = SysAllocString( L"(local)" );
dbProp[1].dwPropertyID = DBPROP_AUTH_INTEGRATED;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[1].vValue)) = SysAllocString( L"SSPI" );
dbProp[2].dwPropertyID = DBPROP_NULLCOLLATION;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[2].vValue)) = SysAllocString( L"" );
dbProp[3].dwPropertyID = DBPROP_INIT_CATALOG;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[3].vValue)) = SysAllocString( L"AdventureWorks" );
dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 4;
dbPropSet.guidPropertySet = DBPROPSET_DBINIT;
if ( FAILED( hr = pIDBProperties->SetProperties( 1, &dbPropSet ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
SysFreeString( V_BSTR(&(dbProp[0].vValue)) );
SysFreeString( V_BSTR(&(dbProp[1].vValue)) );
SysFreeString( V_BSTR(&(dbProp[2].vValue)) );
SysFreeString( V_BSTR(&(dbProp[3].vValue)) );
// Get an IDBInitialize interface.
if ( FAILED( hr =
pIDBProperties->QueryInterface( IID_IDBInitialize, (void **) &pIDBInitialize ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
// Call Initialize.
if ( FAILED( hr = pIDBInitialize->Initialize())) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
// Get a IDBCreateSession interface.
if ( FAILED( hr =
pIDBInitialize->QueryInterface( IID_IDBCreateSession, (void **) &pIDBCreateSession ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
// Create a session
if ( FAILED( hr =
pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, (IUnknown **) &pIDBCreateCommand))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
// Create a command.
if ( FAILED( hr =
pIDBCreateCommand->CreateCommand( NULL, IID_ICommandText, (IUnknown **) &pICommandText))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}
CLEANUP:
// Release all the objects not needed anymore.
pIDBProperties->Release();
if ( pIDBCreateSession )
pIDBCreateSession->Release();
if ( pIDBCreateCommand )
pIDBCreateCommand->Release();
return iRetVal;
}
int SetRowsetProperties() {
HRESULT hr = S_OK;
ICommandProperties* pICommandProperties = NULL;
DBPROPSET dbPropSet;
DBPROP dbProp[5];
int iRetVal = 0;
// Get an ICommandProperties object.
if ( FAILED( hr =
pICommandText->QueryInterface( IID_ICommandProperties, (void **) &pICommandProperties ))) {
// Handle errors here.
return -1;
}
// Set up the properties to get a FAST_FORWARD cursor.
dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[0].vValue)) = VARIANT_TRUE;
dbProp[1].dwPropertyID = DBPROP_OTHERINSERT;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[1].vValue)) = VARIANT_TRUE;
dbProp[2].dwPropertyID = DBPROP_OTHERUPDATEDELETE;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[2].vValue)) = VARIANT_TRUE;
dbProp[3].dwPropertyID = DBPROP_OWNINSERT;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[3].vValue)) = VARIANT_TRUE;
dbProp[4].dwPropertyID = DBPROP_OWNUPDATEDELETE;
dbProp[4].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[4].colid = DB_NULLID;
V_VT(&(dbProp[4].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[4].vValue)) = VARIANT_TRUE;
dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 5;
dbPropSet.guidPropertySet = DBPROPSET_ROWSET;
if ( FAILED( hr = pICommandProperties->SetProperties( 1, &dbPropSet))) {
// Handle errors here.
iRetVal = -1;
}
// Release the ICommandProperties object.
pICommandProperties->Release();
return iRetVal;
}
int ExecuteAndDisplay() {
HRESULT hr = S_OK;
IRowset* pIRowset = NULL;
IAccessor* pIAccessor = NULL;
BYTE* pData = NULL;
DBCOUNTITEM cRowsObtained = 0;
ULONG cCount = 0;
HROW* pRows = new HROW[10];
HACCESSOR hAccessor = 0;
DBBINDING Bind[1];
int iRetVal = 0;
if (!pRows)
return -1;
// Set the command text.
if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"select Name from Purchasing.Vendor")))
// Handle errors and free the memory here.
return -1;
// Execute the command.
if ( FAILED( hr = pICommandText->Execute( NULL, IID_IRowset, NULL, NULL, (IUnknown **) &pIRowset )))
// Handle errors and free the memory here.
return -1;
// Set up the binding structure for Name (nvarchar(50)).
Bind[0].dwPart = DBPART_VALUE;
Bind[0].eParamIO = DBPARAMIO_NOTPARAM;
Bind[0].iOrdinal = 1;
Bind[0].pTypeInfo = NULL;
Bind[0].pObject = NULL;
Bind[0].pBindExt = NULL;
Bind[0].dwFlags = 0;
Bind[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bind[0].obLength = 0;
Bind[0].obStatus = 0;
Bind[0].obValue = 0;
Bind[0].cbMaxLen = 102;
Bind[0].wType = DBTYPE_WSTR;
Bind[0].bPrecision = 0;
Bind[0].bScale = 0;
// Get an IAccessor interface.
if ( FAILED( hr = pIRowset->QueryInterface( IID_IAccessor, (void **) &pIAccessor))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
// Create an accessor.
if ( FAILED( hr =
pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, Bind, 0, &hAccessor, NULL))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
// Allocate memory for the data.
pData = new BYTE[102];
if (!(pData /* = new BYTE[102] */ )) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
// Loop through all of the rows.
for ( ; ; ) {
if (FAILED( hr = pIRowset->GetNextRows( NULL, 0, 10, &cRowsObtained, &pRows))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
// Make sure some rows were obtained.
if (cRowsObtained == 0)
break;
// Get the data for the each of the rows.
for ( cCount = 0 ; cCount < cRowsObtained ; cCount++ ) {
// Get the row data needed.
if ( FAILED( hr = pIRowset->GetData( pRows[cCount], hAccessor, pData ))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
// Display row data.
printf( "%S\n", pData);
}
// Release the rows.
if ( FAILED( hr = pIRowset->ReleaseRows(cRowsObtained, pRows, NULL, NULL, NULL ))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
}
CLEANUP:
// Release memory allocated for the data.
delete [] pRows;
delete [] pData;
// Release the HACCESSOR.
if (pIAccessor) {
pIAccessor->ReleaseAccessor( hAccessor, NULL );
// Release the IAccessor object.
pIAccessor->Release();
}
// Release the rowset.
pIRowset->Release();
return iRetVal;
}
void Cleanup() {
HRESULT hr = S_OK;
// Release the ICommandText object.
pICommandText->Release();
// Uninitialize the IDBInitialize object.
if ( FAILED( hr = pIDBInitialize->Uninitialize())) {
// Handle errors here.
}
// Release the IDBInitialize object.
pIDBInitialize->Release();
// Uninitialize OLE.
OleUninitialize();
}
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈