Mendapatkan Kursor FAST_FORWARD (penyedia OLE DB Klien Asli)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)
Untuk mendapatkan kursor baca-saja, atur properti set baris DBPROP_SERVERCURSOR, DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, dan DBPROP_OWNUPDATEDELETE ke VARIANT_TRUE.
Sampel lengkap menunjukkan cara mengatur properti kumpulan baris untuk mendapatkan kursor FAST_FORWARD. Setelah properti diatur, pernyataan SELECT dijalankan untuk mengambil dan menampilkan kolom Nama tabel Purchasing.Vendor di database AdventureWorks.
Penting
Jika memungkinkan, gunakan Autentikasi Windows. Jika Autentikasi Windows tidak tersedia, minta pengguna untuk memasukkan kredensial mereka pada waktu proses. Hindari menyimpan kredensial dalam file. Jika Anda harus mempertahankan kredensial, Anda harus mengenkripsinya dengan API kripto Win32.
Untuk mendapatkan kursor FAST_FORWARD
Buat koneksi ke sumber data.
Atur properti set baris DBPROP_SERVERCURSOR, DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, dan DBPROP_OWNUPDATEDELETE ke VARIANT_TRUE.
Jalankan perintah .
Contoh
Contoh berikut menunjukkan cara mengatur properti kumpulan baris untuk mendapatkan kursor FAST_FORWARD. Setelah properti diatur, pernyataan SELECT dijalankan untuk mengambil dan menampilkan kolom Nama tabel Purchasing.Vendor di database AdventureWorks. Sampel ini tidak didukung pada IA64.
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
Kompilasi dengan ole32.lib oleaut32.lib dan jalankan daftar kode C++ berikut. Aplikasi ini tersambung ke instans SQL Server default komputer Anda. Pada beberapa sistem operasi Windows, Anda harus mengubah (localhost) atau (lokal) ke nama instans SQL Server Anda. Untuk menyambungkan ke instans bernama, ubah string koneksi dari L"(local)" ke L"(local)\\name", di mana nama adalah instans bernama. Secara default, SQL Server Express menginstal ke instans bernama. Pastikan variabel lingkungan INCLUDE Anda menyertakan direktori yang berisi 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();
}
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk