Windows XP SP3

If you are an MSDN subscriber, go get XP SP3. It's now available on MSDN subscriptions!

Off to Seattle!

Tomorrow I'm off in a crazy trip to Seattle for the 2008 MVP Summit. If you want to meet me, I'll (try to) be here:



Hopefully, I will try to blog about what I can disclose about the Summit and any info I get there.

WTL Helper and VS 2008

My good friend Cristiano Severini has figured out a way to adapt Sergey Solezhentsev's WTL Helper to VS 2008 with a little help from me (I helped him match the VS 2005 typelibs to 2008 because I have both installed side by side). Here's how he did it: WTL Helper and VS 2008.

Crino ROCKS!

Smartphone keyboard handling with GAPI

Here's the scenario: you are working on a DirectDraw full screen application and you require full control of the device keyboard. Things have worked nicely with the Pocket PC and even with the WM5 Smartphone, but when you move to WM6 your keyboard is gone. You do get the WM_KEYDOWN and WM_KEYUP messages, but most of them only report the dreaded VK_PROCESSKEY and ImmGetVirtualKey() does not seem to help at all. What can you do?

I found a solution for this problem that solves most of the issues above, but also uncovers some other issues. The solution is to call ImmAssociateContext() on your main window passing NULL as the HIMC parameter. This will remove the input context association from the window, and you get the keyboard back. But...

... now you have to handle the keyboard mappings yourself. That's the price you pay. On some devices this means having a mixed keyboard (numbers and alpha keys) so you must internally implement a keyboard map in order to make it work as the user expects. To make matters worse, the keyboard map is device dependent so you are in for a bit of work. Finally, I have found that on some devices you don't get all the keys, especially the special keys for mail and contacts. These devices seem to have these keys wired to launch the matching applications and not even GAPI seems to release them...

Smartphone display timeout

How do you prevent a Smartphone (sorry, a Windows Mobile 6 Standard) device from falling asleep? I tried everything, including sending null keys:

keybd_event(VK_NONAME, 0, KEYEVENTF_SILENT, 0);

This nasty little trick works but also forces your backlight to the maximum and you may not want it. What I really want to do is mimick going to the Power Management applet and setting the "Display time out" to "Never". With this setting, your Smartphone device will never go to sleep. So how do you achieve this? It's actually quite easy.

The display time out value is stored on the device registry under:

HKEY_CURRENT_USER\ControlPanel\Power

All you have to do is set the Display value (DWORD) to -1 and make the system know that the value changed. How do you break the news? Simple again:

PostMessage(HWND_BROADCAST, WM_WININICHANGE, 0, 0);

If you use this in your application to avoid the device from falling asleep (I also call SystemIdleTimerReset and SHIdleTimerReset every few seconds), please remember to set the value back to the original value.

Insomnia rules!

Windows Mobile Remote Control - Published

You can now get the latest version of the Windows Mobile Remote Controller application here. This code adds compression to the screen data transport so it becomes a bit faster. Enjoy!

My first hours with VS 2008

My first few hours of work with VS 2008 have been a pure joy. This new beast performs like a champ and has no apparent quarrels with Vista. Upgrading the Data Port Console solution to the 2008 format was a breeze and I was even rewarded with a C# compilation error that had gone unnoticed in VS 2005. The whole product feels much more robust and reliable under Vista and the visuals are also nicer. So far, it has been a great experience. Highly recommended!

Don't worry - if I have anything to gripe about, you will know it.

Installing VS 2008

I finally decided to install VS 2008 this morning. Why so late? I have been very busy and my customers use mainly VS 2005 so I had no urgency to install VS 2008. Now that it has been officially launched I will surely get more and more requests to migrate the products to .NET 3.5 so VS 2008 is a requirement.

Installation went as smothly as possible except for a mysterious shutdown at the end. I was not present, so I cannot tell you what happened but I suspect that the Vodafone 3G card is playing tricks on me. Again.

After installing, I looked at the C drive to check for free space and was appalled to see that it was below 50%. After installing Vista SP1, my C drive used space magically shrunk and has been growing very fast ever since. The culprit? System restore points! After clearing them I got back to post SP1 install conditions. Cool, my disk is back.

Raffael's Blog

Now, here is an ultra-cool blog: Mobile Development! Thank you Chris for the heads-up.

WMDC woes

It's almost a year now since I started using Vista Ultimate. The new OS came on the ASUS VX2 I bought last year and I have found that the overall experience has been very positive. There are a few minor snags like the apparent sluggishness in the couple of minutes after Windows boots and WMDC. This is my major gripe with Vista - I never know when one of my WM5 or WM6 devices will connect or not. One of the quick and dirty solutions I have found is to uninstall the device network adapter, disconnect the device and reconnect. To do this, go to the Control Panel, open the System applet and select the Device Manager link. Under the "Network adapters" tree node, locate the Windows Mobile device and uninstall it. Remove the device and connect it again. WMDC should recognize the device again.

There is an alternative that I have not tested yet and was pointed to me by Mark Arteaga on the PocketPC FAQ site. Anyway this should be an easier experience, especially for people like me who need to connect to everything, from Pocket PC 2003 devices to the latest WM6 devices throwing in a few bizzare Windows CE 5 GPS devices in between...

Windows Mobile Remote Control

Here is the first version of the Windows Mobile Remote Control application. It basically extends the concepts I developed for the Code Project article "A Remote Windows Mobile Screen Grabber". Instead of a blocking RAPI call, this project uses a streamed call so that a live connection persists between the device and the desktop. Mouse and keyboard events are now sent over to the device so you can remotely control your device from the desktop. Please note that this is still a bit crude and will fail under some circumstances, like when you launch a Direct Draw application on the device. For regular GDI applications, it should work just fine (if you can live with 4-5 fps, that is).

Now I must add some way to send the special device keys, like the phone and left / right selection buttons.

Windows Mobile Developer Center

MSDN has revamped the Windows Mobile Developer Center. Go take a look (yes, you will find native code there). I am especially interested in the DirectX samples.

On another note, I will be working throughout this weekend on a Windows Mobile remote control server (something like Pocket Controller). After writing the screen grabber tool, this is an extension I really want to have a go at. Will post the results here as they become available.

Remote Screen Grabber

As promised: A Remote Windows Mobile Screen Grabber

One of the things I tried out during the development of this code to send mouse events to the device. When the user clicked the desktop screen with the device screen capture, a mouse message would be sent to the device via a different remote call. Although a bit crude, it was possible to remotely control the device (with some notable exceptions...). So this set my mind in motion (again) and now I'm writing a streamed version of the RAPI DLL so that it accepts commands from the desktop (such as mouse messages and even keyboard presses) and returns device screens to the desktop. I will post the code when it is ready (and working).

Interview

If you are interested to know a little bit more abut me, take a look at Christopher Fairbairn's blog. Thank you for this, Chris!

Windows Mobile Screen Capture

While waiting for Vista SP1 to hit the MSDN downloads section, I decided to investigate a little bit on an issue that has caught my curiosity time and again: remote capturing of the device screen. There are lots of products on the market that will do this for you, but the question of how it is done kept my curiosity alive.

Searching the net for source code samples turned nothing (blame Live and Google). The best approach I got was from a CodeProject article but the proposed GDI method does not work on Windows Mobile because the CAPTUREBLT flag is not supported. This means that I needed another solution.

GAPI seemed to be a very good candidate. It has a very simple API and I had used it in the past, but it is now declared deprecated by Microsoft and I was prompted to use Direct Draw instead. Willing to learn a bit more about this technology, I decided to give it a go and spent a couple of hours reading through MSDN docs to figure out how this can be done. As it turned out, it's very simple.

To capture the device screen using Direct Draw you need to follow these simple steps (I will write an article on this illustrating a desktop tool to capture the device screen via RAPI):

1. Initialize Direct Draw using the DirectDrawCreate function using NULL as the first parameter.
2. Set the cooperative level to DDSCL_NORMAL. This is enough for accessing the primary surface in read-only mode.
3. Create the primary surface using this code.
4. Get the surface's HDC and use it to create a compatible DC.
5. Create a compatible bitmap using the screen dimensions and select it to the compatible DC.
6. Blit from the surface DC to the compatible DC using BitBlt.
7. Done! The compatible bitmap now contains the device's screen capture.

I wrote this using a DIBSection in order to marshal it via a RAPI call and was able to deserialize it on the desktop and display the device screen capture. I'm planning to use this same marshalling technique to implement a remote file browser using the device's own system image list.

Stay tuned for the article!

Vista SP1 around the corner

According to this, Vista SP1 goes RTM today. I really want to install the SP1 and check the improvements it has in store for me. I have to tell you that Vista was a bit of a let-down when it refused to run eVC3 and eVC4 (I don't care about VS 2003). Microsoft's Virtual PC was also disappointing because it does not support USB, so I had to resort to a third-party VM (Parallels) to get a USB 1.1 emulation. Note that I do not expect that SP1 will add support for the eVC compilers, but I do expect to see some improvements in overall speed and especially when getting out of sleep mode. My Asus VX2 does not always wake up correctly and some drivers may not start correctly. The worst situation happened when the temperature control died after sleeping and the system overheated. I knew what happened because the whole thing shut down abruptly. So I'm really interested in seeing some improvements.

Remote File Viewer

A few days ago a Pocket PC Developer Network forum reader asked for a RAPI remote file viewer (like the one that ships with Visual Studio). I know that OpenNETCF has a free library to support this, but I decided to reinvent the wheel just for the fun of it. The (still crude) result is here. There are a few interesting points to note (especially for the beginner) like how the returned array of CE_FILE_DATA structures is read into a list. Adding file upload and download features should be a snap, now.

WTL on MSDN Magazine

This helps explain why I'm a WTL addict. Shame you don't see articles like these for Windows Mobile...

The collapsible toolbar

Here is an interesting challenge: implement the behavior of this collapsible toolbar. I'm wondering if this can be done by reusing the shell's toolbar control or if the whole thing needs a custom implementation. I will investigate an post back my findings.

After a bit of a rush trying to amend my own lack of testing before publishing new product releases (I should know better at 42...) I will also start looking at what it takes to implement the "Consumer Templates-less" OLE DB library for Windows CE (and Win32, why not?).

DBPROP_SSCE_TEMPFILE_MAX_SIZE

Never set this property to zero - SQL Compact Edition will not open your database... Yes, I just got my hands really dirty with this one.

New year, new life

This is what we say in Portugal: "Ano novo, vida nova" - "New year, new life" meaning that the turn of the year is a great time to ponder about last year and make the changes for the new one. Focusing deeper on less things is definitely something I will do this year. I just created a new blog for all the issues that pertain to my work at Primeworks and will keep this for all the other stuff, especially the native code stuff.

The community project that I chose to focus on is to create a replacement for the ATL OLE DB Consumer Templates. More and more I see native code developers looking for an easier way to access SQL Compact Edition databases from C++, and my solution for tweaking the PPC 2003 SDK header files does not feel right. Besides, Microsoft does not ship the Consumer Templates header files anymore so this must mean that this code is not supported (but is it licensed?). Developing a new library for OLE DB on devices will surely be an interesting challenge and will keep me busy writing code and publishing articles throughout 2008.

Once again, thank you Microsoft for the fifth MVP award!

Detecting the SDF file version

SDF files don't have an explicit way of letting you know their versions. What can you do to tell the version of a given SDF file? In my products I detect the SDF file version as a side effect of opening it: if a given engine can open that SDF file then I can safely assume it has the same version as the engine. This is not a very straightforward approach because it requires that you have all engines installed on the device (not a typical scenario).

The alternative way to detect the SDF file version is by reading a few signature bytes on the file. Just open the file, set the read location at offset 16 and read the 32 bit int there. The following values will tell you the file version:
2.0: 0x73616261
3.0: 0x002dd714
3.5: 0x00357b9d

I wrote a very simple .NET CF 2.0 application to illustrate this. The download link is this. If you find any SDF file that fails this test, please let me know.

WM6 GPSID problem workaround

John Spaith from Microsoft has blogged about a workaround solution to the HTC GPS problems on WM6. Apparently the size of the GPS_POSITION structure must be tweaked in order for the call to GPSGetPosition to work, but this can only be applied on devices where the first call returns 87... I actually worked around this by falling back to the COM port.

COM Port Enumeration

If you want to enumerate the available COM ports on your WM device, there is a very simple solution: look under the HKEY_LOCAL_MACHINE\Drivers\Active for all keys that have the Name value starting with the "COM" string. With some luck, you can even find a FriendlyName value under the key specified by the Key value.

The only problem with this very simple method is that it does not enumerate the Microsoft Bluetooth stack emulated COM ports. If you create a partnership with a device that has the serial port profile, you will be able to assign a free COM port to it. This COM port works like any regular serial port but does not show up in the list of active drivers on the registry. Microsoft decided to enumerate all Bluetooth virtual COM ports under HKEY_LOCAL_MACHINE\
Software\Microsoft\Bluetooth\Serial\Ports. To find all available COM ports you must enumerate all the child keys and look for the Port value under each key.

Interestingly, the Widcomm stack does put the virtual COM ports where they should be (or at least where I expected them to be).

VS2008 Install Heads Up

Before you install Visual studio 2008, make sure you read the readme file. There are two important implications for native and mobile developers: SQL CE 3.5 must be uninstalled prior to installinv VS2008 and the Windows Mobile 6 SDK will have to be uninstalled and reinstalled after installing VS2008.

VS2008 is out!

Check out the subscriber downloads in MSDN!

Windows Live

Off-topic: Today I noticed that for the first time in the last few years Google is not on the top of the list of search engine referrals on my comany's site web log data. Out of the blue (or maybe not) Windows Live is leading the searches! This is intersting news for a chart that used to show a huge Google bar and essentially nothing else.

Performance Improvements in VS 2005

Jim Springfield has just posted in the Visual C++ Team Blog a very interesting piece of information: there is a new performance improvement QFE for Visual Studio 2005. This download addresses a number of performance issues and its features will roll over to VS 2008. I'm getting it right now to give it a spin.

Carousel Demo

Now that the carousel code is working correctly across a range of devices, I decided to upload it as a preview (download here). The code illustrates the carousel animation and user control as a strip on the child window of the WTL main frame. I'm still wondering how this "control" should be implemented, either as a "widget" on the main frame or as a toolbar-like control.

The main frame approach would implement the carousel code in the main frame allowing it to correctly resize the child window and even allowing the application to change the child view (like in a property sheet). With this approach it might also be easier to implement a means to change the location of the bar whan the screen is rotated. In fact, the carousel looks much nicer in a vertical position when you rotate your screen to landscape (right or left would be up to you).

Implementing this as a toolbar control would have the advantage of allowing the developer to reuse the code outside the main window, especially on dialogs and modified property sheets...

While I think about what to do, please do take a look at the code. The icons were "created" with Axialis IconWorkshop, one of the best icon editors around. Please note that these icons can only be used by registered users of the product and I'm not sure that you can reuse them on your applications.

GradientFill and a memory DC with a viewport

I’m working on a “carousel” control similar to the one you can find in the Yahoo Go! 2.0 application. This thing showed up in my HTC S620 WM6 upgrade and the control is very cool, so I decided to write my own implementation of it in C++ using WTL 8.0. The scrolling animation is achieved by painting to a memory DC and then blitting it to the screen. All went smoothly until I decided that the carousel background would look so much nicer with a gradient, making the center item stand out in a lighter background.

WTL 8.0 does a lot for you and I found myself having more work creating the icons for the sample than actually implementing the code... I started by using the CMemoryDC class which worked beautifully until I used GradientFill. All other GDI functions work correctly, but the gradient refused to appear correctly or at all. Interestingly, when the carousel strip was set at the top of the client window it would paint correctly, but would fail at the bottom.

Looking at CMemoryDC's source code I found that the constructor sets a viewport origin in order to cope with any rectangle. When setting the carousel strip at the top, the gradient would show up because the viewport origin would match the child window's (0,0), but fail when set to the bottom because the viewport origin would be different.

I ended up by using a slightly modified version of CMemoryDC, by removing the SetViewportOrg call on the constructor and zeroing the source bitmap coordinates at the destructor's BitBlt call. The carousel now works correctly on all four window edges (top, bottom, left and right).

Presently the carousel is implemented as a child view window but I'm looking at ways to make it work as a toolbar.

HTC P3300 GPS problems (again)

After posting in January about the failed implementation of COM events on the GPS port of the HTC P3300, I have another sad story to tell. After upgrading the device to Windows Mobile 6, the GPSGetPosition function always returns 87 (ERROR_INVALID_PARAMETER). There seems to be no appropriate set of parameters that this thing will take, you will always get back an error 87. Great! The GPSID on the P3300 is useless. The solution is to fall back to the COM port code...

Access 2007 woes

I don't have any complaints about using the Access 2007 application whatsoever. After the not so brief period that took me to learn the new user interface (some stuff on the older UI was quicker to use), the experience is quite smooth. Not so on the nuts-and-bolts side of the question.

As you already know I'm an OLEDB freak, and I use it for all native code database access I write both on the device and on the desktop. Last year I did write a sizeable amount of code for my data synchrnization component (Data Port Sync) and this targeted the JET 4.0 database engine. The code I wrote implemented both the Access database preparation and also change tracking. Database preparation involves creating a few extra tables and adding two columns to every tracked table. This looks pretty simple to do using straight SQL, but when I consistently failed to create self generating "replication id" columns (the SQL equivalent of the uniqueidentifier type with the rowguidcol property) I turned to the low-level
ITableDefinitionWithConstraints OLEDB interface to do all the table creation work. The code is not nice to see, but it is effective and gave me an added bonus: I can hide the tables from the user. To add the extra columns to the tracked tables, I used the
ITableDefinition interface.

Recently I started to get requests from my customers to support the new Access 2007 file format on my data transfer products. Changing the provider from Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0 did the trick quite nicely because Microsoft kept all the other connection string properties untouched. BTW: where can I find a reference to all the properties and most importantly, where are the new header files for OLEDB?

The first implementation of the Access 2007 database engine was shipped on the Data Port Console release. Although it does not create the mdb or accdb files (yet), it successfully imports and exports data to and from the various SDF formats.

Going back to the desktop synchronization code (which will eventually see its way into the Console) I recently began to write a small desktop agent for simple desktop scenarios. Putting the new code to work was quite easy until I decided to test the code against an Access 2007 database. I picked up the old Northwind sample and converted it to the new engine only to find that my preparation code would fail miserably. There were no issues when the tracking columns were created, but it proved next to impossible to create the tracking tables: the engine would throw an E_FAIL error with a very strange OLEDB error description: "Could not find field." Period. I changed every possible factor that could be causing this: column names, the table name, absence or presence of delimiter brackets. Nothing would work. Out of desperation, I replaced the code with a straight SQL DDL command only to be greeted with a similar error: "Could not find field 'SID'." I beg your pardon? There is no 'SID' in my command! Has Access gone mad?

No, Access is working perfectly but the accdb file seems to be corrupt. After reverting all the code back to what it was, I decided to create a new accdb from scratch and then import new data from an SDF using the Console. After running it through the preparation code there was no error! Is the database conversion code broken in Access 2007?

Bottom line: don't convert mdb to accdb. Create a new accdb and then import the old data into it. Sigh.

The edit control under Windows Mobile 2003 and 5.0

After publishing my last article, I found out an interesting thing about the edit control on a Windows Mobile 2003 Pocket PC device: the up and down arrow keys behave differently from the edit control in Windows Mobile 5. In WM5, the single line edit control responds to the up and down navigation keys by setting the focus to the parent window. When this happens, the parent window receives the corresponding WM_KEYUP messages and thus is able to set up a navigation mechanism. Not so with the Windows Mobile 2003 edit control. This version of the control seems to "eat" these messages so the parent has no clue about the requested user navigation. This became quite apparent on my implementation of the CFormListCtrl: the navigation would not work.

This problem was quickly solved by adding the OnKeyDown message handler to the CCeEdit class and forcing the control to set the focus to the parent window whenever an up or down navigation keys are detected. Voilá!

A list-based form for Windows Mobile

I have just published an article on CodeProject that better describes the list-based form control. As you can see from the article, I added a few other bells and whistles to the code.

Next two projects: port this thing to WTL and start writing an OLE DB class library for Windows Mobile. This second project is necessary because Microsoft no longer ships the atloledbcli.h file on the SDKs and this must mean something...

Bug in the FormListSample

There is a bug in the FormListSample that causes a stack fault exception if you close the dialog with a created item control (edit, combo or date time). I'm looking at this right now.

Solution: On the CFormListCtrl::OnDestroy method, set the m_iEdit member variable to -1 after calling ShowEditor.

Meanwhile, I added some code to handle edit control resizing when the screen is rotated. The code can be found on the same location.

The list-based form control

Back in 2003 when the Smart Device Extensions were still in Beta and about to be renamed .NET Compact Framework, some people had to use C++ to develop database applications. I was one of these developers struggling with a crude set of tools: ADOCE for database access and the eVC3 dialog editor to design the user interfaces.

Soon we learned that ADOCE would be deprecated and so I started to look at the ATL OLE DB consumer templates. This is a great technology and I still fail to understand why it is not present on the WM5 and WM6 SDKs. In my code, I use an adapted version of the header file you get on the Pocket PC 2003 SDK but to be honest I don't like this approach (I will come back to this issue in a future post).

On the user interface side, we already had a very nice alternative to dialogs: the list-based form control used by the Pocket Outlook Contacts application. Not only this is an intuitive control for the user, it also promised to be easy for a developer to use: no designer needed to be involved, you just specified the list of items to edit and fired the object.

The big problem with this control was that there was no API for it so I had to write one. Back then I was using MFC big time due to the compiler support for it on eVC3 and eVC4. Now I am not so sure that MFC is a good option for native user interfaces and I'm leaning towards WTL. You now have great tool support in VS 2005 with the WTL Helper tool which I am using on a daily basis and with great results.

So I eventually wrote an MFC control to implement a list-based form and in the process I threw in couple of goodies such as grouping, context menus for managing the items, control notifications and a few other bells and whistles. After a recent request of fellow MVP Christian Forsberg for a similar control, I decided to go back to my 4 year old code, adapt it to VS 2005 and publish it. I had to do more work than expected because nowadays there are lots of devices that have a keyboard, display in landscape and even have VGA screens! Back then I was used to portrait QVGA screens and no keyboard, so some changes were in order.

Before I publish this code as an article, I decided to preview it here so you get a chance to look at it and let me know what you think. Download FormListSample.zip

Supporting SQL Compact 3.5

Native code is so cool! As of today I can write applications that target SQL CE 2.0, 3.0 and the new 3.5 Beta 2. The trick? Extend this technique to the SQL Compact 3.5 header files. Microsoft made our life really easy by making very small changes to the header file, so you only need to copy the definition of CLSID_SQLSERVERCE_3_5 and all the property definitions of
DBPROP_SSCE_DBENCRYPTIONMODE. Now your header file can be used for 2.0, 3.0 and 3.5!

What this means for me is that SQL Compact 3.5 will be supported by the release version of Data Port Console and I will also issue a last version of DesktopSqlCe that will also support the new database engine. This will be the only game in town for .NET 2.0 desktop applications that must support both the 3.0 and the 3.5 database formats (without code changes, of course).

Cool!

SQL Compact 3.5 Beta 2 and VS2005

Ready to move forward and add SQL Compact 3.5 support to my products, I downloaded the Beta 2 bits in the hope to find the required header files. Unfortunately they were nowhere to be found. Does that mean that you cannot use VS2005 with SQL Compact 3.5 Beta 2? No. Fortunately there is an indirect way to get the much-needed header files: download and install either Orcas Beta 2 (not yet for me) or Microsoft Visual C# 2008 Express Edition Beta 2. Apparently the header files are also available on the Visual Basic edition.

Now back to work.

Database Diagrams for Data Port Console

The second release candidate of Data Port Console 1.0 is almost out and while I was removing some bugs, I failed to resist the urge to bring back some code that had been sleeping for amost one year: database diagrams. Yes, coding should also be fun so I decided to add this to the 1.0 and in this post I'm offering my readers the chance to try this new feature. You can create multiple diagrams per database and now all information is persisted as an XML file instead of using the registry. You will also notice that there is a new folder for tables and that you can group them by their first letter. Please let me know what you think about these new features while RC2 is not uploaded to the site. Grab your copy of this interim build here.

ZIP and RAPI - The article

My latest musings on this subject have been published on CodeProject. Read the article here.

ZIP and RAPI - Yes, it's faster

I really was not pleased with the performance results I got from the RAPI Unzip extension I published in my last post. My reasoning was that if you send a compressed data stream to a device and decompress it you should get a better performance than decompressing the data on the PC and sending it to the device. The results I got were not very satisfying: the RAPI extension DLL was not clearly faster than the PC decompression, especially for ZIP files with small compression rates.

So I decided to take a harder look at the code and found the culprit: WriteFile latency. The function was being called way too often and sometimes with small sets of data. If you look at the code, you will see that WriteFile is called when either the decompression input or output buffers are emptied. This is wrong. The call must only be made when the output buffer is empty and not when the input buffer is. When the input buffer is emptied it must be refilled from the RAPI stream and the output buffer should not be flushed, otherwise we risk flushing a small amout of data expending one WriteFile call.

The final result is now ready and is consistently faster than the PC decompression approach. Try it!

ZIP and RAPI - Is it worth it?

After a few more days working on Lucian Wischik's Zip Utils code I finally managed to build a working RAPI ZIP decompression server. The architecture of this RAPI extension is quite simple: it exports a single method (ZipServer) that implements a streamed RAPI call. Streaming (non-blocking) RAPI calls are very nice because they allow you to keep an open connection with the desktop which is especially useful when expanding very large compressed files: you can slice the compressed stream and have it decompress on the device. For more implementation details please see the source VS 2005 project in the above link.

To make this work I changed the CeUnzip project in order to support calling a RAPI extension and added an extra mandatory command line argument: -i for expanding on the PC from a ZIP file on the device and -e for the exact opposite operation. It's here that I make the remote call to the RAPI extension DLL. Please note that the two projects use different versions of the unzip.cpp file. I will correct this before publishing an article that describes in greater detail the inner workings of the code.

How about results? Is this really faster than using an on-the-fly decompression algorithm on the PC and using CeWriteFile to write the decompressed data on the device?

My first results were very disappointing. When compared to an installer application I wrote for a customer that decompresses on the PC and writes to the device, I consistently got worse timings. The only remedy that made the device DLL run as fast as the PC was to increase the IRAPIStream read buffer size to 128 or 256 KB. As a matter of fact, RAPI read latency is a big issue here. Before giving up I looked at the particular ZIP file I was using to test: it was quite large and had a relatively small compression ratio. Maybe this explains something?

My next test was to use a very high compression ratio ZIP file and run the same tests (expanding it to the device using the two different approaces). This ZIP file was built from a very large SDF file which almost disappeared when compressed: 193 KB to only 3 KB. Now the results were a bit different...

Depending on the device, the DLL expansion was from 2 to 4.5 times faster than the PC's. This means that for compression ratios closer to 1 it is (almost) indifferent which approach to use, but when the ZIP compression ratios get larger, it's far better to use the server DLL.

ZIP and RAPI

For quite sometime I have used Lucian Wischik's Zip Utils, a set of zipping and unzipping functions that work very well both under Win32 and Windows CE. This code has allowed me to do a lot of fun stuff both on the desktop (specialized installers) and on devices (an HTML browser for ZIP files).

Recently I had to use this code to write a specialized device installer (no cabs) for a consumer application that runs on all sorts of devices. This installer was designed to extract files from a ZIP on the desktop and copy the extracted files to the device over an ActiveSync connection. I used Zip Utils to extract the data and RAPI to copy the expanded files to the device. This works but can be slow on some devices. Wouldn't it be great if I could expand the files on the device while having the source ZIP file on the PC? I would surely make the whole thing faster because the RAPI transport times are a big bottleneck in this whole process.

Due to time constraints, the installer is now shipping like this: data is expanded on the PC and copied to the device. Now I'm starting to write an application that will update the device code and data. Interestingly most of the static data is stored on ZIP files on the device and the updater must extract specific files on the ZIPs in order to know some details. The problem is that some of these ZIP files are over 2 MB and copying them to the PC just to extract one tiny file is out of the question due to slow transfer times.

No options here: I had to roll up my sleeves and started to read Lucien's code in order to see what could be done. This was no walk in the park because he uses a very packed code indentation, and understanding the code was not easy at first. Then I noted that all the ZIP file access methods were neatly encapsulated in specific functions (like lufopen, lufread and so on) so it would be quite easy to replace the desktop file access functions with RAPI's.

My approach was to expand the LUFILE structure and include a set of function pointers that would be initialized with the regular API when the ZIP file is on the PC and with RAPI's when the ZIP file is on the device. After 20 minutes the code was working and I was able to write a very simple command line unzipper (get the code here).

Now I want to solve the first problem I had: efficiently unzipping a file to a CE device. This will require a RAPI DLL on the device that will have all the decompression code. When this is ready I will publish the results in an article. Stay tuned.

Bitten by the desktop GC

If you downloaded the Console Beta 4 I published yesterday, you might have noticed how badly it bombed when exporting an SDF database to Access. The error message you see usually means that some native code DLL is writing to NULL pointers or doing some other equally responsible stuff. In this case what you see is a NULL pointer dereference (yes, that bad).

While running perfectly in Debug mode, the error almost always showed up in Release mode. When exporting very short databases or single tables it might not show up. But when I tried to export the Northwind sample the Console would bomb exporting one of the largest tables (Order Details or Orders). The keyword here is random because as it turned out, the error was being caused by the garbage collector (well, my bad code was causing it - the GC just made it apparent).

The Access database code was implemented in C++ with a similar approach as DesktopSqlCe's low-level classes: there are separate classes for the table structure and the table cursor. In order to open a base table cursor and insert data on the table (no SQL is used for data manipulation) the code creates a table object and "opens" it returning a "rowset" object. My mistake was to have the table class include an ATL CTable-derived object, not a pointer to it. The CTable-derived class contains all the accessor and rowset logic that are used by the rowset object. Making it static is a bad idea because the desktop .NET GC did not know about this and kills the table object because it thinks it is no longer needed. After all the only active object is the rowset... Now you see the problem: by killing the table, the rowset is implicitly killed as well because it shares an object that is statically owned by the table. Closing the rowset means that a few internal pointers will be deleted and set to NULL. Bang! Am I smart or what? (The answer is "No").

I uploaded a revised Beta 4 that solves this issue, so if you installed the one I uploaded yesterday please replace it with the new one.

Data Port Console 1.0 Beta 4 available for download

I have just uploaded the new Data Port Console Beta 4. As previously promised, import and export to Microsoft Access (2002-2003 and 2007) is now supported and this looks like it will be the last Beta. Now I will focus on cleaning a few bugs and will try to have the final version 1.0 by mid-August.

What's next? Primeworks.Data - the merger between DesktopSqlCe and Data Port Sync with a few "benefits" such as the ability to make P2P connections via Bluetooth or WiFi between two devices. Also I will add support for the JET (ACE) engines on the desktop server and will publish the services via a muti-threaded Windows Sockets server (I don't anticipate the need for IOCP anytime soon because there will be few concurrent connections).

The new Beta 4 can be downloaded from here.

Bluetooth Service Publishing

The code for my forthcoming article on Microsoft Bluetooth Stack inquiry and discovery (remember, inquiry is for devices and discovery is for services) is ready. Apart from inquiring the available devices, it also discovers the existing services and lists the SDP record contents.

After being able to find a device with the service you want to consume, you can connect to that device using Windows Sockets. The reverse process involves publishing a service for other devices to consume and to serve requests using WS. Unfortunately the SDK has no means (nor explanation) on how to create an SDP record. Platform Builder users have the BTHNSCREATE sample to play with, but what about us, mere SDK users? Looks like Microsoft thinks this is something that should only concern people designing new Windows CE or Windows Mobile devices. I sincerely hope not and I'd rather think this is a design oversight. Why would I need such a thing?

Well, one of my plans is to provide P2P support for my products. If you look around the newsgroups you will see lots of people asking for an SDF-only synchronization package. This will require that two SDF files can "talk" to one another. I already support having a PC talk to an SDF file on a device, but what about having one device talk to another? A year ago I actually achieved this using WiFi, but this is not the correct protocol for ad-hoc P2P scenarios - Bluetooth is better. So if I want to support this scenario, I must publish a new Bluetooth service. Yes, of course I could use emulated COM ports and the code already supports serial communications. But I want the real thing: a dedicated service with its own GUID.

So how am I going to circumvent this? Most likely I will use Doug Boling's solution: use a templated SDP record and fill in the gaps (not nice, I know). What really bugs me is that the Widcomm stack makes it so much easier to publish a new service...

SHMENUBAR and MF_GRAYED

My WTL 8.0 experience with Windows Mobile development is going just great. The application wizard worked out correctly with only a very minor issue: the WTL include directory is not set up either for C++ compilation nor for resource compilation. As I said, this is a minor issue that you overcome quite easily.

While working for the new Microsoft Bluetooth device inquiry and service discovery (these seem to be the appropriate Bluetooth terms) I found out that Sergey Solozhentsev's WTL Helper tool works like a charm in Windows Mobile projects under VS 2005. All of the message handling functions for the main frame window were generated with this tool and it makes it very easy to write all the UI code - this tool just rocks.

Today I found out about something that does not seem to be documented anywhere: when using the SHMENUBAR resource, never specify a menu resource with a grayed (MF_GRAYED) menu item - the menu will not show up. The WTL code is very straight forward and uses the SHCreateMenuBar API, so this was not where the problem lied. After removing the grayed style from the resource editor the menu magically showed up! So is this a bug on the shell or on the compiler?

Back to Bluetooth the WTL way

I'm writing this post from (today not so) sunny Algarve using my brand-new Vodafone Mobile connect card. This thing really works but the Vista version of the software seems to be a bit slow when booting. Also, if I boot the machine with the card plugged in the driver will not find it. Apart from this it's a treat - I get internet access pretty much wherever I want to (coverage permitting).

I'm coming back to writing about Bluetooth on Windows Mobile devices and this time I'm exploring the Microsoft stack. Using MFC code I had written almost a year ago, I'm now turning to WTL to rewrite the sample for the article. So far I can tell you that the Windows Mobile WTL wizard looks quite competent at generating the basic application code. More impressions on this experience will be published here. Stay tuned.

CeGetDiskFreeSpaceEx - II

This is not an ActiveSync issue: my iPAQ 2210 does not support this API nor do some Windows CE 5.0-based GPS devices I've been working with. At the end of the day, I had to write a RAPI DLL to support this. The code is quite simple:

typedef struct _DISKSPACE
{
    ULARGE_INTEGER uliFreeUser,
                   uliTotalSize,
                   uliTotalFree;
} DISKSPACE;

__declspec(dllexport)
int DevGetDiskFreeSpace(DWORD cbInput, BYTE* pInput,
                        DWORD* pcbOutput, BYTE** ppOutput,
                        LPVOID pReserved)
{
    DISKSPACE ds;
    BOOL      bOk;

    bOk = GetDiskFreeSpaceEx((LPCTSTR)pInput, &ds.uliFreeUser,
                              &ds.uliTotalSize, &ds.uliTotalFree);
    if(bOk)
    {
        *ppOutput = (BYTE*)LocalAlloc(LPTR, sizeof(ds));
        memcpy(*ppOutput, &ds, sizeof(ds));
        *pcbOutput = sizeof(ds);
    }

    return 0;
}

Of course, you need to put this in a device DLL and consume it as a blocking RAPI call.

CeGetDiskFreeSpaceEx

This is one of the cases where you do have to dig into the SDK header files to find what you need. The CeGetDiskFreeSpaceEx RAPI function is not documented on my VS2005 MSDN, although you can easily find it on the online MSDN. I have to confess that I only found it after looking into the Windows Mobile 5 Pocket PC SDK rapi.h file. The online documentation says it is available since Windows CE 3.0, but a quick look at the same header file on the Pocket PC 2003 SDK is enough to see it is not there. So how do we decide who is right, the docs or the header? The answer is simple: use dynamic linking to the rapi.dll, and you will find out if this is supported or not.

During my furious searches on the net, I found an obscure thread posted by someone from Microsoft stating that this API is only exposed from ActiveSync versions 4.0 onwards.

Data Port Console 1.0 Beta 3 available for download

Right after uploading the Data Port Console Beta 3 (*), I decided to implement the TCP/IP sockets connectivity feature you could use in the old SQL CE Console. Extending the code was easy enough and I was able to do something that was impossible with the old code: connect to more than one PDA at the same time. This is going to enable some interesting scenarios where just one console will be able to manage mutiple PDAs connected to the desktop via a network. For GPRS scenarios this may need a mobile VPN to fix the device IP address. I've been thinking on how to go around it seems that the only way is to somehow revert the PDA and PC roles to a somewhat more "natural" way where the PC is the server and the PDA is the client, and not the other way around.

Next up is to enable the serial port connectivity for Bluetooth emulated COM ports (the code is there, just needs a little work). I would like to have a native Bluetooth connectivity option in order to make it simpler for users to set up their devices, but this will imply going back to my Bluetooth code (which I started to write for this very purpose...).

Where do I want to go last? Device P2P. In a few days I will start working on the DesktopSqlCe successor that will also run on devices, so your mobile applications will be able to consume SQL Compact Edition databases locally or on a nearby device.

Back to work.

(*) This is not a real Beta. I will keep adding features to the application while it is almost ready to go live and after that as well. Beta status will stop when data can be exported to an Access database and when the number of errors is reduced to a sane minimum.

Primary Key or Index? ROWGUIDCOL or Guid.NewGuid()?

If you are really worried about the performance of your SQL Compact Edition database, switch all your primary keys for unique indexes and always let the engine generate the UNIQUEINDENTIFIER's GUID value through the ROWGUIDCOL property - never ever use the Guid.NewGuid() function.

Looking for more performance material to publish about SQL Compact Edition, I found myself testing the performance of GUIDs when inserting on an indexed table. Performance will be marginally slower when you insert a GUID on a PRIMARY KEY column than when inserting it on a UNIQUE INDEX. In a 10,000 row insert sample it can be as low as half a second, but nevertheless this is an interesting result: PKs are slower.

Now, if you remove the ROWGUIDCOL property from the UNIQUEIDENTIFIER column and start generating the GUID values yourself, be prepared for a big surprise: the insertion time tripled (10.5 to 30.7 seconds on my battle-scarred JasJar). Is the Guid.NewGuid() function that slow?

Change MSI name in VS2005

Why can't we change the MSI file name on a VS2005 deployment project? When you create a deployment project with VS2005, the name of the resulting MSI file is the same as the project's name. From this point on, there is no way to change the output file name from the project properties (nor from anywhere else on the IDE, or is there?). Even if you rename the project on the Solution Explorer tree, the resulting MSI file name will not change.

The solution for this is to manually open the .vdproj file and edit it on a regular text editor (Notepad will do). Now search for the "OutputFilename" string under the configuration name of your choice (by default you will see "Debug" and "Release" under the "Configurations" group, but your project may have other configurations), and change the MSI file name. Save the file, open the project in VS2005 and rebuild.

Request for Orcas: can Microsoft please make this property available on the IDE?

EDIT: Someone smarter than me just showed me the way... Thanks, Pedro!

More troubles installing WTL Wizards in Vista

WTL 8.0 is out now so I decided to install it on my Vista box. Now that I know a bit more about Vista security, I launched a command window in Administrator mode in order to run all the setup scripts (desktop, Windows CE and Windows Mobile). All went smoothly.

Then I decided to install Sergey Solozhentsev's WTL Helper tool. No issues again (nice!). This great tool requires that you download and install a new WTL Wizard from Sergey's website. Instead for using a JScript file to install everything, Sergey's Wizard uses an MSI file, and this is when I felt the iron grip of Vista security again: the MSI just would not install reporting an error number 2739. My friend Google pointed me in the right direction, first to understand what this error means, and what to do about it.

For your information, I did not remove the JScript key but merely renamed it. Running the msiexec /i command from the admin command window did the rest of the trick.

Data Port Console 1.0 Beta 2 available for download

You can get the latest Beta 2 from this link. There are a number of bug fixes in this Beta that make it more stable and reliable. Error reporting has been improved but this requires that you update the device component using the included installer. As a matter of fact, users would be greeted with empty error dialogs because of how the OLE DB error information was being collected.

One of the features I added for this Beta was a special request of Alberto Silva: the ability to edit the query data grid (when the user enters an "updatable" SELECT). Behind the scenes, the code just uses a SqlCeCommandBuilder to create the necessary commands (displayed on the notes tab) and these are used for updating (use the save button). You can also filter the grid by selecting the row state and the changes can be rolled back row by row (before updating, of course).

Comments and suggestions are welcome!

Tuning SQL Compact Edition Insert Performance

Here's how you can improve on a good thing: speed up your bulk insertions in SQL Compact Edition up to 30%! Read all about it here.

DBPROP_SSCE_MAXBUFFERSIZE

Looking for ways to make batch insert work faster under OLE DB, I tried to fiddle a bit with the SQL CE 3.1 database properties. My first approach was to radically increase the maximum buffer size to 2 MB using the DBPROP_SSCE_MAXBUFFERSIZE property. The early experiments look promising with speed performance increases in the 50% range (yes, that much). I promise to come back to you with a better case in the form of an article where I will compare insertion performance under different scenarios and property settings.

For now, keep this in your mind: increasing the value of DBPROP_SSCE_MAXBUFFERSIZE may actually help your database perform better.

MFC Revisited

A few weeks ago, Mike Hall posted about "Why use MFC on Windows CE?" This was a bit of a surprise to me because you hardly see Microsoft discussing MFC these days. When the Compact Framework was launched, native code got such a bad press that I thought Microsoft was about to kill its C++ developer base. Fortunately I was wrong.

I (re)started developing for mobile devices in MFC and that was a very interesting experience. In those days the best PDA around was a Compaq iPAQ 3870 with Bluetooth and your development environment of chioce was eVC3. MFC seemed to be an obvious choice due to tool support but there were a few shortcomings. The frameworks imposes a document-view model that sometimes you have to fight against. Documentation was scarce (yes, the online help was bad) and your best resource was to read the MFC source files. This was how I got to understand how the framework was designed and why the tap-and-hold always made the dots circle twice. Interestingly you can see this behavior in a lot of the most recent devices on the Settings applets meaning that these are written in MFC (and the double circling bug is still there - sigh).

There is one good reason why you should not use MFC: it is slow. I understood this when I was working on the SqlCeSpy code and found that MFC would refresh a virtual list view noticeably slower than other applications, especially the ones written in ATL. MFC's message handling mechanism is a bit complex and convoluted meaning that when you need to handle a reflected message it gets bounced back and forth before it actually gets to where it is needed. The result for me was a slow refresh of the virtual list view. So slow in fact that it prompted me to write a grid-like control using virtual methods instead of reflected notification messages. Come to think of it, I never published this code...

So what is the best choice? As far as I can tell, when handling a user interface in native code, your best choice is to use WTL, ATL's extension now freely available from sourceforge. The thunk mechanism for handling the message maps make it very fast and efficient to process windows messages and applications tend to be both faster and smaller (compared to the statically linked MFC applications). Also, you feel like having much better control of what is going on and the templated approach is very elegant, although it requires conquering a steeper learning curve. But once you're there, you're hooked.

Installing the WTL 7.5 Application Wizards in VS 2005 under Vista

Don't even try to use the install script: it will not work. Vista's security mechanisms prevent the script from doing anythig on your VS 2005 install directories. I just went through this situation this morning and realized there was just one way to go about it: manually.

It's not that difficult, but took some time to reverse-engineer what the script was doing. The first thing you need to do is to locate the VS application wizards directory. In my case it's in:

C:\Program Files\Microsoft Visual Studio 8\VC\vcprojects

Under this directory you must create a new one named WTL (what else?). Now go to the directory where you installed the WTL 7.5 files and copy the following files:

WTLAppWiz.ico
WTLAppWiz.vsz

to the vcprojects directory. You will need administrator clearance to do this. Now change the security settings of WTLAppWiz.vsz to allow editing. Change it so it looks like this:

VSWIZARD 7.0
Wizard=VsWizard.VsWizardEngine.8.0
Param="WIZARD_NAME = WTLAppWiz"
Param="WIZARD_VERSION = 8.0"
Param="ABSOLUTE_PATH = D:\src\WTL75\AppWiz\Files"
Param="FALLBACK_LCID = 1033"

Replace the D: path with your own WTL 7.5 directory. Finally, copy the WTLAppWiz.vsdir file to the WTL directory you created before and, going through the same security checks you did for the vsz file, change it so it reads:

..\WTLAppWiz.vsz ATL/WTL Application Wizard1An application that uses the Windows Template Library. 67774096#1154

You are done.

Data Port Console 1.0 Beta 1 available for download

I have just uploaded the Data Port Console 1.0 Beta 1 setup file. This is the first public Beta of the product and it already has the license mechanism in place, limiting the use to 30 days. For the people I promised to offer a free license, now is the time to collect it: please send me an email with your desired license name so I can issue the key.

There are some new features wothy of notice:

• Connection breakups are detected and handled. This means that if you inadvertently break the USB connection both the desktop and the device will gracefully recover (the device may take a few seconds before it becomes responsive, but this is a RAPI issue).
• The device component is now manually installed from a specific menu. I have included a very comprehensive set of device cabs: Pocket PC 2003, Windows Mobile 5 (both Pocket PC and Smartphone), Windows CE 4.2 (all CPUs) and Windows CE 5.0 (all CPUs). Hopefully the WM5 cab will install correctly on WM6 devices.
• The new version of DesktopSqlCe (1.8) is included (the console is built on top of DesktopSqlCe 1.8) and the Gold will ship with a single user license of this component. This means that if you just want to use the component in one PC (the same where the Console is installed) you will not have to pay the extra DesktopSqlCe license.

I'm really interested in reading your opinions on this product and what features you would add / remove / change.

Diagnosing cab installations

This is an old time favorite but I seem to keep forgetting it. So for my future reference, here are the registry keys for enabling cab installation diagnostics on the PC:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AppMgr\ReportErrors = (DWORD)1

The dangers of not terminating your strings

After a bit of a long break I'm here to tell you about my latest wild bug hunt. A few months ago I was contacted by a French company to develop a high-speed text to SQL CE converter. This component allows the user to send plain or compressed text files to a device and locally process inserts, updates and deletes. Due to its very nature, the code was supposed to accept either UTF8 or UTF16 character encodings.

My first approach was to use the text conversion functions (wcstombs and mbstowcs). This way I had only one set of conversion functions (I mean converting from text to a GUID, to a DBTIMESTAMP and so forth). After the first tests the customer complained about stability: the data would be written to the database but the component DLL crashed almost consistently.

