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...

Installing SQL Mobile Beta 2

This is not as straightforward as I thought it would be. After copying and expanding the recommended cab files for the platform (in my case I installed it on both a Pocket PC 2003 SE and on the Windows Mobile 5 Pocket PC emulator) you will not be able to run Query Analyzer. Simply put, this application like my device component are OLE DB consumers and the OLE DB provider is not automatically installed. Microsoft designed SQL Mobile in a very componentized way and the OLE DB provider is one such component. To make this work, I had to manually copy the sqlceoledb30.dll and sqlceca30.dll to the device and register them. Everything now works as expected.

Note: If you are developing against SQL Mobile, you might want to install the error messages DLL - sqlceer30EN.dll.

RemSqlCe.dll for SQL Mobile

It took me less than an hour to port all of the SQL CE 2.0 code in RemSqlCe.dll to SQL Mobile. Wow! I never expected it to be so fast. There were some minor issues I had to deal with.

First, the whole project was ported from eVC3 to eVC4. This was not a strict requirement, but since I was targeting a Pocket PC 2003 device I decided to do so. The result was interesting because I ended up dropping some dead code that still hangs around on the SQL CE 2.0 version... Now I have a smaller DLL (87 KB).

When porting to eVC4, I had to drop Giuseppe Govi's STL port and use the one that ships with the Pocket PC 2003 SDK. Apparently the old STL code will create a number of compile-time conflicts with the XML headers...

After changing a few constants and applying the small correction I mentioned on my last post, the code started to work - in less than one hour (most of this time was spent with the eVC3 to eVC4 migration).

The SQL Mobile component can be downloded from here. Please note that this will retain a Beta status while SQL Mobile is also a Beta product. To install it, just copy it to your device's \Windows directory and you are done.

Supporting SQL Mobile

The Beta 2 version, that is. So far I was able to update my code to work with SQL Mobile (running on a Pocket PC 2003 device) without a lot of work. Some of the constants will change, such as CLSID_SQLSERVERCE_2_0 which must now be CLSID_SQLSERVERCE_3_0.

Bookmarks are now unsigned integers (DBTYPE_UI4) when they were integers in SQL CE 2.0.

Another interesting difference occurs when you open a table with an index. With SQL CE 2.0, using an empty string for the index name would be interpreted as no index selection. No so with SQL Mobile - an empty string seems to be a valid index name and the table will refuse to open. Here's some code I had to change in my previously published CIndexTable class template (in bold):

if(szIndexName && wcslen(szIndexName))
{
idIndex.eKind = DBKIND_NAME;
idIndex.uName.pwszName = (LPOLESTR)T2COLE(szIndexName);
pIndex = &idIndex;
}

I am testing the whole code against SQL Mobile and will post my findings as they come up.

Back to Lisbon

The Summit is over. I have to say that this was much better than last year's Summit. Besides the excellent company I had over there (as well as the privilege of visiting the nice city that Seattle is - I just love it), the Summit's technical sessions were for the most part very interesting. Unfortunately, I cannot write about what I saw and learned due to my NDA.

Jet lag was one of my biggest problems. It was so big that when I met Alex Feinman, I thought I was talking to Andy Wigley and complimented him on his book... Foot: meet mouth! (Sorry Alex!)

Off to Seattle

Packing time again, this time I'm leaving to Seattle to attend the MVP Global Summit. I will try to make some blog entries about any non-NDA material that comes along (if I manage to handle the jet-lag, of course...)

Supporting ActiveSync 4.0

The technology behind the products I've been developing was initially based on ActiveSync and RAPI (Remote API). Now, some of them support TCP/IP connections to a device server using the same component but, for the most part, ActiveSync is still the connection method most customers choose.

My first approach to consuming RAPI was quite simple: a static link to rapi.lib. This meant that all the products were tied to a particular version of ActiveSync and would not run in another. By linking to a specific version's lib file we are actually precluding the possibility of linking to other versions, both older or newer.

It so happens that util recently all applciations were statically linked to ActiveSync 3.8, meaning that you had to install this particular version in order for the applications to run. Incidentally, I got a request from two different customers, one asking for ActiveSync 4.0 support, and the other was asking for version 3.6 support. Now what?

Publishing different versions of the applications was obviously out of the question. There had to be a better way of supporting mutiple versions of RAPI. Then I recalled something I read in one of Doug Boling's books: use dynamic linking. The idea is to dynamically load the RAPI.DLL file and retrieve the entry points to the functions you need by using GetProcAddress. To make things easier, I wrote a very simple helper class, of which I'm highlighting a few snippets.

