Jalankan Prosedur Tersimpan Klien Asli SQL Server dengan PANGGILAN ODBC dan Output Proses
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)
Prosedur tersimpan SQL Server dapat memiliki kode pengembalian bilangan bulat dan parameter output. Kode pengembalian dan parameter output dikirim dalam paket terakhir dari server dan oleh karena itu tidak tersedia untuk aplikasi sampai set baris benar-benar dirilis. Jika perintah mengembalikan beberapa hasil, data parameter output tersedia saat IMultipleResults::GetResult mengembalikan DB_S_NORESULT atau antarmuka IMultipleResults sepenuhnya dirilis, mana pun yang terjadi terlebih dahulu.
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 memproses kode pengembalian dan parameter output
Buat pernyataan SQL yang menggunakan urutan escape ODBC CALL. Pernyataan harus menggunakan penanda parameter untuk setiap parameter input/output dan output, dan untuk nilai pengembalian prosedur (jika ada). Untuk parameter input, Anda dapat menggunakan penanda parameter, atau kode keras nilai.
Buat serangkaian pengikatan (satu untuk setiap pembuat parameter) dengan menggunakan array struktur DBBINDING.
Buat aksesor untuk parameter yang ditentukan dengan menggunakan metode IAccessor::CreateAccessor . CreateAccessor membuat aksesor dari sekumpulan pengikatan.
Isi struktur DBPARAMS.
Panggil perintah Jalankan (dalam hal ini, panggilan ke prosedur tersimpan).
Proses set baris dan rilis dengan menggunakan metode IRowset::Release .
Proses kode pengembalian dan nilai parameter output yang diterima dari prosedur tersimpan.
Contoh
Contoh menunjukkan pemrosesan set baris, kode pengembalian, dan parameter output. Kumpulan hasil tidak diproses. Sampel ini tidak didukung pada IA64.
Artikel ini memerlukan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
Jalankan daftar kode pertama (Transact-SQL) untuk membuat prosedur tersimpan yang digunakan oleh aplikasi.
Kompilasi dengan ole32.lib oleaut32.lib dan jalankan daftar kode kedua (C++). 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.
Jalankan daftar kode ketiga (Transact-SQL) untuk menghapus prosedur tersimpan yang digunakan oleh aplikasi.
USE AdventureWorks2022;
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[myProc]'))
DROP PROCEDURE myProc
GO
CREATE PROCEDURE myProc
@inparam nvarchar(5),,
@outparam int OUTPUT
AS
SELECT Color, ListPrice
FROM Production.Product WHERE Size > @inparam
SELECT @outparam = 100
IF (@outparam > 0)
RETURN 999
ELSE
RETURN 888
GO
// compile with: ole32.lib oleaut32.lib
void InitializeAndEstablishConnection();
#define UNICODE
#define DBINITCONSTANTS
#define INITGUID
#define OLEDBVER 0x0250 // to include correct interfaces
#include <windows.h>
#include <stdio.h>
#include <stddef.h>
#include <iostream>
#include <oledb.h>
#include <oledberr.h>
#include <SQLNCLI.h>
using namespace std;
IDBInitialize* pIDBInitialize = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
ICommandWithParameters* pICommandWithParams = NULL;
IAccessor* pIAccessor = NULL;
IDBProperties* pIDBProperties = NULL;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
const ULONG nInitProps = 4;
DBPROP InitProperties[nInitProps];
const ULONG nPropSet = 1;
DBPROPSET rgInitPropSet[nPropSet];
HRESULT hr;
HACCESSOR hAccessor;
const ULONG nParams = 3; // Number of parameters in the command
DBPARAMBINDINFO ParamBindInfo[nParams];
ULONG i;
ULONG cbColOffset = 0;
ULONG ParamOrdinals[nParams];
DBROWCOUNT cNumRows = 0;
DBPARAMS Params;
// Declare an array of DBBINDING structures, one for each parameter in the command.
DBBINDING acDBBinding[nParams];
DBBINDSTATUS acDBBindStatus[nParams];
// The following buffer is used to store parameter values.
typedef struct tagSPROCPARAMS {
long lReturnValue;
long outParam;
long inParam;
} SPROCPARAMS;
int main() {
// The command to execute.
WCHAR* wCmdString = L"{? = call myProc(?,?)}";
SPROCPARAMS sprocparams = {0,0,14};
// All the initialization activities in a separate function.
InitializeAndEstablishConnection();
// Create a new activity from the data source object.
if ( FAILED(pIDBInitialize->QueryInterface( IID_IDBCreateSession,
(void**) &pIDBCreateSession))) {
cout << "Failed to access IDBCreateSession interface.\n";
goto EXIT;
}
if (FAILED(pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand))) {
cout << "pIDBCreateSession->CreateSession failed.\n";
goto EXIT;
}
// Create a Command object.
if (FAILED(pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText))) {
cout << "Failed to access ICommand interface.\n";
goto EXIT;
}
// Set the command text.
if (FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString))) {
cout << "Failed to set command text.\n";
goto EXIT;
}
// No need to describe command parameters (parameter name, data type
// etc) in DBPARAMBINDINFO structure and then SetParameterInfo(). The
// provider obtains this information by calling appropriate helper
// function.
// Describe the consumer buffer by filling in the array of DBBINDING structures.
// Each binding associates a single parameter to the consumer's buffer.
for ( i = 0 ; i < nParams ; i++ ) {
acDBBinding[i].obLength = 0;
acDBBinding[i].obStatus = 0;
acDBBinding[i].pTypeInfo = NULL;
acDBBinding[i].pObject = NULL;
acDBBinding[i].pBindExt = NULL;
acDBBinding[i].dwPart = DBPART_VALUE;
acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
acDBBinding[i].dwFlags = 0;
acDBBinding[i].bScale = 0;
} // end for
acDBBinding[0].iOrdinal = 1;
acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
acDBBinding[0].cbMaxLen = sizeof(long);
acDBBinding[0].wType = DBTYPE_I4;
acDBBinding[0].bPrecision = 11;
acDBBinding[1].iOrdinal = 2;
acDBBinding[1].obValue = offsetof(SPROCPARAMS, inParam);
acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
acDBBinding[1].cbMaxLen = sizeof(long);
acDBBinding[1].wType = DBTYPE_I4;
acDBBinding[1].bPrecision = 11;
acDBBinding[2].iOrdinal = 3;
acDBBinding[2].obValue = offsetof(SPROCPARAMS, outParam);
acDBBinding[2].eParamIO = DBPARAMIO_OUTPUT;
acDBBinding[2].cbMaxLen = sizeof(long);
acDBBinding[2].wType = DBTYPE_I4;
acDBBinding[2].bPrecision = 11;
// Create an accessor from the above set of bindings.
hr = pICommandText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);
if (FAILED(hr))
cout << "Failed to get IAccessor interface.\n";
hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA,
nParams,
acDBBinding,
sizeof(SPROCPARAMS),
&hAccessor,
acDBBindStatus);
if (FAILED(hr))
cout << "Failed to create accessor for the defined parameters.\n";
// Fill in DBPARAMS structure for the command execution. This structure
// specifies the parameter values in the command and is then passed to Execute.
Params.pData = &sprocparams;
Params.cParamSets = 1;
Params.hAccessor = hAccessor;
// Execute the command.
if ( FAILED(hr = pICommandText->Execute( NULL,
IID_IRowset,
&Params,
&cNumRows,
(IUnknown **) &pIRowset))) {
cout << "Failed to execute command.\n";
goto EXIT;
}
printf("After command execution but before rowset processing.\n\n");
printf(" Return value = %d\n", sprocparams.lReturnValue);
printf(" Output parameter value = %d\n", sprocparams.outParam);
printf(" These are the same default values set in the application.\n\n\n");
// Result set is not important in this example; release it without processing.
pIRowset->Release();
printf("After processing the result set...\n");
printf(" Return value = %d\n", sprocparams.lReturnValue);
printf(" Output parameter value = %d\n\n", sprocparams.outParam);
// Release memory.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
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();
return 0;
EXIT:
if (pIAccessor != NULL)
pIAccessor->Release();
if (pICommandText != NULL)
pICommandText->Release();
if (pIDBCreateCommand != NULL)
pIDBCreateCommand->Release();
if (pIDBCreateSession != NULL)
pIDBCreateSession->Release();
if (pIDBInitialize != NULL)
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 in uninitializing.\n";
pIDBInitialize->Release();
CoUninitialize();
};
void InitializeAndEstablishConnection() {
// Initialize the COM library.
CoInitialize(NULL);
// Obtain access to the SQL Server Native Client OLE DB provider.
hr = CoCreateInstance( CLSID_SQLNCLI11,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void **) &pIDBInitialize);
if (FAILED(hr))
cout << "Failed in CoCreateInstance().\n";
// Initialize the property values needed to establish the connection.
for ( i = 0 ; i < nInitProps ; i++ )
VariantInit(&InitProperties[i].vValue);
// Specify server name.
InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[0].vValue.vt = VT_BSTR;
// Replace "MySqlServer" with proper value.
InitProperties[0].vValue.bstrVal = SysAllocString(L"(local)");
InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[0].colid = DB_NULLID;
// Specify database name.
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 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.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if (FAILED(hr))
cout << "Failed to obtain IDBProperties interface.\n";
hr = pIDBProperties->SetProperties(nPropSet, rgInitPropSet);
if (FAILED(hr))
cout << "Failed to set initialization properties.\n";
pIDBProperties->Release();
// Now establish a connection to the data source.
if (FAILED(pIDBInitialize->Initialize()))
cout << "Problem in initializing.\n";
}
USE AdventureWorks2022;
DROP PROCEDURE myProc
GO
Lihat Juga
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