Параметры OLE DB (SQL Server Compact)
MicrosoftSQL Server Compact 3.5 поддерживает запросы, содержащие параметры. Параметры могут использоваться для замены значений столбцов в запросе.
Использование параметров
Сведения о параметрах той или иной команды, например о типах данных, можно получить с помощью ICommandWithParameters::GetParameterInfo. Затем параметры передаются при вызове ICommand::Execute.
Примечание
Метод ICommandWithParameters::SetParameterInfo не должен использоваться для изменения типов данных параметров. Даже когда тип параметра задан с помощью SetParameterInfo, обработчик запросов в SQL Server Compact 3.5 делает окончательный выбор правильного типа и может отменить любые изменения.
Примеры
В приведенном примере метод использует OLE DB для вставки новой строки в таблицу Shippers. Обратите внимание, что этот пример можно скомпилировать и запустить как на 32-разрядной, так и на 64-разрядной платформах. Дополнительные сведения см. в разделе, посвященном работе с OLE DB в 64-разрядных версиях, в руководстве разработчика по OLE DB на веб-сайте Майкрософт.
// Start the method to insert data using parameters.
// Declarations
HRESULT hr;
IDBInitialize * pIDBInitialize = NULL;
IDBProperties * pIDBProperties = NULL;
IDBCreateSession * pIDBCrtSession = NULL;
DBPROPSET rgPropSets[1];
DBPROP rgProps[1];
ULONG iPropSet = 0;
ULONG iProp = 0;
ICommandText * pICmdText = NULL;
IDBCreateCommand * pIDBCrtCmd = NULL;
ICommandPrepare * pICmdPrepare = NULL;
ICommandWithParameters * pICmdWParams = NULL;
IAccessor * pIAcc = NULL;
DB_UPARAMS cParams;
DBPARAMINFO * rgParamInfo = NULL;
OLECHAR * pNamesBuffer = NULL;
DBCOUNTITEM cBindings;
DBBINDING rgBindings[3];
DBBYTEOFFSET cbRowSize;
HACCESSOR hAcc;
BYTE * pData = NULL;
DBPARAMS params;
DBROWCOUNT cRowsAffected;
// Specify the MyDB database as wzDbName.
WCHAR wzDbName[] = L"\\windows\\MyDB.sdf";
ULONG i;
// Initialize the property set values.and create the data source object.
for (i = 0; i < sizeof(rgProps)/sizeof(rgProps[0]); i++)
{
VariantInit(&rgProps[i].vValue);
}
hr = CoCreateInstance(CLSID_SQLSERVERCE, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (LPVOID *) &pIDBInitialize);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
iProp = 0;
rgProps[iProp].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProps[iProp].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[iProp].vValue.vt = VT_BSTR;
rgProps[iProp].vValue.bstrVal = SysAllocString(wzDbName);
if(!(rgProps[iProp].vValue.bstrVal))
{
hr = E_OUTOFMEMORY;
goto Exit;
}
iProp++;
iPropSet = 0;
rgPropSets[iPropSet].rgProperties = rgProps;
rgPropSets[iPropSet].cProperties = iProp;
rgPropSets[iPropSet].guidPropertySet = DBPROPSET_DBINIT;
iPropSet++;
// Set the properties into the provider's data source object.
pIDBInitialize->QueryInterface(IID_IDBProperties,(void**)&pIDBProperties);
hr = pIDBProperties->SetProperties(sizeof(rgPropSets)/sizeof(rgPropSets[iPropSet]),
rgPropSets);
if(FAILED(hr))
{
goto Exit;
}
// Create a session that supports commands.
hr = pIDBProperties->QueryInterface(IID_IDBCreateSession, (void **)
&pIDBCrtSession);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
hr = pIDBCrtSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCrtCmd);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Create the new command that uses parameters.
hr = pIDBCrtCmd->CreateCommand(NULL, IID_ICommandWithParameters,
(IUnknown**) &pICmdWParams);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
hr = pICmdWParams->QueryInterface(IID_ICommandText, (void**) &pICmdText);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
hr = pICmdWParams->QueryInterface(IID_ICommandPrepare, (void**) &pICmdPrepare);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Specify the command text using parameter markers in the query syntax.
hr = pICmdText->SetCommandText(DBGUID_DBSQL, L"INSERT INTO Shippers \
(ShipperID, CompanyName, Phone) VALUES (?, ?, ?)");
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Prepare the current command.
hr = pICmdPrepare->Prepare(1);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Retrieving parameter information
hr = pICmdWParams->GetParameterInfo(&cParams, &rgParamInfo, &pNamesBuffer);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Create the acessor object and column specific bindings.
hr = pICmdText->QueryInterface(IID_IAccessor, (void**) &pIAcc);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Create the bindings for the three columns.
cBindings = 3;
rgBindings[0].iOrdinal = 1;
rgBindings[0].obStatus = 0;
rgBindings[0].obLength = rgBindings[0].obStatus + sizeof(DBSTATUS);
rgBindings[0].obValue = rgBindings[0].obLength + sizeof(DBLENGTH);
rgBindings[0].pTypeInfo = NULL;
rgBindings[0].pObject = NULL;
rgBindings[0].pBindExt = NULL;
rgBindings[0].dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[0].eParamIO = DBPARAMIO_INPUT;
rgBindings[0].cbMaxLen = sizeof(int); //ShipperID is integer
rgBindings[0].dwFlags = 0;
rgBindings[0].wType = DBTYPE_I4;
rgBindings[0].bPrecision = 0;
rgBindings[0].bScale = 0;
rgBindings[1].iOrdinal = 2;
rgBindings[1].obStatus = rgBindings[0].obValue + rgBindings[0].cbMaxLen;
rgBindings[1].obLength = rgBindings[1].obStatus + sizeof(DBSTATUS);
rgBindings[1].obValue = rgBindings[1].obLength + sizeof(DBLENGTH);
rgBindings[1].pTypeInfo = NULL;
rgBindings[1].pObject = NULL;
rgBindings[1].pBindExt = NULL;
rgBindings[1].dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[1].eParamIO = DBPARAMIO_INPUT;
rgBindings[1].cbMaxLen = 40 * sizeof(WCHAR); //CompanyName is nvarchar(40)
rgBindings[1].dwFlags = 0;
rgBindings[1].wType = DBTYPE_WSTR;
rgBindings[1].bPrecision = 0;
rgBindings[1].bScale = 0;
rgBindings[2].iOrdinal = 3;
rgBindings[2].obStatus = rgBindings[1].obValue + rgBindings[1].cbMaxLen;
rgBindings[2].obLength = rgBindings[2].obStatus + sizeof(DBSTATUS);
rgBindings[2].obValue = rgBindings[2].obLength + sizeof(DBLENGTH);
rgBindings[2].pTypeInfo = NULL;
rgBindings[2].pObject = NULL;
rgBindings[2].pBindExt = NULL;
rgBindings[2].dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
rgBindings[2].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBindings[2].eParamIO = DBPARAMIO_INPUT;
rgBindings[2].cbMaxLen = 24 * sizeof(WCHAR); //Phone is nvarchar(24)
rgBindings[2].dwFlags = 0;
rgBindings[2].wType = DBTYPE_WSTR;
rgBindings[2].bPrecision = 0;
rgBindings[2].bScale = 0;
// Calculate the total memory needed for the input buffer.
cbRowSize = rgBindings[2].obValue + rgBindings[2].cbMaxLen;
// Create the accessor for the parameter data.
hr = pIAcc->CreateAccessor(DBACCESSOR_PARAMETERDATA, cBindings,
rgBindings, cbRowSize, &hAcc, NULL);
if (FAILED(hr))
{
//Send an error-specific message and do error handling.
goto Exit;
}
// Allocate memory for the parameter data.
pData = (BYTE*) malloc(cbRowSize);
if(!(pData))
{
hr = E_OUTOFMEMORY;
goto Exit;
}
//Clear out the buffer.
memset(pData, 0, cbRowSize);
// Define the insert data for the parameters.
// Shipper ID
* (DBSTATUS*) (pData + rgBindings[0].obStatus) = DBSTATUS_S_OK;
* (int*) (pData + rgBindings[0].obValue) = 1;
* (DBLENGTH*) (pData + rgBindings[0].obLength) = sizeof(int);
// CompanyName
* (DBSTATUS*) (pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;
wcscpy((WCHAR*) (pData + rgBindings[1].obValue), L"Federal Express");
* (DBLENGTH*) (pData + rgBindings[1].obLength) = wcslen(L"Federal Express") * sizeof(WCHAR);
// Phone
* (DBSTATUS*) (pData + rgBindings[2].obStatus) = DBSTATUS_S_OK;
wcscpy((WCHAR*) (pData + rgBindings[2].obValue), L"1-800-555-1212");
* (DBLENGTH*) (pData + rgBindings[2].obLength) = wcslen(L"1-800-555-1212") * sizeof(WCHAR);
// Define the DBPARAMS structure.
params.pData = pData;
params.cParamSets = 1;
params.hAccessor = hAcc;
// Execute the command with paramters.
hr = pICmdText->Execute(NULL, IID_NULL, ¶ms, &cRowsAffected, NULL);
// Error handling for the command
if (FAILED(hr) || (1 != cRowsAffected))
{
MessageBox(NULL,L"An error occurred",L"error",MB_OK);
}
Exit:
// Clean up resources.
free(pData);
CoTaskMemFree(rgParamInfo);
CoTaskMemFree(pNamesBuffer);
if(pIAcc) pIAcc->Release();
if(pIDBProperties) pIDBProperties->Release();
if(pIDBCrtSession) pIDBCrtSession->Release();
if(pICmdPrepare) pICmdPrepare->Release();
if(pICmdWParams) pICmdWParams->Release();
if(pICmdText) pICmdText->Release();
if(pIDBCrtCmd) pIDBCrtCmd->Release();
for(i = 0; i < sizeof(rgProps)/sizeof(rgProps[0]); i++)
{
VariantClear(&rgProps[i].vValue);
}
return;