First, the class declaration:

#pragma once

#include <rapi.h>

class CRemoteAPI
{
public:
    CRemoteAPI(void);
    ~CRemoteAPI(void);

    HRESULT InitEx(RAPIINIT* pRapiInit);
    HRESULT Uninit();
    HRESULT Invoke(LPCWSTR       pszDLL,
                   LPCWSTR       pszFUnction, 
                   DWORD         cbInput,
                   BYTE         *pInput, 
                   DWORD        *pcbOutput, 
                   BYTE        **ppOutput, 
                   IRAPIStream **ppIRAPIStream, 
                   DWORD         dwReserved);

protected:
    HMODULE m_hDll;
};

I'm only including a few methods to illustrate the technique. You should be able to extend your own version with the calls you need.

Let's look at the constructor and destructor:

CRemoteAPI::CRemoteAPI(void)
:    m_hDll(NULL)
{
    m_hDll = LoadLibrary(_T("RAPI.DLL"));
}


CRemoteAPI::~CRemoteAPI(void)
{
    if(m_hDll)
        FreeLibrary(m_hDll);
}

As you can see, we dynamically load and unload the RAPI.DLL on the constructor and destructor. You should have no problems in nesting these - you will only be incrementing or decrementing the module load count. Now, let's see how to implement one of the methods:

typedef HRESULT (__stdcall *CERAPIINITEX)(RAPIINIT*);

HRESULT CRemoteAPI::InitEx(RAPIINIT *pRapiInit)
{
    HRESULT      hr = E_NOTIMPL;
    CERAPIINITEX pfnCeRapiInitEx;

    if(!m_hDll)
        return hr;

    pfnCeRapiInitEx = 
      (CERAPIINITEX)GetProcAddress(m_hDll, 
                                   "CeRapiInitEx");
    if(pfnCeRapiInitEx)
        hr = pfnCeRapiInitEx(pRapiInit);
    return hr;
}

You now get the idea on how to implement all other methods. The beauty of this is that by using this approach, you can support all versions of ActiveSync (provided that the target version does implement the function call you need...).

Data Port products updated

All Data Port products have been updated to tackle two nagging issues: library threading model and a memory leak on the device component. The device component has been corrected to address the GetTableDefinition "leak" (see my previous post) and underwent a major review on memory allocation issues. Memory consumption is now much leaner due to some clever buffer reuse tricks.

I'm still recovering from the shock of realizing that all desktop code was being linked to the sinlge threaded versions of the runtime libraries. At least two threads are used in all products (DPW uses more) so you may now understand why some of my users were getting random memory addressing crashes...

The GetTableDefinition memory leak

It's not really a memory leak - the online documentation of ITableCreation::GetTableDefinition only talks about releasing memory pointed to by one of the parameters. The bottom line is that you need to release all the memory that the method allocates for you and returns in the pointer parameters. The problem is that it's not simple.

This explains why RemSqlCe.dll leaks memory (arrgggh!). Now, I have to roll up my sleeves and produce new releases of all my products...

New C# sample for Data Port Component

The old CsDataPort sample has been updated to allow for the selection of both desktop and device databases. On the desktop side, the user can specify either an Access database (with file browse) or a SQL Server database (without browsing...). The SQL CE database is fully specified (file name, password, encryption and the temporary file directory on the device). You can find the sample on the latest distribution zip file (see here).

P.S.: The new version is now 1.0.30.

Data Port Component updated to 1.1.29

I have updated Data Port Component to version 1.1.29. There is a new VB .NET sample, the VB6 sample has been corrected and updated and the engine now supports pre-port and post-port custom SQL command execution.

Update CCommand, please!

One of the problems I find with the ATL OLE DB Consumer Templates is that you have to know what you are going to get before you ask for it. Wait - this is normal, right? Looks normal but when you are handling generic SQL commands, you only know what you are going to get after asking for it.

There are two different kinds of SQL commands: the ones that return something and the ones that return nothing. The first class of commands are the classical SELECT commands - they always return something, even if it's only a single row count. On the other hand INSERT, UPDATE and DELETE commands (as well as all the DDL commands) do not return rows for you to process.

Making this distinction is very important when you use CCommand: what accessor and rowset types will you use? All non-SELECT commands typically use a CDynamicAccessor and a CRowset where all others may get away with CNoAccessor and CNoRowset. This means that you need to know what kind of SQL command to execute before instantiating the proper template parameters in CCommand. This is a nuisance when running generic SQL commands. Why?

