Export (0) Print
Expand All

Parameters

SQL Server 2000

Parameters

Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) supports queries that contain parameters. Parameters can be used to replace column values in a query. You can get information about the parameters of a command, such as their data types, by using the ICommandWithParameters::GetParameterInfo. Parameters are then passed when ICommand::Execute is called.

Note  The ICommandWithParameters::SetParameterInfo method should not be used to change the data types of parameters. Even when the type for a parameter is set using SetParameterInfo, the query processor in SQL Server CE makes the final decision as to the correct type, potentially overriding any changes.

For more information, see Using Parameters in Queries.

Examples

The following example method uses OLE DB to insert a new row into the Northwind Shippers table:

// Begin 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;
ULONG                    cParams;
DBPARAMINFO *            rgParamInfo       = NULL;
OLECHAR *                pNamesBuffer      = NULL;
ULONG                    cBindings;
DBBINDING                rgBindings[3];
ULONG                    cbRowSize;
HACCESSOR                hAcc;
BYTE *                   pData             = NULL;
DBPARAMS                 params;
LONG                     cRowsAffected;

// Specify the Northwind database as wzDbName.
WCHAR  wzDbName[]  = L"\\windows\\Northwind.sdf";


// Initialize the property set values.and create the data source object.
for (ULONG i = 0; i < sizeof(rgProps)/sizeof(rgProps[0]); i++)
{
    VariantInit(&rgProps[i].vValue);
}

hr = CoCreateInstance(CLSID_SQLSERVERCE_2_0, 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(ULONG);
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(ULONG);
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(ULONG);
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;
* (ULONG*)   (pData + rgBindings[0].obLength) = sizeof(int);

// CompanyName
* (DBSTATUS*) (pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;
wcscpy((WCHAR*) (pData + rgBindings[1].obValue), L"Federal Express");
* (int*)   (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");
* (int*)   (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,"An error occured","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;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft