One application, two database engines

I have just figured out how to enable the same application to consume either SQL CE 2.0 or SQL Mobile databases at run time. I'm obviously talking about OLE DB here.

When Microsoft shipped SQL Mobile, we got a new header file to include in our applications: ssceoledb30.h. Unfortunately, this means that you cannot have both the old include file and the new include file on the same exe. You can use the preprocessor to include either one, but this is not what I was looking for: I wanted an integrated solution that would allow me to consume any database (2.0 or 3.0) from the very same exe.

The solution is to merge both header files into just one. While I wait for Microsoft to tell me if I can publish the one I wrote (due to copyright issues, of course) you can have a go at it yourself by diff'ing the two files and figuring out the differences. Not many, actually.

SQL CE 2.0 Support ends this month

Read more on Nino's post.

Season's greetings, everyone!

Microsoft SQL Server 2005 Mobile Edition Device SDK

The Microsoft SQL Server 2005 Mobile Edition Device SDK is now available for download here.

New site, new forum

I dumped the old forum and mirrored my site at www.primeworks-mobile.com. Horray for the dot com!

WTL 7.5 Released

The latest version of WTL (7.5) has been officially released on sourceforge. According to Nenad Stefanovic: This is the first release on SourceForge.net, as the part of the Microsoft Shared Source Initiative. WTL 7.5 features the support for Visual Studio 2005, enhanced support for Windows CE development, and new classes and bug fixes and enhancements.

SQL Server Mobile 2005 on MSDN

SQL Mobile has a new home page on the MSDN Mobile Developer Center. There, you can find articles, downloads and other resources on SQL Mobile. Cool stuff!

Localized .NET Compact Framework 2.0 Redistributable

Microsoft has just released the localized versions of the .NET Compact Framework 2.0 Redistributable. These are available in the following langueges: Chinese (Simplified), Chinese (Traditional), English, French, German, Italian, Japanese, Korean, Portuguese (Brazil), Spanish. You can download these from this link.

New MSDN articles

There are some new and interesting articles on MSDN:

Step by Step: Introduction to the New Native APIs in Windows Mobile

Device Debugging and Emulation in Visual Studio 2005

Using Visual Studio 2005 to Design User Interfaces and Data for Device Applications

ActiveSync 4.1 Released

You can download it here.

Post-TechDays interview

Here is my interview after TechDays in Lisbon (Portuguese language only).

Ricardo Figueira's Blog (no, we are not related)

RAPI and Windows Mobile 5

When the first Windows Mobile 5 devices started to hit the market, some of my customers tested Data Port Wizard on them and found out that it would not work. There is a documented workaround on MSDN but one of my customers found a better way by setting a single registry value. As a matter of fact, setting HKLM\Security\Policies\Policies\00001001 to 1 will enable all RAPI activities on the device.

Setting this registry key corresponds to the following provisioning script:

<wap-provisioningdoc>
  <characteristic type="SecurityPolicy">
    <parm name="4097" value="1">
  </characteristic>
</wap-provisioningdoc>

Read all about this at its original source.

TechDays and RAPI speed

TechDays in Lisbon finished today and it was a blast! I had the opportunity to deliver two presentations (SQL Mobile and .NET CF Performance) and the place was always crowded, but not my presentations, alas. There were several parallel tracks and it was not uncommon to have participants standing in aisles or sitting on the floor... Congratulations Microsoft Portugal!

In one of the recesses, I was approached by one of my customers complaining about DesktopSqlCe performance when inserting data in bulk. After comparing the insertion performance of Data Port Wizard, DekstopSqlCe was an order of magnitude slower...

My customer's code also needed to be optimized, but that was not the root of the problem. It was in my code. How could that be if both products share most of the base code? On one hand DPW was blasting 11,000 rows in less than 30 seconds over RAPI, while DesktopSqlCe was performing in the tens of minutes range. But wait, I thought, I've seen this before.

When DPW was being developed, I soon realized that synchronous messaging was very slow over RAPI. If after inserting a row in a table (even if the cursor remains open) and you wait for the device to send an acknowledgement message, then you are going to be very slow. So slow as to justify the tens of minutes scenario.

To solve this, DPW uses an asynchronous method of communicating and that is why it does not stop when it finds an error - all errors are reported at the end and are collected asynchronously. This is fast, very fast indeed.

When I wrote DesktopSqlCe I wrongly assumed that my customers would make occasional and rare direct table inserts and updates, so inserts and updates were being delivered in a synchronous fashion, i.e. waiting for a confirmation after insertion or update. This situation is now solved by adding a new attribute to the SqlCeRowset class (similar to the CF 2.0 new SqlCeResultSet class): ReportError. This attribute is of type SqlCeReportError and has two values: Sync (default) and Async. Now you know why.

.NET Compact Framework 2.0 End User Download

It's out and you can find it here.

Tools support matrix

Greetings from beautiful Prague.

Just to remind everyone of the Tools support matrix. Actually, this is a funny way of bookmarking this page, huh?