Let's look at the code in CCommand::Open. Somewhere near the bottom of the method, you can see (ATL 3):

if( bBind & 
   _OutputColumnsClass::HasOutputColumns())
    return Bind();
else
    return hr;

This is bad. By default, bBind is true (default function parameter) but the HasOutputColumns function is defined by the accessor class you provide as a template parameter for CCommand. Gotcha - you need to now if the command returns rows before instantiating CCommand. So how do you go around this? How do you run generic SQL commands without having errors thrown at you, no matter what accessor you use?

Easy. Just change the last code snippet so it reads:

if (bBind && GetInterface() != NULL)
    return Bind();
else
    return hr;

Now all commands will run without having assertions thrown at your face. To see if you actually have an accessor and a rowset, just test GetInterface(). If it's NULL then your command returned nothing.

Why? Because I Can!

The stunt both me and César pulled off on August 30th caused some interest in the community. Hits on my site peaked as I never thought would be possible.

One guy, though, kept it cool. In his note on Pocket PC Thoughts, Ed Hansberry said:

I am sure someone will fine a practical application for this, but for now, it falls under the W?BIC category.

Actually we started this stunt with a challenge from César. Why don't we try and connect?

Ed has a good point. The practical implications of this are still a bit limited by the technology. On one hand, the client must know the IP address of the server PDA. This will change very often due to the connection methods available. Not everybody will be able to have a fixed IP address to use on the PDA. So one may wonder if the a half-a-world-away scenario is feasible, but on a WiFi scenario this may actually happen.

On the other hand, while the server is actually busy serving data, the PDA user cannot edit his data. This is obviously a limitation of the current SQL CE 2.0 engine that will be lifted with SQL Mobile. When it becomes mainstream, one may see real-world applications of this technology, where the backoffice accesses the mobile database to update it while the user continues to work.

So that's right, Ed: Why did we do it? Because we could, and was a lot of fun! ;)

Extreme database editing

My friend César and I just participated in what I would call "extreme database editing". After discussing my new site and the latest improvements to DesktopSqlCe, César challenged me to try and edit a SQL CE database on his PDA. We would use the latest version of my tools and would do it over the network. The challenge was that he lives in Peru and I live in Portugal.

I don't know how, but he did manage to get his PDA connected to the Internet with a fixed IP address, which is all that my Pocket PC server software needs to start serving a remote client. On my end, I used SQL CE Console to edit his data. We were using ADSL on both ends, which means that our real bandwidth was limited by the upload bandwith which is typically 128K.

It took us some time to get the connection settings right (especially with César because the server PDA was on his end), but we finally managed to make it work. I could successfully connect to César's PDA, open the Northwind database, insert some new records, and disconnect without a hitch. No data were lost.

Cool stuff!

New Site

I was totally fed up with my company's old site. Having navigation frames on a website is not a very good idea, especially when you want to provide links to some specific pages. When I did that, readers would lose the navigation buttons which is pretty bad.

A simpler site was in order and, not being an HTML geek, I turned to FogCreek's CityDesk to help me in publishing content on the site. Interestingly, I'm using it exclusively to manage the entire site. A final note: it took me one day to rewrite the whole site. Not bad, huh?

Check the new lean and mean Primeworks website.

IColumnsRowset

DesktopSqlCe seems on time to be launched in September. After clearing a lot of hurdles, such as command parameters and direct table seeks, I started to implement the ADO .NET data provider on top of the lower-level classes. A data provider is not a small body of code to write, but you can find help in lots of places, especially in MSDN where templates for all the major classes are provided with detailed explanations. Quite conveniently, the MSDN writers skip some of the trickiest implementations and you can get a bit lost.

Such was the case when I started to implement SqlCeDataReader.GetSchemaTable. This one was a bitch to do. One of the best clues I actually got from the MSDN documentation:

The GetSchemaTable method maps to the OLE DB IColumnsRowset::GetColumnsRowset method (...)

It's not enough, but it's a big step forward. After implementing this using the Consumer Templates, I deceided to write a small article on this subject:
Using IColumnsRowset with the ATL OLE DB Consumer Templates

Enjoy!

BLOBs and SQL CE Command Parameters

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable-length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

You might already have read this on the MSDN help page for SqlCeCommand.Prepare. Have you ever wondered why you need to set the size of variable-length data types before preparing the command? The answer is easy: you cannot use a BLOB in a parameter. I have a strong belief that this is not necessarily so but, at most, you could use one BLOB type as a parameter.

