The Property List

Before I can go any further in the development of the SQL Explorer device application, I must make sure that I have some components ready for use. These will include (but will not be limited to):

• A tree component to display the database schema;
• A property list control to edit/display object properties such as connection, table and column properties;
• A grid or table-like display for table and query results;
• A splitter control for compound views;
• A decent file open dialog box.

It surely would be easy to just use the controls that Windows Mobile has in store for us: the tree view and the list view. These are undoubtedly the most used controls but are somewhat déja-vu and are arguably hard to use (especially the list view). I decided to (guess what) write these controls from scratch using something the WTL CTouchWindow class that I published here a some months ago.

This class is a nice candidate to be a base class for the new tree, grid and property list controls because it enables a touch-like interface. My first approach was to implement the property list control taking some of my previous work as a model: A list-based form for Windows Mobile. There are two nagging issues with this code: it uses MFC and subclasses the list view control. As I've learned by implementing the WTL version I'm presenting here, subclassing and custom-painting the list view is more work than you need to implement the control.

The property list is shown here contains a list of editable items and groups. Groups are shown as collapsed (the + sign indicates that the item can be expanded) and, like the original MFC implementation, each group can contain only editable items, not other goups (I may change this later on).

A group should be used as a means to group related data items and to ease the list navigation. When clicked the group expands to display the contained editable items. By clicking again, the group collapses and hides the contained items. This is the same principle that you can see at work with a tree, so extending this concept to a "touch tree" should not be very difficult.

Each editable item may be in either of two states: focused or activated. An item gets the focus when it is clicked and when either the up or down keys are used to change the selection. A focused item shows the focus rectangle around it. When the enter key is pressed (or when the user clicks the item) it is activated (note that activating implies setting the focus). The notable exception to this rule is the text editor item that activates itself when it receives the focus.

When an item is activated, it generally creates a Windows control to perform the editing. The exceptions to this rule are the check box (changes its state) and the group item (expands / collapses the contained items).

As of this implementation, the supported data item types are:

• Text editor
• Date time editor
• Check box
• Combo box

Here is the expanding property list:



The scroll bar to the right is custom-painted and is not yet the final version (I'm still thinking about allowing the use of old style scroll bars). The advantage of this type of scroll bar is its transparency - you can actually read what's beneath it so you do get a few more pixels of screen real estate.

I will make lots of changes and additions to this code but, in the meantime, please do take a look at it and be so kind as to use it and criticize it.

Sample code: PropList.zip (45 KB)

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)

Windows Mobile VM Article

A very interesting article about Windows Mobile Virtual Memory has just turned up on Code Project:

Visualizing the Windows Mobile Virtual Memory Monster

Not only this is a worthwhile read, you will also get a couple of links into the Windows Mobile RSS (Reed and Steve Stuff) Feed blog where the Windows Mobile virtual memory manager is discussed.

On my next post I will publish the revised version of the SchemaTree sample with a few additions, like the ability to open any SQL Compact database on the device. From then on, the project will be renamed and I will start building an on-device SQL Compact explorer application. In native code, of course!

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)

Native Pointers

No, I'm not talking about memory addresses, just interesting bits of information for the Native Mobile developer.

MSDN forums have recently moved to a new platform and got new URLs. Here are my favorites:
Visual Studio Smart Device Development - Native C++ Project
SQL Server Compact

On another note, Christopher Fairbairn just wrote another great post in his blog where he discusses some very cool techniques for Native Mobile developers, such as playing sound, using COM and displaying PNG images. A must read!

Code changes

After a second look at the array CAtlArray implementation, I'm planning to change all the containers from value to pointer (instead of CAtlArray, I will change this to CAtlArray). The reason is quite simple: when growing the buffer through Add, the existing items are moved to the new buffer with a simple Checked::memmove_s call. The contained class copy constructor is not called which may lead to memory leaks (depending on the contained class implementation). If the array merely contains pointers, everything works as expected and all memory relocations will be much faster (only the pointers are copied, not the objects themselves). The downsidr to this approach is that I will have to manually implement the code to release all the objects pointed to by the array.

Finally, don't miss this great post on the Windows Mobile Team Blog: Uninstalling Applications Programmatically in Windows Mobile.

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

Connection Manager Article

The latest issue of MSDN Magazine is out and it features a great article from Marcus Perryman: Going Places: How Connection Manager Connects. The reason why I'm also referring to this article here is that its code samples are in C, a rarity these days.

SQL Compact Schemas

Now that I have looked into the most basic principles of using a SQL Compact database from native code (base table cursors and command execution), I will now look at the available tools to enumerate and manipulate the database schema. There are essentially two ways to go about this:

1. SQL commands
2. OLE DB interfaces

Using SQL commands
This may be the easiest approach and is also language-independent - you can use these techniques from both native and managed code. To determine what's in your database you can execute SELECT commands against the INFORMATION_SCHEMA system views. These work like regular SELECT commands and return specific rowsets:

• COLUMNS
• INDEXES
• KEY_COLUMN_USAGE
• PROVIDER_TYPES
• TABLES
• TABLE_CONSTRAINTS
• REFERENTIAL_CONSTRAINTS (not documented for 2.0)
  

To retrieve information from one of these views, you just execute a simple query like:

SELECT * FROM INFORMATION_SCHEMA.TABLES

This will return all tables in your database. The advantage of using a SELECT command is that you can add a WHERE clause and filter the result. If you need to make changes to your database schema you can use all the supported DDL commands. Since version 3.0 came out, Microsoft even provided us with a stored procedure to rename tables: sp_rename. Unfortunately you cannot rename columns which sometimes is needed.

Using OLE DB interfaces
All of this (and a bit more) can also be done through some of the exposed OLE DB interfaces, namely the following Session interfaces:

• IDBSchemaRowset - Generates rowsets that are similar to the ones returned by the INFORMATION_SCHEMA views.
  
• IIndexDefinition - Creates and drops indexes.
  
• ITableCreation - Retrieves the table definition (full schema).
  
• ITableDefinition - Creates drops tables, adds and drops columns.
  
• ITableDefinitionWithConstraints - Same as above, but also manages table constraints.
  
• IAlterIndex - Only renames indexes in SQL Compact.
  
• IAlterTable - Allows changing table properties like the table name, column defaults and identity properties. You can also use this interface to rename columns (not available in managed code).
  

Note that when using these interfaces, you are accessing the storage engine directly, circumventing the Query Processor.

On my next post I'm planning to publish a sample that illustrates how to use these interfaces.

Logo Certification Article

Here's a very nice article for native mobile developers that addresses the required coding for some of the strict Windows Mobile Logo Certification. A worthwhile read.

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!

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.