Parámetros OLE DB (SQL Server Compact)
Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) admite consultas que contengan parámetros. Los parámetros se pueden utilizar para sustituir valores de columna en una consulta.
Usar parámetros
Puede obtener información acerca de los parámetros de un comando, por ejemplo sus tipos de datos, mediante ICommandWithParameters::GetParameterInfo. Los parámetros se pasan al llamar a ICommand::Execute.
Nota
No se debe utilizar el método ICommandWithParameters::SetParameterInfo para cambiar los tipos de datos de parámetros. Aunque el tipo de un parámetro se establezca mediante SetParameterInfo, el procesador de consultas de SQL Server Compact 3.5 tomará la decisión final acerca del tipo correcto, con lo que se podría reemplazar cualquier cambio.
Ejemplos
En el siguiente método de ejemplo se utiliza OLE DB para insertar una fila nueva en la tabla Shippers. Observe que puede compilar y ejecutar este ejemplo en plataformas de 32 bits y de 64 bits. Para obtener más información, vea el tema de información sobre OLE DB de 64 bits en la documentación de Visual Studio 2008 SP1.
// 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;