This happens due to a limitation of SQL CE 2.0 and of OLE DB. As you may know, SQL CE 2.0 can only handle one storage object at a time. Storage objects such as ISequentialStream are used to copy BLOB data between the provider and the consumer. One of the workarounds for this is to use more than one accessor handle per row. I described this in two articles:
Managing Blobs Using the ATL OLE DB Consumer Templates
and
Inserting non-nullable BLOB data in SQL CE using the OLE DB Consumer Templates
This solution breaks down with the current OLE DB implementation. As it stands, ICommand::Execute only takes one accessor handle for all parameters in the DBPARAMS structure. So this means no BLOB command parameters.

The workaround Microsoft devised is actually the only one possible - preset the parameter size before preparing the command.

Data Port Command Royalty-Free

After insistent requests from customers, I finally released a royalty-free version of Data Port Command. This version targets those users that want to consume DPC in their applications while retaining the ability to distribute it to their own customers or end users. It has the same price as Data Port Component, which seemed to be a logical choice.

Corrected CRowsetIndex

While I'm preparing the release of DesktopSqlCe, I've been rooting out some very old bugs from the code. One of the bugs that was pending a resolution was pointed out to me by an anonymous CodeProject reader. The corrected CRowsetIndex code has now been published on Pocket PC Developer Network. The code is now working and supporting the SqlCeRowset.Seek method (similar to the on in DataReader).

The Garden of Arcane Delights

No, this is not a post about the exquisite music of Dead Can Dance. It's about getting trapped by some arcane "delights" of technology. It's about SQL CE and how to insert non-nullable BLOB data using my (apparently) tested code.

It fails. Yes, it fails. Works wonderfully when one assumes that BLOB columns are meant to be nullable (i.e., to accept null values). And then suddenly here comes a customer who thinks otherwise. He likes his NTEXT columns NOT NULL. Gosh: the whole thing crumbled.

If you are using my ATL OLE DB C++ code, heed these words of advice, so you can merrily trod in the garden of arcane delights.

By the way, DCD rocks (or rocked)...

Product Updates

I'm almost through publishing the updates for the Data Port products. One of the most elusive bugs was finally rooted out - an intermittent and rare failure when exporting data via RAPI streams. The IRAPIStream::Read does not guarantee that it will read all the requested bytes in a single call, so it must be called enough times to fill the target buffer. Interestingly, this will only happen very rarely but when it does, the export procedure fails. This update pertains to both the PC and CE code.

Another subtle bug has also been corrected: importing empty NTEXT fields into SQL Server. Apparently the SQL Server provider does not like to be given an empty stream object - you must really pass a NULL pointer as the parameter value.

dplib.dll updated

Some unexpected errors in Data Port Component's dplib.dll were found and I've updated it (download). The "Select Append" feature now works correctly. I tried to finally make the DEFAULT clause work for both Access and SQL Server but didn't make it yet...

RemSqlCe.dll updated

I have just updated the RemSqlCe.dll file that is shipped with all my products. When connecting via RAPI (ActiveSync) the pipe data reading code was prone to skip some bytes, thus invalidating some of the exporting procedures. Interestingly, this is a very rare situation. Nevertheless, I updated the RemSqlCe.dll file (goes into the device) and posted it here.

Back from holidays

There is a lot of work to catch up with. First of all, I will try to get DesktopSqlCe to production standards. The first thing to sort out is the seek mechanism. The next is to implement SQL command parameters. This time I will also release the full C# code for the assembly.

Why WTL?

When researching for a small article that lists WTL resources for Pocket PC native code develores, I found a very interesting piece of information that compares the performance of MFC and ATL (WTL builds on the ATL windowing classes): Oleg Pudeyev's MFC vs ATL. Now, the message processing performance issue is clearly explained. MFC degrades with the number of managed window handles due to its linear search method when mapping an HWND to a function pointer. With ATL's thunking mechanism this is instantaneous.

WTL rocks!

Another bug in SQL CE Console (cont.)

The comments I have just made below refer to the DesktopSqlCe assembly that supports SQL CE Console, not to the .NET classes. Thanks to Alberto Silva for pointing this out!

Another bug in SQL CE Console

I'm still banging my head on the wall. Real hard.

SQL commands (SqlCeCmd objects) may be executed either as queries (when they return a SqlCeRowset object) or as simple commands with no data returned (only status info). Interestingly, version 1.0.17 is incorrectly reporting an out of memory condition after a non-query SQL command is executed. Brilliant. So how useful is a tool like SCC when you cannot even create a table???

The version 1.0.18 is out now, with my apologies.