It reminds us that we have to keep all tools under our belt, from eVC3 to VS 2005 (if we are targetting all sorts of devices, that is...).

Waiter! Another Pilsener, please!

Dekuji!

The IDENTITY issue on SQL Mobile is solved!

SQL Mobile refuses to accept values on IDENTITY columns, contrary to SQL Server and Access. The only way to force an IDENTITY value is by changing its seed. In my case, I’m using OLE DB so the IAlterTable::AlterColumn method is used. This has been an issue for me because it would seem to prevent database copying between the desktop and the device. After a bit of thought I realized that I did not need to call the AlterColumn method for every single insert – I could use the auto numbering to generate IDENTITY values for me.

Most databases that use IDENTITY columns will do so for primary key or index support, meaning that these values are generally laid out in row creation order. For a table with few deletions, it is very likely that you can find large continuous ranges for which you can let the IDENTITY column do its job - generate the next value.

I write this after finishing my first tests on Data Port Wizard and a new version of the device component. For "well behaved" databases such as the Northwind sample, the code only needs to change the IDENTITY seed twice per table and performance does not seem to be an issue. On databases with sparse tables (where the number of gaps in IDENTITY values is large) performance may be a concern because the base table cursor must be closed before the seed is changed, and reopen again to continue data insertion.

ActiveSync 4.0 Issue Alert

This issue alert just came out from Microsoft with a request for publication. Here it is:

From: Windows Mobile Update
Subject: ActiveSync 4.0 Issue Alert

Microsoft wants to alert you to an issue regarding ActiveSync 4.0 and the immediate steps the company is taking to resolve it and meet customer needs.

Specifically, some users of ActiveSync® 4.0 have reported challenges when using the USB connection of their personal computer to connect to a device with Windows Mobile 5.0. In many cases, these challenges are likely caused by interoperability with desktop firewall applications or applications that manage network traffic. These applications appear to conflict with the TCP traffic between a device and the PC.

An update for ActiveSync is scheduled to be available in late November, but customers may also resolve the issue manually immediately by visiting visiting http://www.microsoft.com/windowsmobile.

This issue only affects people with new devices just coming to market and running Windows Mobile 5.0 software, and only when synchronizing directly to a PC (wireless synchronization via Bluetooth, infra-red or directly to a server or other services are not affected).

• In the U.S., these devices include the Sprint PPC-6700, iMate JasJar, iMate KJam, iMate SP5, Dell Axim X51v,HP iPAQ rx1955 Pocket PC, HP iPAQ hx2495 Pocket PC, and the HP hx2795 Pocket PC.
• In Europe, these devices include the Orange SPV M5000, iMate JasJar, iMate K-Jam, T-Mobile Vario, T-Mobile MDA Pro, O2 XDA Exec, O2 XDA Mini S, O2 XDA Phone, FSC Pocket LOOX N500, Dell Axim X51, Dell Axim X51v, HP PPC series (rx1950, hx2190, hx2490, hx2790).
• In Asia, the devices affected are the iMate JasJar, iMate KJam, iMate SP5 and iMate SP5m.

Microsoft is committed to solving this issue for its customers and its partners.

For more information, please contact the Windows Mobile PR team at 425.452.5400 or usmedteam@webershandwick.com.

Fast row counting with the ATL OLE DB Consumer Templates

Here is a very fast alternative to the SELECT COUNT(*) method of counting rows in a rowset. The problem with the SQL statement is quite simple - it's slow. So slow in fact that in some situations it is just unusable. Computation durations of over one minute are definite show-stoppers for any application.

The answer? OLE DB, of course!

PRIMARY KEY bliss

One of the limitations of SQL CE 2.0 seems to have been lifted in SQL Mobile Beta 2 (3.0.5177.0): primary key constraints can now have non-unique names. This is interesting because it will remove one of the problems everybody has when exporting to SQL CE 2.0 an Access database that was created with default settings. One of these is the default name of "PrimaryKey" for all PRIMARY KEY constraints which would force a complete renaming of all primary keys before exporting with Data Port...

Now let's wait for some good news on the IDENTITY column issue.

Now, it bites...

There is one nifty trick I can no longer do in SQL Mobile - writing to an IDENTITY column. I just hope this is Beta behavior, otherwise it will break my products.

SQL CE 2.0 allowed consumers to write on IDENTITY columns, a feature that enables copying desktop databases. The table is created on the device with the IDENTITY column set at the given seed and increment. When copying the table data, the column values are forced (the OLE DB provider complains mildly) and the resulting table behaves exactly like the source one. Interestingly, both Jet and SQL Server allow this so why wouldn't SQL Mobile allow it as well?

Where is MSysConstraints?

I write this post with my fingers covered with an anti-burn cream due to the third degree burn I just got. After porting to SQL Mobile a piece of code that relied on the MSysConstraints table, my keyboard immediately turned into a flame-thrower. SQL Mobile does not have this system table (or does not expose it). What hurts me the most is that I had the chance to write this right the first time, but chose to go the easier route...