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!