Diving into WTL

I finally decided to adopt WTL as my framework of choice to develop native applications. After a few years of working with MFC I got fed up with it. My first tests showed me that WTL is faster than MFC handling the GUI (the thunking mechanism makes it very fast to dispatch messages from the window procedure to the class). Also, WTL does not carry with it all the cargo I don't use in MFC, such as views and documents (they always seemed to be getting in the way, and not really helping).

Too bad we don't get all the code-generation support that eVC has for MFC, but I'm getting over it pretty fast.

MEDC UK - The aftermath

I'm a bit disappointed. Next year I'll try to attend the US event. Enough said.

On another note: London rocks!

Off to London

In a couple of hours I will leave to London to attend the MEDC UK. Cool!

Why I still use eVC3

Besides being a very stable development environment, it is still the only game in town for addressing all Pocket PC platforms. Don't just take my word for it, read what the Windows Mobile Team Blog has to say about this.

eVC3 rocks!

SQL CE Console 1.0.17 Released

I fixed some vexing bugs on the application and tried for the first time to obfuscate it. It is an interesting concept (and old, very old - remember the "C Shroud" product?) but needs some tweaking. After obfuscating your assemblies, make sure you test them thoroughly. Failing to do so may lead to some very nasty surprises. Anyway, I'm actually thinking about moving more code down to the C++ DLL and actually publishing the wrapper C# code, so obfuscation will be less of a problem.

Pocket Access - V

I got it wrong, let me tell you. After finishing the CEDB .NET code, I started to write the Pocket Access layer for .NET CF and realized how some of my early assumptions were simply wrong.

The CEDB code implements a record containing a collection of properties. Each property maps to a CEPROPVAL structure directly, meaning that the contained value is accessed through bit conversion methods. This is pretty stupid. My early reasoning was that this would make it so much easier to read and write the binary info to and from the database.

When I tried to fill a DataTable object using this approach, I was surprised with the poor speed. At first I blamed the DataGrid but realised that, although it is a slow loader, the bulk of the time was being spent loading the DataTable. I turned my attention to the DataTable and applied all the tricks of the trade to make it load faster. Nah... no way.

But wait! Could it be...? Was it in my code to load the records? Like Michael Abrash used to say: "Assume nothing: Test everything".

Bingo! As any Scorpio worth his salt, I rewrote the whole thing. Upon loading, the record now converts all its properties to boxed values or string references (blobs are just byte arrays, but references as well). These are all packed into an object array which makes it a breeze to load into a DataRow. Buffers are reused to avoid heap fragmentation and the undesirable visit of our friend the garbage collector.

The Suppliers table of the converted Northwind database now gets loaded into a brand-new DataTable object in under 200 ticks on my iPAQ 3850 (I cannot get enough of this machine...), down from almost 600 ticks using the old code. Impressive, huh?

Optimization rocks!

SqlCeSpy .NET

I'm starting to publish the full source code for SqlCeSpy, a SQL CE 2.0 database viewer / editor based on the FastSqlCe assembly and the fssce.dll DLL. All managed code is being published for review but not the C++ code. The project file can be found here (Zip 352KB). By opening and using this code, you acknowledge that this code was written by me and that I am the only one to blame for any errors that you may find there (hehehe).

Before you deploy the project to the device, make sure that the fssce.dll file is copied to either the target project folder or to the \Windows folder.

Functionality is now limited to browsing the database structure and to renaming tables, something that seems to be deprecated on SQL Mobile. By the way - what does "deprecated" mean? I'm feeling linguistically challenged here: does it mean that the feature is not supported or that it is supported but should not be used? Comments are welcome.

Debugging over WiFi in VS2005

After all, it seems like we will be able to debug Windows CE 5.0 applications over WiFi in VS 2005. This workaround takes ActiveSync 4.0 out of the way and enables true WiFi debugging. Great news!

Installing a MAPI filter

I recently came across the challenge of writing an application that processed SMS messages. The MAPIRULE sample of the Pocket PC 2003 was duly studied, successfully modified and used. The real challenge came when I needed to cleanly install it and uninstall it. Installing is simple: you just copy the DLL to the \Windows folder, register it (the setup can do this for you) and then you have to soft-reset the device (a setup DLL is required for this). This is quite simple and a sample of soft-reset code can be found here.

Uninstalling calls for a different approach. First, you have to make sure that the DLL is not being used by the SMS-handling process. My answer to this is to shut down the tmail.exe process. Here is some code:

