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!

New column binding and commands

As promised in a previous post, I changed the way columns are bound in the CRowset class so that the class retains all the important column information retrieved from the IColumnsInfo interface, but also the binding offsets. You can see all of this at work in the new version of CRowset in the sample files.

You will also find the first implementation of the CCommand class, an abstraction over the OLE DB Command object. This first implementation is very simple and allows only for execution of a command text (no parameters yet) returning a CRowset and/or an affected row count.

Using a CCommand object is very simple (much like using CTable):

CCommand command(m_session);

command.SetText(_T("SELECT * FROM Employees"));
hr = command.Execute(m_rowset);

Start by declaring a CCommand object passing the open CSession object as parameter. Next, set the command text and then call execute passing a CRowset object as parameter. You can now use the rowset to navigate the rows and retrieve data.

Note that the returning rowset may not have a bookmark (not in this case, anyway) so you must be careful how you index the resulting columns. Remember that CRowset::GetColumnCount returns the total number of columns including the bookmark, if present. Also, columns are referred to by their ordinal position and the first data column is always at ordinal 1, so if you are iterating through all columns using GetColumnCount, make sure you always start at 1 and also add 1 if the rowset does not have bookmarks (see CRowset::HasBookmark).

Finally, if you don't set any specific command properties (as in this case) you get a forward-only cursor, so there is no going back...

Sample files: EditRow5.zip, oledbcli4.zip

P.S.: Don't try to use the EditRow sample to edit rows coming from the command rowset! I just adapted the sample to show how easy it is to display command data. The resulting is read-only.

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.

ICommand::Cancel is a dummy

No, I'm not going to talk about the ICommand interface right now, but I just found out that the ICommand::Cancel implementation on the SQL Compact OLE DB provider is a dummy. Good to know!

Reading BLOBs

My first approach to the BLOB-handling code took a bit longer than expected due to my aging brain. Please understand that the code I'm publishing today is incomplete and will change in the future. I hope that it helps you in understanding what it takes to read BLOB data through the SQL Compact OLE DB provider.

There are two important changes to the code since last post:

1. Columns are indexed by their ordinal.
   
2. Columns are bound using a two-pass approach - On the first pass the non-BLOB columns are bound using the same accessor handle. The second pass binds the BLOB columns allocating an accessor handle for each column.

The bulk of the code that reads the BLOB into memory is on the new CRowset::GetValue method. Note how the column accessor handle is used to retrieve the ISequentialStream pointer and how it is read into a CString. You have to consume all the data immediately because you cannot keep that stream object alive very long, especially if you want to read or write to another BLOB.

Most of the complexity you see on the code (the difference between binding and column indexes) stems from the fact that columns are being moved around in order to make binding simpler. When life was simpler and there were no BLOBs, there was a direct relation between column and binding entries and there was no need to map between the two. Now that we are moving binding entries around, we must make sure that a mapping exists between both arrays (see the GetBindingIndex method).

Next post will handle writing data to the BLOB.

Sample files: EditRow3.zip, oledbcli2.zip

CStdDialogImpl and the OK button

Are using CStdDialogImpl as the base class for your WTL dialogs? Are you having difficulty hiding the "ok" button? Here's a simple solution:

Derive your dialog class (say CMyDialog) from

CStdDialogImpl<CMyDialog, SHIDIF_SIZEDLGFULLSCREEN | SHIDIF_SIPDOWN>

On the OnInitDialog handler, call:

SHDoneButton(m_hWnd, SHDB_HIDE);
ModifyStyle(0, WS_NONAVDONEBUTTON, SWP_NOSIZE);

Make sure you change all the calls to CStdDialogImpl<CMyDialog> methods with the new base (you can use a typedef for that).