Back to the eVC4 debugger (now running on my Vista box in a Parallels virtual machine) I was confronted with an ugly beast. After successfully running the code, the whole thing would blast into hyperspace when closing the data source object (yes, that's the last thing you close). Hunting the bug forced me to look everywhere (even in the tested ATL OLE DB code). The thing was nowhere to be found. Often times I had to hard reset the debug device in order to get it back to its feet. Not a nice thing...

Bugs like these "smell" like memory issues: you write to a wild pointer and you may destroy some very fundamental data structures in your application. Needless to say that you are in for some unexpected outcomes.

I did not kill the bug directly. Instead, I rewrote the code so as to avoid text conversions. After running the new code for the first time the whole thing just behaved correctly. So should we avoid the text conversion functions altogether? Absolutely not! Just be wiser than me when you use them and make sure the converted strings are terminated. An unterminated string may cause this kind of problems in your application. So beware: you can either add one to the source string length or you can do that to the target string and make sure the buffer is zeroed before the conversion. Or you can do both.

What was the last inserted IDENTITY value?

One of the nice things you get from using OLE DB is that you can readily access the last inserted IDENTITY value without resorting to the funny @@IDENTITY. You just insert the new row and read back the inserted value - it's that simple. If you use the .NET CF 2.0 ADO .NET provider for SQL CE 3.0 you can also use a similar technique. Read all about it here.

Installing CAB files from desktop .NET

Data Port Console is now on Draft 15 and I decided to move ahead and promote it to Beta. There are still lots of features that I will add in the future (both during Beta and after the product goes "gold"), but there was one on the top of my list that had to be done right away: installing the device server from within the application. This is actually quite necessary for Windows Mobile 5 devices (both Pocket PC and Smartphone) because the installer CAB not only deploys the required DLL but also enables RAPI connectivity.

The code I wrote for the installer is quite simple and I decided to write a short article on this. You can read it here.

Who put SQL CE 3.1 in my C:\Windows\System32?

I have this one to figure out. My brand new Vista Ultimate box has a mysterious version of SQL CE 3.1 installed in C:\Windows\System32 that is referred to by the registry as:

Microsoft.SQLLITE.MOBILE.OLEDB.3.0

Say what!?!? SQLLITE???

P.S.: Yes, it looks like it is causing trouble: IAlterIndex does not work in my PC even after reinstalling SQL Compact Edition 3.1...

SQL Compact Edition Insert Performance

How fast can you insert on a SQL Compact Edition database? On March 22nd I delivered a presentation on SQL Compact Edition in the local TechDays 2007 event. The event was a great success with lots of people literally flooding the venue. Unlike Alberto Silva, I was lucky enough to present only once. Poor Alberto had to make the same presentation twice due to the unbelievably large number of people that attended his first show. I managed to get away with a peak audience of 167...

The demo that caused most impact on my presentation was a benchmark between four different approaches to bulk data insertion on a SQL Compact Edition database. As you are probably guessing, using native code and OLE DB was the fastest scenario but the SqlCeResultSet also puts out a very decent performance.

To better illustrate this (and also to release the code to the public), I published an article that explains the benchmark and shows how wrong you can be if you don't use the correct approach...

The Windows Mobile 6 SDKs are out!

Grab your copy here!

The Bluetooth sources

When I wrote my first article on the Widcomm Bluetooth stack, I promised to publish a .NET CF 2.0 library that would abstract the enumeration features of the Widcomm and Microsoft stacks under a unique API. My life became very busy since then, especially when I got a contract for integrating the new version of a mobile GPS application on the Windows Mobile environment. This has been a very interesting challenge that has put all my knowledge through its paces and made me learn a lot: powering off the device, integrating DirectDraw with screen rotations, popping up the phone when a call comes through and the screen is in DirectDraw mode, handling bizzare GPS behaviors, exotic device keys (think HTC 3300)... The works!

Requests for the Bluetooth code and article have not stopped, though so I decided to post links to the unedited versions of the C++ and C# sources. Before I get to fully comment the code and write a proper article, what you see is what you get. The sources are divided into 3 ZIP files:

Bluetooth.zip - Contains a sample C# WM5 application that enumerates devices and services. Also contains the Primeworks.Bluetooth class library.
msbts.zip - The low-level Microsoft Bluetooth stack wrapper.
wcbts.zip - The low-level Widcomm Bluetooth stack wrapper.

Now, I can take my time and write the full length article. All your comments and suggestions are welcome.

Handling the Power Button

After three months without writing, I posted today a new article named Handling the Power Off Button. This reflects my recent experiences on managing the Pocket PC resources when the power status changes.

I finally got the eToken from Verisign. From now on I will be able to digitally sign my device DLL and CAB files in order to make life so much easier for my customers. I hope to have the first CAB for Windows Mobile 5 (Pocket PC) out next week.

An unexpectedly good experience

I always hated the idea that Windows Mobile code needs to be signed to properly run on our beloved devices. And I also admit that I have resisted to signing my own code, maybe in a stubborn or lazy way. Signing your code is an expensive thing and I was fearing that it would also be a burdensome and expensive bureaucratic process, so I was delaying the decision to bite the bullet as much as I could.

Well, I have to admit that I wasted a lot of my time procrastinating... After contacting Verisign for the first time on January the 31st, I got the full approval today. Instead of a heavy bureaucratic process, I was only asked for a telephone bill copy. After emailing it last Friday, I got the approval early this morning.

This is still not an inexpensive service, but it certainly is easy to purchase!

Installing a CE MAPI filter

We all know how this is done, right? We start by installing the CE MAPI filter DLL on the device (must be signed for WM5 devices), we register it and we restart the messaging application. Hum... How do we restart the messaging application? My approach (and I have to say it is not only my approach because I see it a lot) is to soft-reset the device. Not bad for a Pocket PC 2003, but a nuisance for a WM5 device - it just takes too long to reboot. Needless to say, this approach is a nightmare for developers trying to debug a CE MAPI filter DLL: you have to restart the device twice for each deployment (registering and unregistering the DLL).

There is a better way, though: the messaging application can be closed with a simple WM_CLOSE message sent to the right window. To find that window, use the FindWindow API with the "Inbox.MainWnd" class name. Once you close the messaging application, you can register and unregister the DLL without resetting the device. Nice.

Read-only SDF files

Ever tried opening a read-only SDF file? The OLE DB provider will refuse to open it, reporting error number 25039 (SSCE_M_FILEACCESSDENIED): "Access to the database file not allowed". The workaround for this is to provide a writeable path for the temporary files directory on the connection string (or connection properties). Thanks for this piece of info, Laxmi!

Reading the GPS port on the HTC P3300

So how do you read a COM port? After a few years of practice I got the impression that using WaitCommEvent on a thread was an assured way of catching all the characters sent by the serial port. As a matter of fact I have used the same code over and over again with the same reliable results, whether I was reading a real serial port or an emulated one over a Bluetooth connection.

Recently I had to tackle the built-in GPS serial port of the HTC P3300 and got a big surprise: my code wouldn't work! In fact, no characters were being buffered by the thread code meaning that the consuming application was receiving no data... After a close examination of what was going on in the thread code, I realised that no events were being generated by the serial port, namely the EV_RXCHAR that is used to signal that the serial port has characters to deliver. Interestingly, if you use the ReadFile API with a reasonable set ot timeouts you will be able to read data.

It is baffling to se how unreliably hardware support for common stuff like serial ports can be implemented. I'm still trying to figure out why HTC implemented it this way...

Recognition

It's nice when you see your (modest) offline help publicly recognized. Thanks for this, Maurizio!

Time to update eVC4?

I'm getting a few weird 0x80000002 exceptions in my eVC4-generated code. After looking around, it looks like this *may* be a compiler issue. Microsoft has provided an update to address this. I wil try it out and post back the results.

[Edit]
Well, this was *my* error, not eVC4's... Shame on me.

RAPI Data Throttling

One of the significant changes of Windows Mobile 5 devices over the previous generation was the noticeable speedup of RAPI communications. My iPAQ 2210 just crawls when compared to the either the JasJar or the K-Jam (with a slower CPU). This means that it is much easier for me to test my remote database access code on the WM5 devices, but I still keep my pledge to support older devices like my pre-historic iPAQ 3850 with the Pocket PC 2002 OS. And this means slower RAPI connections.

The connection speed can be so slow to the point where loading a very large table from the iPAQ 2210 (on a CF card) to the new Console may take several minutes. In the case of the huge table I discussed before, this means a 15 minute load time. If you think like I do, this is unnacceptable. So how can we speed up the communication between the device and the desktop? Easy: ZIP it!

In my particular case this was quite easy. All of my remote database access products use the same device DLL that communicates with the desktop via RAPI, TCP/IP or even the serial port. Data is packaged in messages of varying sizes and, depending on their semantics, they are either dispatched immediately or grouped together in a 64 KB buffer and sent when it fills up. This seems to be the optimal size for RAPI connections (according to one slide deck from MEDC 2005). So what happens if you compress this buffer before sending it? In my case the data transfer time was cut to a third. Now the behemoth table downloads to the desktop in under 5 minutes.

Finally, the ZIP library I'm using was found in CodeProject. This code allows me to compress and uncompress to a memory buffer, making it perfect for my own purposes.

MVP again

Thank you Microsoft for the fourth MVP award!

1946

1946 is out today!

My apologies for the off-topic, but this flight sim just looks GREAT. I've been trying to fly IL2 FB only to find that no matter what plane I fly, I'm a damned turkey. If you are on to WW2 flight sims like me (addicted since the venerable SWOTL) 1946 has got to be your next toy - just take a look at the trailers (drool).

How fast can you delete?

There have been a lot of interesting discussions on data insertion performance in SQL CE. The bottom line is actually quite simple - to insert at full speed, you use a base table cursor and drop any indexes. If you must have indexes you will get a lower performance. But if you want to go really slow, you use a SQL insert command.

How about deleting? The same principles apply, actually. Use a base table cursor and (if possible) drop the indexes. But what if you have to selectively delete a large number of rows in a very large table? Well, you will need an index to seek the rows to delete. You pick up the next row to delete (given a unique row ID), seek it and delete it. Simple? No. This apparently simple process can become a real headache as I have just learned.

The scenario is simple: a very large database (around 50 MB) stored on a very fast (80x) CF card. I'm given a text file containing a very large set of GUID values that correspond to rows in a table (around 150K rows) to delete. The table has 6 indexes and a PK. How fast can we go on an iPAQ 2210 (my wartime machine) deleting al the rows? We cannot just do a DELETE FROM because this is a selective algorithm - it just happens to be deleting all of the rows (the same result would apply of you were deleting most of them).

Anywhere from 7 minutes to several hours... Really. Is this an issue with the CF card? No - this is repeatable on other devices with different flash cards. Is this an issue with the indexes? Partly - if you remove all indexes but the GUID you will get better performance, but this is a restriction of the problem: I cannot drop the indexes (after all, the algorithm does not know how many rows it is supposed to delete). Is this an issue with SQL CE? No - after all there is a scenario where you delete all the rows in 7 minutes (roughly the same as the DELETE FROM command takes).

As it turns out, this is an issue with the order with which you feed the GUID values to the algorithm. The 7 minutes run was achieved with an unsorted set of GUID values (randomly fed) where the several hours run (actually this is a projection because I stopped the bastard after 30 minutes and only 25K rows deleted) was ran when the GUID values were sorted. Yes, you read correctly - sorted.

So if you are in a similar scenario, please make sure you are not deleting the rows in the same order of the index you are seeking them on. It will kill your performance.

Why does this happen? B-tree algorithms, anyone?

Renaming a SQL CE Table From a .NET CF Application

Yes, I have addressed this issue a couple of times in the past. The difference now is that I'm providing a means for .NET CF applications to rename SQL CE table names through a native code DLL. Read about it here.

ALTER TABLE / ALTER COLUMN

So how do you change a column type in SQL CE? Going back to 2.0, there was no built-in support in the SQL language. The ALTER TABLE / ALTER COLUMN options were restricted to adding or dropping the default value, or changing the IDENTITY column properties.

If you compare the 2.0 BOL with the 3.0 BOL you see no difference. The same syntax is supported with the same limitations. Well, actually not.

With SQL CE 3.0 you can change a column data type using an ALTER TABLE command. The Syntax is very simple:

ALTER TABLE table ALTER COLUMN column type

I just got a confirmation from Microsoft that this is missing from the documentation so you should see it included in future versions.

Implications of this discovery are very important to me, especially for the SQL CE table editor in Data Port Console (now in Draft 11):

1. Editing tables for SQL CE 3.0 just got easier. Instead of recreating a table every time a user requests a change in column data types, I only need to generate an appropriate SQL command and I'm done with it.
2. Now, how do I keep backwards compatibility with SQL CE 2.0? Well, I don't. When using the SQL CE 2.0 engine the Console code will have to recreate the table (and hope the user has the missing msdadc.dll on the device - a highly unlikely scenario).

Back to the VS2005 editor...

Biting the DB_NUMERIC bullet

I tried hard to avoid it. I really did. I always looked at the DB_NUMERIC structure with a mix of shock and horror. How do you handle such a beast? How do you put there a number and how do you handle it?

Well, you avoid biting the bullet until you definitely have to do it. Right now I'm working on a native code text-to-SDF conversion tool for a customer and one of the types I need to handle is the dreaded numeric.

It's interesting that this is a very popular type due to the fact that it represents a fixed-precision number. What's more, you get to define the precision. The downside to this type is its sheer size: 19 bytes! As a matter of fact, a numeric value is represented as a DB_NUMERIC structure. If you look it up either on MSDN or on oledb.h, you will see what I'm talking about. The damned thing is a monster.

Now my problem was to deserialize a text representation of a numeric value and store it in a DB_NUMERIC structure so I could feed it to the SQL CE OLE DB provider. If you are thinking about using the IDataConvert interface then you can forget it because Microsoft does not provide the msdadc.dll file anymore, so you have to convert the beast by yourself.

Firstly I tried a two tier approach: convert the text to a double and then convert the double to a DB_NUMERIC (there's some code out there that does this). You have to be careful with rounding to get the conversion done, but it works... slowly.

My second approach was actually based on the first one, but I removed most of the floating point calculations. It is fast but limited to numbers with a precision of 18 digits. Take a look:

bool CDbNumeric::Parse(LPCTSTR pszText)
{
    __int64    big        = 0;
    LPCTSTR    pCur;
    int        nScale    = -1;
    int        i;

    for(pCur = pszText; *pCur == ' '; ++pCur)
        ;

    if(*pCur == '-')
    {
        ++pCur;
        sign = 0;
    }
    else
        sign = 1;

    for(; *pCur && nScale; ++pCur, --nScale)
    {
        if(IsNumeric(*pCur))
        {
            big = big * 10 + (*pCur - '0');
        }
        else if(*pCur == '.')
        {
            if(nScale > 0)
                return false;

            nScale = scale + 1;
        }
        else
            return false;
    }

    memset(val, 0, sizeof(val));

    for(i = 0; i < 16 && big != 0; ++i)
    {
        val[i] = (BYTE)(big & 0xFF);

        big >>= 8;
    }

    return true;
}

It's faster, believe me.

Just another database?

Is SQL Mobile / Everywhere / Compact Edition just another database? Read the DDJ interview with Mark Jewett and Steve Lasker for more insight on this:

SQL Server Everywhere: Just Another Database?

Thank you Rui!

Thank you to my fellow MVP Rui Silva for all the stuff he sends me that helps me keep my mental health.

Data Port Console Draft 6

This is a bit boring now... I will not make any more posts about the Data Port Console Draft versions unless they are worthy of notice. If you want to get the latest version of the Draft, you can use this link.

Data Port Console Draft 5

The Draft 5 of Data Port Console is now online. There are a couple of new features such as the revamped table designer (not fully functional yet), that integrates both column editing and index / primary key editing. Fellow MVPs Jan Yeh and Alberto Silva have been helping a lot with suggestions and bug reports, so here's my Thank You.

Anybody else out there with some bright ideas?

Microsoft SQL Server 2005 Compact Edition RC1

The Microsoft SQL Server 2005 Compact Edition RC1 is now available for download here.

JScript support on Windows Mobile

This has got to be one of the coolest things around - exposing your Windows Mobile application's features via JScript!

Draft 3 Notes

There is something I forgot to mention about Draft 3: you have to manually deploy the new RemSqlCe.dll file to the device. Please remember that this is far from being a finished product so you will see lots of missing features, and this is one of them.

The new RemSqlCe.dll (marked as 1.50.xxxx) now supports multiple databases per physical connection. I was looking forward to implement this new feature some time ago. It was not quite necessary for products like the Wizard where you actually process one device database per connection. With DesktopSqlCe I started to feel the need to implement this but as the sales took off I simply got too busy supporting my customers cleaning off some very stupid bugs, and this was delayed.

I finally decided to rewrite the console and implementing this feature was on the top of my to-do list. Designing the multi-database support code was quite easy: I extracted most of the code that was on a single monolithic server and implemented it in a separate class. This new server class is instantiated whenever a new database is created or an existing one is opened, and a pointer to it is stored in an internal array of 256 elements (I believe you will never need to open 256 databases on a device at the same time, will you?). When you open a database using the Draft 3 code you can see the server index in the database properties window. If you get a -1 after opening the database then you are using an older version of RemSqlCe.dll - please replace it. Note that the index value will not automatically refresh after opening the database. To do so, please select another node in the tree and select the database node again.

Please send all your comments and suggestions to this forum. The best ideas will get a free license. What do you think should be in this product?

• Integrated import / export features?
• If so, what formats should I support?
• What database editing features are the most important for you?
• Should I implement some sort of SQL Command batch processing?
• Do you want to manage other Microsoft databases from this console?
• Should I integrate the Data Port Sync features?

Let's hear it from you!

Data Port Console Draft 3

Draft 3 of the Data Port Console is now online. You can now:

• Export the table data to an Excel sheet and
• Specify database open and creation properties

As always, remember that this is not a product yet. Most of the features you will see on the menus will not work. Although my tests have shown that the data handling is now stable (the underlying native code DLLs have been revamped to support multiple connections per Pipe), please refrain to use your production SDF files with this and use copies instead.

SQL Server Compact Edition

Another name change? Well... read Steve Lasker's post to find out more.

Looks like I will have to change all my product's documentation...

Data Port Console Draft 1

The first draft version of Data Port Console is now ready to download. Before you download this, please uderstand that this is not a product yet. Your SDF files are not safe with this software: use only backed up copies so you don't lose any valuable data. Some of the features are still partially implemented or not implemented at all. Remember: this is not a finished product, not even a Beta.

So why am I posting this? I want your feedback on how the product should evolve and I am ready to give away free licenses for the best feature ideas. Use the special forum to express yourself.

Handling multiple databases

I'm preparing what will be the first Data Port Console Draft for public download. It will be an unfinished piece of code: you will see lots of holes in it and I will politely ask you not to use any production database with it. But it will be good enough for you to see what is coming up and the whole "Draft" idea is to get user input while I am developing this new product. The basics are working on SQL CE Console, so let's make this one a much better tool.

One of the biggest improvements you will see is that the code now handles multiple databases per connection (DesktopSqlCe users - this will also apply to you). As a matter of fact, and as you should expect, the whole product is based on DesktopSqlCe and I am using this as an excuse to add more features to the component.

The versions so far used to tie up the Pipe (physical connection) to the SqlCeDatabase in a one-to-one relationship. No more! Now you can have more than one SqlCeDatabase object per Pipe even on a RAPI connection. This enables some interesting scenarios indeed, and the first consumer for this one will be the new Console. Stay tuned for the incoming Draft 1.

SQL CE Console Updated (1.3.700)

This update solves an interesting issue with SQL Mobile / SQL Everywhere: altering the table schema. When you need to add or change columns to a table with all the data in, SQL CE Console makes a full copy of the source table to the target table while converting the necessary data (you may need to add msdadc.dll to your device, although this is not required on the desktop). So how are IDENTITY columns handled?

With SQL CE 2.0 there are no problems because the clever OLE DB provider allows you to overwite the generated value.

SQL Mobile, on the other hand, has a mean OLE DB engine - the only way you can influence the next IDENTITY value is by altering the column's seed through either a SQL command or a low-level OLE DB interface (my solution).

To avoid changing the IDENTITY seed for every copied row I use an optimization algorithm that keeps track of the next value and only alters the column if there is a difference between the value to write and the automatically generated one. This is actually a variation of the same algorithm found in Data Port Wizard.

For all you SQL CE Console users, here's the latest download.

Accessing SQL Mobile-specific column properties

I wrote a small article on Retrieving IDENTITY properties with the ATL OLE DB Consumer Templates. It illustrates how to create a custom restriction class to retrieve the SQL Mobile-specific column schema properties: AUTOINC_MIN, AUTOINC_MAX, AUTOINC_NEXT, AUTOINC_SEED and AUTOINC_INCREMENT.

SQL Syntax Highlighting

I really want the new Data Port Console to go a few steps ahead of the old SQL CE Console when it comes to useability. One of the nice additions to the product is SQL syntax highlighting on the query editor. This allows you to write stuff like:

SELECT * FROM Customers

Fonts and colors are applied as you write and now I want to extend the editor to support auto-completion. Instead of purchasing a costly component, I decided to adapt the code on this article:

Syntax highlighting textbox written in C#

The code has some bugs and gotchas, but it is easy to use and adapt to VS 2005.

Data Port Console

I am in the early stages of development of Data Port Console, the successor to SQL CE Console. After looking at the very long list of UI design errors I decided to use the DockPanel Suite from Weifen Luo. Most of the database editing code is being reused from the old product, but packaged in a much more compelling user interface with tabbed windows and docking panels.

Combine this with the new .NET Framework 2.0 features such as a better data grid, and I am actually getting very good results and very fast. Looks like .NET is actually delivering its promise: fast time-to-market with a solid framework to support your application.

P.S.: Yes, I know it hogs memory and the CPU with what some native code purists would call needless cycles. But I'm not sure I would be able to do this as fast as I am doing now with either MFC or WTL. Anyway, the core of the code - remote database access - is still native code. The more I think of it, the more I like this combination: native code for the performance bits and managed code for the UI and managing the user environment. A killer!

Enumerating devices and services with the Widcomm Bluetooth stack

I've just published an article on enumerating devices and services with the Widcomm Bluetooth stack. You can find it here.