BOOL KillProcess(LPCTSTR pszProcName)
{
  HANDLE hSnapshot;
  BOOL   bRet  = FALSE;
  
  hSnapshot = CreateToolhelp32Snapshot(
               TH32CS_SNAPPROCESS, 0);
  if((int)hSnapshot != -1)
  {
    BOOL           bOk;
    PROCESSENTRY32 pe;
    DWORD          dwID = 0;
    HANDLE         hProcess;

    pe.dwSize = sizeof(PROCESSENTRY32);

    for(bOk = Process32First(hSnapshot, &pe); 
        bOk; 
        bOk = Process32Next(hSnapshot, &pe))
    {
      if(!wcsicmp(pe.szExeFile, pszProcName))
        dwID = pe.th32ProcessID;
    }
    CloseToolhelp32Snapshot(hSnapshot);

    if(dwID != 0)
    {
      // Kill the process
      hProcess = OpenProcess(0, FALSE, dwID);
      if(hProcess != INVALID_HANDLE_VALUE)
      {
        bRet = TerminateProcess(hProcess, 0);
        CloseHandle(hProcess);
      }
    }
  }
  return bRet;
}

After killing the process, you have to unregister the DLL (sample code here) and finally soft-reset the device.

A sample setup dll project is available on demand.

The New Emulator

The Microsoft Device Emulator 1.0 Community Preview is now available for download (just follow the link). Yes, this is the much expected ARM native emulator. You can actually install retail products on the emulator, run them and even better, develop against it using eVC4 SP4. How cool can this get?

Native is Faster

I couldn't help linking this in:
A .NET Loss

What is strange is presumably they had tried out .NET 2 which would seem to have a more compelling story than .NET 1, but found that lacking as well.

I wonder why...

Data Port ActiveX

I finally managed to write an ActiveX component for the Data Port Component. Instead of using a rocket-science C++ approach, I turned to the venerable Visual Basic 6 to do the job for me. The result of this work is now available online and is made out of two parts. First, you need to download the newest version of dplib.dll (Zip 1.45 MB). You get both the release and debug versions as well as the debug symbols and the map file. Second, you need to download the Vb6DataPort project (Zip 293 KB) that includes both the ActiveX project (DataPortX) and the consuming application with the same name as the Zip file.

This project shows how the Data Port Component must be used from VB6 and uses new code (the dplib.dll is now on version 1.1.25) that is not yet compatible with the published C# wrapper. The C# wrapper will be updated to comply with the changes and will also be published with the source code (it's a very thin wrapper, really).

To make things work reliably, you should put the dplib.dll in the search path or you risk getting a run-time error from VB...

Windows Mobile Platform Migration FAQ for Developers

The new version of the Windows Mobile Platform Migration FAQ for Developers is now online (just follow the link).

Check out the "Recent Updates" list next to the May 2005 entry: they're about Windows Mobile 5.

CeDbgView32

Here is an interesting tool from Laurent Docquir to help us in debugging Windows CE processes: CeDbgView32. Cool stuff!

Robert Burdick's Blog

I've just added Robert Burdick's Blog to my list of links. His latest post teaches you how to programmatically turn on or off the Pocket PC WLAN adapter in a device-independent fashion. Great stuff!

News for us, Native Fellas

Nishan Jebanasam just posted an article on MSDN on What's New in Visual Studio 2005 for Native Developers. Although some image links are still broken as I write this, it is definitely an interesting read.

There is also another one, also by Nisham, on Migrating Microsoft eMbedded Visual C++ Projects to Visual Studio 2005 where he discusses the Upgrade Wizard and other issues.

Data Port and VB6

I finally managed to make VB6 work with the Data Port Component (or the other way around). Why VB6? Although Microsoft killed it, a lot of people out there is still using it and will continue to do so in the foreseeable future. Interestingly, I recieved a lot of requests to develop a Component wrapper in VB6 from the UK. Being an almost complete ignorant, I shied away from this until one of my customers actually started the project.

