It's not a bug...

... it's a feature! Really! I'm talking about my last post where I jumped to the conclusion that I had a bug in a particular line of code. In fact, that line of code is correct - the bug lies elsewhere. Let me explain.

As I stated before, there is no way (at least I never found a way to) use storage object with SQL Compact command parameters. To overcome this limitation the code must allocate the full size of the BLOB data on the binding buffer (which is not very efficient, but it's a viable solution). The problem with this is that the code has to know the size of the BLOB and adjust the binding buffer accordingly. If you use ADO .NET then you know how this issue is solved: the developer must create SqlCeParameters with the given maximum size of the parameter data. The whole purpose of this code is to avoid this situation and let the OLE DB library handle that chore for you.

Command parameter information is requested when you prepare a command and this is the reason why you see the dreaded 1024 constant in the code (the value is pretty arbitrary, by the way). This was the value I chose to represent the default value of the BLOB just to assume a value for it's length. The code then expects that you call SetParam for each parameter and explicitly set its size then. When the command is executed, the whole set of command parameters is looped through in order to get the size of the new binding buffer. If the new binding buffer is larger than the previous one it gets reallocated. And this is exactly where the bug was: the existing buffers were not being deleted and an obvious memory leaked was being created (the complaint reason from the reader who sent me the email message thet prompted me to look at this issue again).

To prove that the memory leak issue is gone in the new code, I set up a very simple demo project where three JPG images are inserted into a sample database table. The images are inserted using a SQL INSERT command and the code is arranged so that the images are processed in an increasing size order. This way you can actually see how the binding buffer increases.

Incidentally, the real bug was on the CCommand::BindParameters method where the m_pBuffer and m_pBindStatus arrays are allocated. These must be freed if non null before allocation.

Sample code: BigParams.zip (789 KB)

Note that the sample application expects both the sdf and jpg files on the \Storage Card\Temp directory. You can change this by editing the BigParamsFrame.cpp file.

Command Parameter Handling

I recently got an email from an interested reader pointing out an error in the SQL command parameter handling OLE DB Client code. If you remember my previous ramblings on this issue, the OLE DB provider for SQL Compact is unable to handle BLOB SQL command parameter through storage objects. This means that you must provide some information about the BLOB before using the command, just like you have to do if you use the ADO .NET stack. Now, if you look at the CCommand::CreateParameter method you will see the error:

ULONG nParamSize = bIsLong ? 1024 : pParamInfo->ulParamSize;

Let me assure you that I was not taking any medication (or smoking funny stuff) when I wrote this line. This is a piece of code that I just did not return to so the error crept in and started to create lots of memory leaks. Solving this issue is not a simple matter of correcting this line to:

ULONG nParamSize = pParamInfo->ulParamSize;

Like I said, we must specify all the BLOB parameter sizes before we prepare the command and this means that CCommand::Prepare must also change. Instead of getting parameter information in the call to Prepare, the user will have the option of calling another method to get this information. In any case, there must be an option to provide custom command parameters. This is the topic of my next post.

SQL Compact and threads

Do you remember the days of Pocket PC 2002 and 2003? Life was pretty simple with no need for certificates, a single screen size and SQL CE only ran on devices. Then Microsoft released SQL Compact 3.1 and suddenly the small database was also on the desktop. By this time I already had a small set of tools that remotely accessed the engine via a streamed RAPI interface and was confronted with the need to port the code to the desktop.

In its classical form, the code runs in two different processors with an asynchronous link. Porting the code to the desktop, where both the client and the server ran on the same process, meant either a major rewrite or a smart adaptation. I chose the latter and implemented a DMA data stream that is implemented by the "server" DLL on the desktop. The client merely uses a virtual stream that can be attached to a variety of server endpoints, like a RAPI server, a TCP/IP server or even a local server running in the same process (but on different threads).

This has worked quite successfully until last week when I got a report from a customer complaining about a mysterious error. After exporting a very large (and I mean very large) database from SQL Server to SQL Compact 3.5 on the desktop, the database file would end up with over 350 MB of size but zero tables. After repairing the database it would shrink down to the 20 KB empty databases have.

I first suspected of the transaction mechanism because the whole process (table creation, data transfer and final script execution) is executed under a single transaction. After confirming with Microsoft that there was no known transaction size limitation, I got back to the drawing board in bewilderment. To make matters worse, no error was being reported!

The product that caused this issue (Data Port Wizard) has a very simple usage pattern: you set the source and target databases, set some options and run the data transfer process. At the end the application reports success or error and then quits (after user confirmation). As it turned out, the problem happened when the application exited.

When transferring data to a desktop SDF file, everything works under the same desktop process. The SQL Compact data provider runs in its own thread (not on the UI thread) due to the reason I pointed out at the beginning: I wanted to reuse the remote provider code that runs on a different CPU, so I implemented it as running on a different thread with a DMA communication to the client code. When the client application closes, it signals the SQL Compact data provider to shut down and waits a little while for it to reply. If no reply arrives on time, the desktop client proceeds to terminate the application.

Apparently with very large databases, the SQL Compact engine takes a bit longer to shut down. This is in fact something to be expected because there are larger buffers to flush to storage and there is also the auto-shrink feature that runs when the connection closes. When this whole thing is running on a remote device, your desktop client can afford not to wait for the remote provider to shut down because it is running in a different CPU. When running under the same process, you don't have that luxury. If you don't wait for the SQL Compact engine to shut down in its own thread and you shut down the application, you will be preventing the database engine from correctly shutting down altogether. You can imagine the mayhem this causes to your database...

Lesson: if you run SQL Compact on a different thread, make sure you wait until the connection closes before you shut down your application. You have been warned!

Seek and you shall find

I recently found myself involved in a prototype Windows Mobile mapping application using the Virtual Earth tile system. Downloading the 256 x 256 bitmaps to a mobile device through a GPRS connection is slow and a bit painful, so I thought about creating an external cache mechanism. This would temporarily store the bitmaps for future use and, instead of writing the bitmaps to the file system I thought about using SQL Compact. There are a few advantages for using a database instead of the file system: you store all the bitmaps in a single file and you get the added benefit of easier maintenance (especially when deleting outdated bitmaps to clear the cache). So I looked back to the OLE DB library I have been working with for some help but I found a major whole (yes, there are more, I know!): there was no support for IRowsetIndex::Seek. Why do I need to use this method when I can equally retrieve individual records using a plain SELECT command? The answer is speed. When you go through the SQL Compact Query Processor your application pays a performance penalty that may be avoided (with varying degrees of effort). In this case I wanted to retrieve a single row from a table using an index (no fancy JOIN or GROUP BY clauses) so using a base table is very appropriate, performance-wise.

The idea is very simple: open a base table cursor and specify the index to use; seek using the required key values and retrieve the row data (if it's there). As I said, you can do this using SQL but there is performance penalty that I did not want to pay, especially when fast painting is required. My major problem was that there was no support for this in the previous version of the OLD DB library, so I had to implement it.

Implementing the IRowsetIndex::Seek supporting code implies adding an extra column binding code (similar to the one I wrote for the columns). In fact, you need an extra IAccessor object to bind the key columns as well as all the other supporting data structures. After a few hours worth of work, I managed to develop a working version of the code that I'm publishing here. If you look at the CRowset class implementation you will see how its complexity increased to a point where some new abstractions are just crying out to be created...

When using this code, remember to:

• Add the DBPROP_IRowsetIndex property (set to true) to the rowset property set;
• Call CRowset::SetKeyValue for each one of the index key values: ordinals start at one;
• To seek to the requested row, call CRowset::Seek specifying the number of valid key columns (the number of valid values set with CRowset::SetKeyValue).
  

If you get an S_OK as return value, the row was found and the data loaded into the row and ready to use.

Sample code: OleDbClientLib3.zip

EDIT: The link has been fixed.

SQL Compact Insert Performance

I've just posted the SQL Compact Insert Performance demo on my managed code blog. It compares various approaches for inserting large volumes of data in a SQL Compact database and, although written for a managed code audience, is also relevant for native code and the OLE DB stack (the basic reality is the same, only slightly faster...).

Enumerating Unique Constraints

There's no big deal about getting the list of UNIQUE constraints - they get listed as indexes by the CIndexesRowset class. When using this schema rowset you get everything that is implemented as an index by the database engine:

• Indexes
• Primary Key constraints
• Unique constraints
  

While the schema rowset can distinguish between a regular index and a PRIMARY KEY, there's no telling if the listed index is a UNIQUE constraint or not. To correct this, you must first list all the UNIQUE constraints using the new CTableDefinition::FillUniqueArray method (see the updated sample below). As you can see, a unique index is also represented by a CIndex object, but stored in a different collection in the CTableSchema class. When the indexes are finally enumerated, the list is filtered for known UNIQUE constraints.

Another addition to the sample code is the ability to open any SQL Compact database on your device by using the Menu / Open sequence. The code that opens the database uses the installed SQL Compact OLE DB engines to "sense" the correct database format (see the updated CSchemaTreeFrame::OpenDataSource method).

On the next post I will start building the SQL Compact Explorer application by using this sample as a starting point. I will also use this application to bring back some of the code that I have been working on, namely the touch list and all the derivatives that have been developed but left unpublished.

Sample code: SchemaTree4.zip (155 KB)

Enumerating Foreign Key Constraints

After enumerating columns and indexes, now it's time to work on the first of the three types of constraints that are supported by SQL Compact: foreign key constraints. Constraint definitions are also retrieved by calling ITableCreation:.GetTableDefinition. These are returned in an array of DBCONSTRAINTDESC structures that contain all the supported constraints. Each record contains one constraint whose type is stored on the ConstraintType member. The SQL Compact header file defines the following values:

• DBCONSTRAINTTYPE_UNIQUE - A unique constraint.
  
• DBCONSTRAINTTYPE_FOREIGNKEY - A foreign key constraint.
  
• DBCONSTRAINTTYPE_PRIMARYKEY - A primary key constraint.
  
• DBCONSTRAINTTYPE_CHECK - A check constraint (not supported by the current SQL Compact versions)
  
• DBCONSTRAINTTYPE_SSCE_DEFAULT - Apparently this is a default value constraint. I have found no documentation about this constant and will investigate it later.
  

For now, we concentrate on the foreign key constraints and how to show them on the SchemaTree sample aplpication. In this version of tha sample, foreign keys will be displayed in a folder of their own, but for future versions this folder will be renamed "Constraints" and will contain all table contraints, not just foreign keys.

A foreign key definition is stored in the new CForeignKey class. You can see these classes being built from the array of DBCONSTRAINTDESC structures in the new CTableDefinition::FillForeignKeyArray function, where each array item is tested for the correct type and then fed to the CForeignKey class constructor, where all thework takes place. Each foreign key contains a list of matched columns from the reference table and the base table (each pair is stored in an instance of the CForeignKeyPair class).

Displaying these in the schema tree is quite straightforward and follows the same rules I have used for columns and indexes. Please note that there are new accessors for foreign keys on the CTableSchema class.

On my next post I will also enumerate primary keys and unique constraints, put them all under the same folder and start moving towards an on-device SQL Compact editor. I already got a name suggestion from Alberto Silva: SQL Explorer. More names, anyone?

Sample code: SchemaTree3.zip (1.2 MB)

The perfect time-waster

Reinventing the wheel. That's what I have been doing for the last few days implementing object arrays in C++... Why? Apparently some template classes are not very easily exportable on a DLL so you cannot use useful stuff like CString or CAtlArray as exported class members. After having implemented a very simple string class and while working on object array allocation I decided it was enough: from now on the OLE DB Client library will not live in a DLL but either on a LIB or embedded in the client project. Enough is enough - I want to move ahead and implement the cool stuff, not arrays and strings so that the damned thing can be neatly exported as a DLL.

The code I'm presenting today has some additions to the OLE DB Client library, namely some new schema-related classes:

• CSchema - Contains an array of table CTableSchema objects and a reference to a CSession.
  
• CTableSchema - Contains a table schema information. This class is prepared to load this information on demand in order to avoid a performance penalty when enumerating the database schema (all tables are loaded and each table schema is loaded on demand).
  
• CTableDefinition - Helper class that loads the table definition in a single OLE DB call. This populates the columns and constraints collections. Indexes are loaded separately through a specialized schema rowset (see the LoadIndexes method).
  
• CColumn - Contains column schema information.
  
• CIndex - Contains index schema information and a list of index columns.
  
• CIndexColumn - An individual index column.
  

Please note that some of these classes will change in content and placement (I mean the .cpp file).

The sample project is the same - it enumerates in a tree the schema of the sample database placed on the device root. As you can see from the code, the tree lazily loads the table schema information (when the user expands either the "Columns" or the "Indexes" folder).

After loking a bit at this sample and to how the OLE DB Client has evolved, I started wondering about writing a "Query Analyzer" type of application. This would mimick most of the "old" SQL Compact Query Analyzer application and would add a few more features. Writing this application would require development in other areas such as the user interface, but I think that it will be a very interesting challenge.

What features would you like to see in the open source QA? What name would you give such app?

Sample: SchemaTree2.zip (1.21 MB)

Enumerating Columns with IDBSchemaRowset

While it's freezing out there (for Lisbon standards, of course) I have been busy working on the schema enumeration code for the OLE DB Client library. My first approach is to enumerate database tables and columns using the IDBSchemaRowset interface. Access to this interface is encapsulated in the CRowsetInterface class that is derived in CTablesRowset and CColumnsRowset classes to enumerate tables and columns respectively. The IDBSchemaRowset interface works by accepting a set of "restrictions" and generates a schema rowset containing the requested data. Schema rowsets are formally defined on the Appendix B of the OLE DB Programmer's Guide, where you can see the returned columns and the accepted "restrictions". SQL Compact implements a subset of these, as described in the BOL.

Using the CTablesRowset is quite easy (and you have seen it at work in a previous sample where it was used to test for the presence of a given table):

CTablesRowset tables(m_session);
CRowset rowset;
HRESULT hr;
hr = tables.Open(NULL, NULL, NULL, NULL, rowset);

The first four NULL parameters mean that you don't want to filter on any of the supported restrictions, so you will get a list of all the tables in the database. SQL Compact does not support table catalogs nor table schemas so the first two parameters are always NULL for this provider. The third restriction is the table name and the fourth is the table type (see the possible values for this in the TABLES schema rowset reference).

When scrolling through the tables rowset, you retrieve the current table name like this:

CString strTable;
rowset.GetValue(3, strTable);

Now that we have the table name, we can enumerate its columns by using the CColumnsRowset and imposing a restriction on the table name (see the COLUMNS schema rowset reference):

CColumnsRowset columns(m_session);
CRowset rsColumns;

hr = columns.Open(NULL, NULL, strTable, NULL, rsColumns);

This retrieves all columns from the given table and you can get the column name from ordinal 4. As you can see from the COLUMNS schema rowset, there are lots of additional schema information about a column, like its OLE DB type, "nullability", size and more.

Although this is a quite convenient way to retrieve a table schema, we can use another OLE DB interface that in a single call returns column and constraint information. On the next post, I will look at ITableCreation.

Sample code:
SchemaTree.zip
OleDbClientLib2.zip

Implementing SQL Compact Command Parameter BLOBs

After much ramblings, tests and misconceptions, I finally closed the chapter on SQL Compact command parameters. The sample code (see below) illustrates how to use a BLOB command parameter using the "variable length" binding buffer. Whenever the total size of the columns exceeds the allocated binding buffer size, the binding process is repeated increasing the binding buffer size. Although this is not an optimal solution (being able to use storage objects for this purpose would be much better), it frees you from having to specify the maximum size for BLOB parameters - the code calculates this for you.

Before you can execute a command with parameters, you must prepare it by calling the Prepare method (failing to do so will cause a run-time error). Here the code determines the number of existing parameters and allocates memory for a "user buffer". This user buffer is an array of CDbValue objects, a VARIANT-like class whose sole purpose is to store parameter values. When the user sets a parameter it is first written to one of these objects and upon execution it is copied to the data buffer. Meanwhile, the code determines if the new set of parameters on the CDbValue array requires rebinding, does so if needed and finally copies the values to the data buffer. This way you just have to set the parameters and execute the command (see the sample code).

As a side-effect of all these travails, I ended up creating a new class - CDbValueRef - from which CDbValue derives that I use as the "engine" that sets and gets data from various types, performing the necessary data type conversions. As you can now see, the column data access on the CRowset class is performed through an array of such objects that directly reference the bound data buffer, and they know how to handle storage object BLOBs. Now you can access your CRowset data using a type-safe interface.

Sample: InsertBlob.zip (152 KB)

I was right...

... for the wrong reasons. In fact, you cannot use storage objects to set BLOB command parameters. I found that the usual mechanism does not work because:

1. When retrieving command parameter information through GetParameterInfo, you don't get the DBPARAMFLAGS_ISLONG bit set for BLOBs;
2. Binding a BLOB parameter through a storage object causes the Execute method to fail reporting a DBSTATUS_E_CANTCONVERTVALUE on the BLOB column.

So I had to revert to the old code that buffers the parameter values in a buffer and forces a rebind when the new set of parameters is larger than the old parameter buffer (used by the accessor). Now that I got this straight, it's time to finish the new demo that inserts BLOBs using a parametrized INSERT command. Please stay tuned for the next post.

I was wrong

Tomorrow I will deliver (using the English language) my second presentation in an international Microsoft conference. My first time ever was in 2005 in Nice for the now defunct MEDC (Mobile and Embedded Developers Conference). I have been splitting my time in attending interesting presentations, preparing my own presentation and completing the OLE DB Client library code. It was while working some final details of the presentation that I had an epiphany: I was wrong about BLOB command parameters!

The fact that the OLE DB specification limits us to using just one parameter accessor handle to set all the parameters does not mean that we are unable to use storage objects. Why? When I discussed the limitation of the SQL Compact engine that prevents it to create more than one storage object at the same time, it actually applies to when the data flows from the provider to the consumer, like when you are reading data from a base table cursor or from a query cursor. It does not apply to when data flows from the consumer to the provider. In fact, we experienced no such limitation when inserting or updating data through a base table cursor. The consumer application can create as many storage object as it pleases to, and this is the exact same situation with command parameters: the consumer application can only write to them because SQL Compact does not support output parameters.

Right now this is still a theory that seems to make a lot of sense to me and I will focus now on proving it right. Meanwhile, I developed a new set of classes to manage column values in such a way that they would do double duty when used on a rowset and on command parameters. For the latter, these classes would work as intermediate data buffers that would be used as temporary storage for application-provided parameter data. When needed, the CCommand code would bind these to a real data buffer, recreating the accessor handle when needed. In fact, I have just tested the code and it works... But this can't be right. If I can bind the parameters before setting them (just like in the CRowset class) then I will have the best solution.

Back to the drawing board. (If you want to see the code as it is, please drop me a line.)

SQL Compact Command Parameter BLOBs

The problem du jour is to make the CCommand class accept BLOB parameters, something that I have been thinking about the last week or so. Let me recap what's at stake.

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:

1. 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).
   
2. 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.

Because the second option seems to be hardest to implement, that is exactly the one I chose. See you in the next post with the code!

SQL Compact Command Parameters - II

After a bit of work, here's the new version of the OLE DB Client library with basic support for command parameters. The sample code implements a very simple query:

SELECT * FROM Customers WHERE Country = @c

and executes it by setting the parameter value to 'Germany' (using the same sample database).

The first change you will see in the sample code is that I downgraded the solution to VS 2005 format due to some requests to do so. VS 2008 users can easily upgrade the solution, so this is a minor issue. For convenience, I'm also packaging everything in a single ZIP file. Also, all the samples from now on will deploy to the same device directory (typically in \Program Files\OleDbClient).

Executing a SQL Compact command with parameters is quite simple. In a nutshell:

command.SetText(pszCommand);
hr = command.Prepare();
if(FAILED(hr)) ...
command.SetParam(1, _T("Germany"));
hr = command.Execute(rowset);

Remember from my last post that parameterized commands must be prepared before executing. If you look under the hood, you will see that parameters are bound (using code very similar to CRowset's column binding code) in the Prepare method. You can only set command parameter values after the Prepare method executes successfully.

This code still has some limitations, and the most notable one is the inability to handle BLOB parameters. When executing SQL commands, the OLE DB provider only accepts one accessor handle to bind all parameters. As we have seen in previous posts, this prevents us from binding more than one BLOB parameter. The only solution I devised for this when using the ATL OLE DB Consumer Templates was to force the BLOB parameters to have a maximum lenght and bind them as regular columns. Of course, this implies a change to the parameter binding code and some user intervention...

Sample code: OleDbClientLib.zip

SQL Compact Command Parameters

Command parameters can be thought of as value replacements or variables for SQL commands. You use a command parameter when you need to execute a SQL command multiple times changing one or more values on each execution. Instead of rebuilding the command text every time the command is executed, your code merely changes a variable value. This means that your code changes from this:

SELECT * FROM Customers WHERE Country = 'Germany'

to this:

SELECT * FROM Customers WHERE Country = @p

Note that the command parameter (@p) replaces a literal value that will change every time you execute the command. Command parameters have some interesting advantages over string concatenation:

• Command parameters are strongly typed;
• Command parameters make the command run faster;
• Command parameters are safe because they avoid SQL injection attacks.

Suppose that you need to pass a date value as a parameter to a SQL command. How exactly do you format a date value in order to be correctly interpreted by the query processor? When using command parameters you just don't are about this - you just feed the parameter with the binary value and it atakes care of the rest, no string formatting contortions needed.

The OLE DB provider requires that parameterized commands be prepared (compiled) before executing. Contrary to the managed ADO .NET provider, the OLE DB provider does check the command for errors and enumerates all the given parameters and even infers their data types (your code can then read this informtion and use it). Preparing a command with the ADO .NET provider for CF merely marks the command as requiring preparation before executing. Either way, a prepared command runs faster than an unprepared one.

Finally, with command parameters there is no way to let your user change the syntax of the underlying SQL command. If you use string concatenation to build command strings (with user input), you risk exposing your code to SQL injection attacks that may break your application code or even its security.

Since version 3.0, SQL Compact supports named parameters meaning that you can give your command parameters some meaningful names. All command parameter identifiers must be prepended by a '@' character. If you are still using SQL CE 2.0 then you are out of luck as all command parameters are identified by a single '?' character and distinguishable by their ordinal position only.

After this brief introduction, I will look at the details of implementing a SQL command parameter management code and some of the finer details of binding, such as what to do with BLOBs.

Very fast row counting on SQL Compact

What's the fastest way to retrieve a table's row count in SQL Compact 3.0 and 3.5? Use the sp_show_statistics stored procedure! Pass your table name as the first parameter (the second is optional) and you will get back a few statistics about the table and its indexes, so more than one row might be returned. The row count is on the ROWS column (ordinal 3), an unsigned 32-bit integer.

Note that this is not supported in versions 2.0 and earlier.

Meet me at Tech Ed EMEA 2008

Meet me in Tech Ed EMEA 2008 where I will deliver a presentation about "Microsoft SQL Server 2005 Compact Edition Ultimate Performance Tuning" (Windows Mobile track). See you there!

OLE DB Commands and SQL Compact

OLE DB Commands are created through the IDBCreateCommand Session object interface. Just find your Session object and call QueryInterface using the IID_IDBCreateCommand GUID. This interface just exposes one method - CreateCommand - that you use to create OLE DB Command objects. As with most COM and OLE DB objects, several interfaces are exposable for a given object subject to provider implementation. For SQL Compact the following interfaces are exposed:

• ICommand - Execute the command through this interface. As I noted in a previous post, the Cancel method is not implemented in SQL Compact.
  
• ICommandPrepare - Use this interface to Prepare or Unprepare the command. Command preparation is useful when executing the same command more than once and is required when using command parameters. Contrary to what happens with the ADO .NET provider, the Prepare method actively validates the SQL Command against the existing database, returning errors for incorrect syntax.
  
• ICommandProperties - Gets or sets the properties associated with the Command.
  
• ICommandText - Gets or sets the command text. This interface inherits from ICommand.
  
• ICommandWithParameters - Manages command parameters (we will have to implement a data binding scheme similar to what exists in CRowset). SQL Compact allows only for input parameters, not output.
  

As you can see, this is enough to run SQL commands against SQL Compact's Query Processor. For now, we leave this here as a reference while I go back to work and start implementing these features in the OLE DB Client library.

BLOB update code

The sample code I'm posting today illustrates the techniques I described on my last post. Please note that this is really a sample - I will change it in the next incarnation in order to be more effective when binding data. Right now you can see a bit of a mess when binding columns because both the DBCOLUMNINFO and DBBINDING arrays are being used to find the column information addresses on the data buffer. Next version will have a unique array with all the required info to correctly and accurately describe each column. Enough of this and let's look at the code.

First of all, take a look at CRowset::SetValue. Here you see how text strings (only!) are written and how the code decides if the column is a BLOB or not. If we are writing to a BLOB column, the code creates a CBlobStream object and writes the string contents to it. Upon success, a pointer to the CBlobStream object is stored in the rowset data buffer. When this data is written to the provider (by calling IRowsetChange::SetData on the BLOB column's accessor handle), the provider calls the CBlobStream::Read method to read all the string contents into the BLOB storage. When it is done with it, the provider calls the Release method and the CBlobStream deletes itself - neat.

Finally, take a look at the CRowset::Insert, CRowset::Delete and CRowset::SetData methods. These implement the changes I described before. Also, note that these methods will work even if the IRowsetUpdate interface is not exposed.

Next up - After rewriting most of the column binding code, I will start talking about commands.

Sample files: EditRow4.zip, oledbcli3.zip

Updating rows with BLOBs

As I have previously discussed, we need to make some substantial changes to our code if we want to update rows with BLOB columns. So how exactly do we update these things? The first thing we need is to implement an object that behaves like an ISequentialStream COM object. Our code will create one such object per BLOB column, store the data in this object and then store a pointer to this object in the rowset buffer. When updating data, the OLE DB provider will read it (just like our code reads the storage objects coming from the provider) and then releases it. To make our life easier, our implementation of this object should delete itself when the reference count reaches zero. This way we will not have to worry about deleting it later.

After setting all the row data (BLOB and otherwise), we can insert or update it. If you want to delete a row it is pointless to write data to it. If you remember, I said that when using BLOBs with SQL Compact we must use the deferred update mode by requesting the IRowsetUpdate interface. Here's how the code changes for each operation (insert, update or delete):

Insert

1. Call IRowsetChange::InsertRow - this sets the non-BLOB data;
2. Call IRowsetChange::SetData for each BLOB accessor;
3. Call IRowsetUpdate::Update to commit the changes.
   

Update

1. Call IRowsetChange::SetData for each accessor.
2. Call IRowsetUpdate::Update to commit the row changes.
   

Delete

1. Call IRowsetChange::DeleteRow.
2. Call IRowsetUpdate::Update.

Note that if any of these processes fails before calling Update, you should call Undo to revert any changes made to the row. I'll show you the code in the next post.