Parâmetros do OLE DB (SQL Server Compact)

O MicrosoftSQL Server Compact 4.0 dá suporte a consultas contendo parâmetros. Os parâmetros podem ser usados para substituir valores de coluna em uma consulta.

Usando parâmetros

Você poderá obter informações sobre os parâmetros de um comando, como seus tipos de dados, usando ICommandWithParameters::GetParameterInfo. Os parâmetros são passados quando ICommand::Execute é chamado.

Exemplos

O método do exemplo a seguir usa o OLE DB para inserir uma nova linha na tabela Shippers (Transportadoras). Observe que você pode compilar e executar este exemplo em plataformas de 32 e 64 bits. Para obter mais informações, consulte o tópico de Informações de 64 bits do OLE DB na Guia do Programador do OLE DB no site da Microsoft.

// 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, &params, &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;