When executing a SQL command with parameters, the OLE DB standard allows only for a single accessor handle to be provided by the consumer. This accessor handle is created to bind the command parameter data to a data buffer, just like in a normal rowset. By just having one accessor to handle bind the whole set of parameters means that we cannot bind more than one BLOB with SQL Compact, due to its OLE DB provider limitations. In order to have a general solution, we must bind the BLOB parameter as regular columns but we must know how much memory to allocate for each BLOB parameter, and for each instance of the command execution.
After some thought I concluded that there are two acceptable approaches:
- Ask the user to provide maximum acceptable size for each BLOB. This is what the ADO .NET provider does and essentially delegates this responsibility to the user. Do you know in advance what is the maximum size of each BLOB that you are going to feed as a parameter? If so this is acceptable, if not... The great advantage of this approach is that you can make an apriori parameter binding (and make it only once).
- Be smart about it and recreate the binding when needed. This requires keeping track of the current BLOB binding sizes and increasing the binding array when needed. This approach frees the user from having to specify a maximum size for the BLOB columns but requires that the binding be rebuilt when a larger BLOB is used.