BLOBs and SQL CE Command Parameters

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable-length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

You might already have read this on the MSDN help page for SqlCeCommand.Prepare. Have you ever wondered why you need to set the size of variable-length data types before preparing the command? The answer is easy: you cannot use a BLOB in a parameter. I have a strong belief that this is not necessarily so but, at most, you could use one BLOB type as a parameter.

This happens due to a limitation of SQL CE 2.0 and of OLE DB. As you may know, SQL CE 2.0 can only handle one storage object at a time. Storage objects such as ISequentialStream are used to copy BLOB data between the provider and the consumer. One of the workarounds for this is to use more than one accessor handle per row. I described this in two articles:
Managing Blobs Using the ATL OLE DB Consumer Templates
and
Inserting non-nullable BLOB data in SQL CE using the OLE DB Consumer Templates
This solution breaks down with the current OLE DB implementation. As it stands, ICommand::Execute only takes one accessor handle for all parameters in the DBPARAMS structure. So this means no BLOB command parameters.

The workaround Microsoft devised is actually the only one possible - preset the parameter size before preparing the command.