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.