取得 FAST_FORWARD 資料指標

  • 發行項

若要取得順向的唯讀資料指標，將資料列集屬性 DBPROP_SERVERCURSOR、DBPROP_OTHERINSERT、DBPROP_OTHERUPDATEDELETE、DBPROP_OWNINSERT 和 DBPROP_OWNUPDATEDELETE 設為 VARIANT_TRUE。

完整的範例示範如何設定資料列集屬性，以取得 FAST_FORWARD 資料指標。 在設定屬性之後，會執行 SELECT 陳述式，擷取並顯示 AdventureWorks 資料庫中 Purchasing.Vendor 資料表的 Name 資料行。

安全性注意事項安全性注意事項

盡可能使用 Windows 驗證。 如果無法使用 Windows 驗證，請提示使用者在執行階段輸入認證。 請避免將認證儲存在檔案中。 如果您必須保存認證，則應該用 Win32 crypto API 加密這些認證。

取得 FAST_FORWARD 資料指標

  1. 建立資料來源的連接。

  2. 將資料列集屬性 DBPROP_SERVERCURSOR、DBPROP_OTHERINSERT、DBPROP_OTHERUPDATEDELETE、DBPROP_OWNINSERT 和 DBPROP_OWNUPDATEDELETE 設為 VARIANT_TRUE。

  3. 執行命令。

範例

以下範例顯示如何設定資料列集屬性，以取得 FAST_FORWARD 資料指標。 在設定屬性之後，會執行 SELECT 陳述式，擷取並顯示 AdventureWorks 資料庫中 Purchasing.Vendor 資料表的 Name 資料行。 IA64 不支援此範例。

此範例需要 AdventureWorks 範例資料庫，您可以從 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 環境變數包含的目錄內含 sqlncli.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 <sqlncli.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 SQL Server Native Client OLE DB Provider.
   if ( FAILED( hr = 
      CoCreateInstance(CLSID_SQLNCLI11, 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();
}