At first it seemed quite straightforward. First, I had to change all __cdecl exported functions to __stdcall. The need for the second change was very hard to detect. The supporting DLL was designed to either be P/Invoked from .NET or to be directly consumed by a C++ application. This meant shielding all database-related functions with COM initialization code. This also meant exit exceptions when the DLL was consumed by VB6, either on the IDE or on the standalone runtime. After discovering this, there was the threading issue. Apparently one cannot safely call back into VB6 from an external thread, something that is safe in C++ and even on the .NET framework (that is how the C# sample works). The final solution is to start a timer to poll the import or export progress data. A kludge, but it works.

The nice thing about the whole process is that I had to instrument the whole code just to make sure there were no memory leaks or invalid pointers. A typical case of spring cleaning...

MEDC UK

I finally made up my mind and registered for the MEDC UK event. I'm starting off with the "What's New for Native Code Developers in Windows Mobile" session. Now, I wonder why did I chose this one? I also chose the SQL Mobile session to finish the morning. In the afternoon, I will attend the "New Managed Messaging, State, and Notification APIs in Windows Mobile" session and the "Developing High Performance Applications with the .NET Compact Framework". Let's see how much P/Invoke we still have to do to get these things running... Native is faster, let me tell you!

Vibrating a Pocket PC

Vibrating a Pocket PC is a simple matter of turning a led on or off. As a matter of fact, the vibrating device when present is operated through the led API. The only problem you will have to sort out is the corresponding led number as this is device-dependent. On the HTC Pocket PC Phone Edition devices, this is led number 1.

To use this functionality in your applications, use the following code:

extern "C" 
{ 
    BOOL NLedGetDeviceInfo(INT nID, PVOID pOutput); 
    BOOL NLedSetDevice(INT nID, PVOID pOutput); 
}; 

void SetLedStatus(int wLed, int wStatus)
{
    NLED_SETTINGS_INFO nsi;
       
    nsi.LedNum = (INT) wLed;
    nsi.OffOnBlink = (INT) wStatus;

    NLedSetDevice(NLED_SETTINGS_INFO_ID, &nsi);
}

Simple, right?

First Customers

After nine months selling software on the internet, only today did I manage to sell a product to a Portuguese company. Hum...

Now you know why I write in English.

New Projects

While I have a lot of code that needs further development, I cannot seem to decide what is going to be the next project on the list. There is the FastSqlCe code that needs to be further developed (schema is done, now data is waiting). Before that, I have DesktopSqlCe that is still waiting for command parameters and base table seeks. Now I'm thinking about adding a data synchronization feature to Data Port Wizard and also to produce an RDA-like feature for Jet databases. Hum...

Meanwhile, I published a small QA on Pocket PC Developer Network based on a year-old piece of code that shows you how to turn on the WiFi radio on a Dell Axim X30. There was some detective work involved in writing this one, especially using Remote Spy++. Fun stuff!

Pocket Access - IV

Finishing off where I left the previous post, the MSysProcs has only two columns:

• ProcName - Procedure name


• SQLText - SQL command

This table is generally empty (I've never seen an instance where it wasn't).

Now, let's look at column types. We cannot confuse CEDB column types with Pocket Access column types. CEDB types are limited to (C/C++):

#define CEVT_I2       2   // short
#define CEVT_UI2      18  // unsigned short
#define CEVT_I4       3   // int
#define CEVT_UI4      19  // unsigned int
#define CEVT_FILETIME 64  // FILETIME
#define CEVT_LPWSTR   31  // LPWSTR
#define CEVT_BLOB     65  // BYTE*
#define CEVT_BOOL     11  // BOOL
#define CEVT_R8       5   // double

I defined these in C# for my previous article as:

public enum CeDbType : ushort
{
    Int16    =  2,
    UInt16   = 18,
    Int32    =  3,
    UInt32   = 19,
    FileTime = 64,
    String   = 31,
    Blob     = 65,
    Bool     = 11,
    Double   =  5
}

Pocket Access types resemble SQL types and are more varied. From the online documentation found on the Windows CE SDKs, we can derive the following C# enum:

public enum SqlDataType : short
{
    Unknown       =  0,
    Char          =  1,
    Numeric       =  2,
    Decimal       =  3,
    Integer       =  4,
    Smallint      =  5,
    Float         =  6,
    Real          =  7,
    Double        =  8,
    DateTime      =  9,
    Time          = 10,
    TimeStamp     = 11,
    VarChar       = 12,
    LongVarChar   = -1,
    Binary        = -2,
    VarBinary     = -3,
    LongVarBinary = -4,
    BigInt        = -5,
    TinyInt       = -6,
    Bit           = -7
}

Pocket Access - III

Pocket Access files are essentially mounted CEDB volumes with a very specific structure. They all contain four system tables:

• MSysFields - Contains all the columns of all tables in the database, including the columns of the system tables.


• MSysTables - Contains all the tables in the database, including the system tables themselves.


• MSysIndexes - User indexes are all declared here.


• MSysProcs - Stored procedures?

An empty Pocket Access database has all four tables created and the first two have some information: the system metadata. System tables are all created as type 3 (see below) and none contains a sort order with the exception of MSysFileds which has a sort order (type 0 - undocumented) on column zero.

To create a Pocket Access database, we must create all system tables an fill them with their default values. This process must be done in a very specific order: 1 - Mount a new CEDB volume; 2 - Create the databases and store their OIDs (the preferred order of creation is MSysFields, MSysTables, MSysIndexes, MSysProcs); 3 - Fill the MSysFields database; 4 - Fill the MSysTables database.

Each record on the MSysFields database has 5 columns (names taken from MSysFields itself):

• TableID - (int) Table OID


• FieldName - (string) Field name


• FieldID - (int) Field property id


• Len - (short) Field length


• ODBCType - (short) Data type

The MSysTables table contains only 3 columns:

• TableName - (string) Table name


• TableID - (int) Table OID


• TableFlags - (int) Table flags (system tables = 3, user = 0)

MSysIndexes has the following columns:

• TableID - (int) Table OID


• IndexName - (string) Index name


• FieldID - (int) Field property id


• IndexFlags - (int) CEDB sort order flags

CEDB .NET

Finnaly, I got to finish the article. I always look back and think I should have written it in a different way, but I'll get over it... Now I have to start writing about the Pocket Access stuff.

CEDB .NET

CEDB .NET under construction

This one is taking me longer to finish than I had expected. For an early look and your early comments, check it here: CEDB .NET

Pocket Access - II

While researching for my managed CEDB wrapper article and code I found two very interesting posts on Sue Loh's blog:
Common CE database questions
More CE database Q&A
My own experience, from C++ of course, tells me that is is possible not only to access but even to create a Pocket Access database without using ADOCE at all. As a matter of fact, as as Sue Loh explains, Pocket Access cdb databases are CEDB databases with a special format. They have metadata tables with a predefined format and support more types than CEDB does natively. Once you know this format and how to manipulate it there is no difference from approaching the database from ADOCE or from CEDB, with the exception of SQL. The SQL processor exists only on the ADOCE stack, so accessing these databases from the CEDB API means you don't have SQL to play with...

Square Games

So you want to write the next blockbuster game for the HP Communicator? Cool, here's how to handle GAPI and a square screen.

Pocket Access - I

Here is an article of fellow MVP Christian Forsberg (he speaks portuguese, did you know?) on migrating applications from Pocket Access to SQL CE. This is an interesting thing because I'm preparing an article on almost the reverse subject: how to access CEDB and Pocket Access databases from .NET CF without using the ADOCE COM interfaces. It's an interesting trip into native code and running around the CF marshaller. I've been learning a lot...

Visual Studio 2005 Beta 2

The download is available here: Visual Studio 2005 Beta 2

So, what is a tinyint?

A tinyint is a byte. For SQL CE 2.0, this means a DBTYPE_UI1, not a DBTYPE_I1. Where was my head when I decided otherwise? Duh...

Ok, I fixed the bug in SQL CE Console...

SQL CE Console 1.0.14 Released

The new minor version release of SQL CE Console is now out. I corrected a few bugs and added the ability of copying the contents of the query data grid to the clipboard. The new setup can be found here. New feature suggestions and bug reports are welcome.

FastSqlCe

I finally decided to go ahead with FastSqlCe, an alternative data access layer for SQL CE 2.0. The first go at it was when I decided to compare table insertion performance between a prepared SQL statement and a raw table insert. Comparing a native C++ implementation with a managed one is just not fair, so I decided to write a very simple layer and P/Invoke it. The results were stunning: a raw table insert can be up to five times faster than a prepared SQL INSERT statement. Five times. Of course, the INSERT statement had parameters.

Then I tried filling in a DataTable. The early results were also stunning: managed code was faster. Can't be, can it? After revising and tweaking the code I noticed that the less you P/Invoke, the better performance you get. After a full morning of tweaks I got the better of it and now I can (grimly) smile after squeezing a 15% improvement. Hey, native is faster.

But then I noticed how some SQL CE features are missing altogether, like schema management. How do you rename a table? How do you rename a column? How do you check if a column is marked as IDENTITY? Well, there may be some room for another data access layer after all. Enter FastSqlCe. Enter? Hum. It's not ready yet, sorry. But you can take an early look at it here: FastSqlCe online documentation. Comments and suggestions are welcome. I will post an early sample as soon as it is ready.

Me Blog

I finally had to do it: create my own blog! I will talk mostly about what I do: write software for mobile devices. I've been doing this since 2002 and I am totally addicted. So beware...