Wednesday, April 11, 2007

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.

Monday, April 09, 2007

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

Saturday, March 24, 2007

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

Sunday, February 25, 2007

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.

Sunday, February 18, 2007

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.

Monday, February 05, 2007

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!

Tuesday, January 30, 2007

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.

Wednesday, January 24, 2007

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!

Friday, January 19, 2007

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

Friday, January 12, 2007

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.

Friday, January 05, 2007

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.

Tuesday, January 02, 2007

MVP again

Thank you Microsoft for the fourth MVP award!

Friday, December 15, 2006

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

Thursday, December 14, 2006

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?

Thursday, November 30, 2006

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.

Wednesday, November 29, 2006

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

Thursday, November 23, 2006

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.

Tuesday, November 14, 2006

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?

Thursday, November 09, 2006

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.

Tuesday, November 07, 2006

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?

Monday, November 06, 2006

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!

Thursday, November 02, 2006

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!

Wednesday, November 01, 2006

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.

Tuesday, October 31, 2006

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

Tuesday, October 24, 2006

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.

Wednesday, October 18, 2006

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.

Monday, October 16, 2006

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.

Thursday, October 12, 2006

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.

Wednesday, September 27, 2006

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.

Saturday, September 23, 2006

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!

Wednesday, September 13, 2006

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.

Data Port Wizard crack

Today I noticed some interesting entries in my site's log coming from Google: people searching for a crack for Data Port Wizard. This is quite flattering indeed for a product in its second year. But I believe this is also the side effect of the lack of tool support for SQL Everywhere.

Let's see if I can find a crack for this product myself (hehehe).

Tuesday, September 12, 2006

Unified Bluetooth stack access for Windows Mobile

After considering the apparently big differences between the Microsoft and the Widcomm Bluetooth stacks, I found out that there is a way to make them work under a similar API. This is especially useful for Compact Framework developers although some native developers may also benefit from a simple C type API.

The Widcomm API is oriented to C++ developers and exposes itself as a set of C++ classes that are supposed to be consumed either as-is or derived in your application. Exposing this type of API to a .NET CF developer is nothing short of impossibe unless a C layer is developed to consume the Widcomm stack while exposing a set of simple functions.

While I was writing this piece of code, I found out that the Microsoft stack could also be subject to the same treatment. So my idea was to produce two DLL files with exactly the same function exports, one for the Widcomm stack and the other for the Microsoft stack. The obvious advantage of this is that now a .NET CF application can consume the existing Bluetooth stack without having to consider the differences between the Microsoft and the Widcomm stack.

I have to say that this was a very interesting learning project, thanks to Peter Foot's help. The resulting code will be published under a series of articles and, time permitting, I will try to integrate the code in Peter's own 32feet.NET project.

Thursday, August 31, 2006

MobileServer preview


If you have been reading these posts lately you know that I am working on getting a peer to peer scenario to work with my remote data access tools. This will allow two PDAs to synchronize data just by getting close to one another within the 32 feet required by Bluetooth.

Right now this is working over Wi-Fi and Bluetooth serial COM ports (no support for sockets in the Widcomm stack, alas).

The thing that has been missing from the picture is the server code. I started by providing a standalone native application to serve a TCP / IP sockets connection. Later I split the server feature and put it in a separate DLL. The latest change was to move all that code to the device server DLL (RemSqlCe.dll) and to expose an API for the sockets server. After adding the serial port server I started writing a very simple managed code class library for users to integrate in their own applications.

This class library allows the consuming application to create a server, configure, start, stop, query its status and dispose of it. The above picture shows an instance of the sample MobileServer application running two servers on an HP iPAQ 2210 serving two different desktop PCs, one via Wi-Fi and the other via a Bluetooth COM port.

Now I have to add Bluetooth server abilities to this little beast and this means supporting both the Microsoft stack and the popular Widcomm stack (that's why I'm using the 2210). The server will be able to expose its services (either COM or sockets) via enumeration in order to make ad-hoc device pairing easier.

I expect to release the device client very soon with some P2P samples ready to run.

Monday, August 21, 2006

SQL Mobile over Bluetooth

Well, not yet what you might expect but I just finished the first version of a serial communications Pipe. This allows you to connect to a Bluetooth-enabled device through a virtual COM port as I just did with my laptop (with Targus USB Bluetooth dongle and the standard Microsoft stack) and my iPAQ 2210 (a very reliable beast with the Broadcom stack).

Nothing automatic here - I just configured a serial link between the devices and wrote the serial code to glue them both. I am ashamed to say that this was not a swift experience because my years as a mobile developer led me to overlook some of the finer points of desktop development, namely overlapped IO. Yes, porting the device code to the desktop did not quite work as I expected. Nothing at all, really. After researching into the overlapped IO mechanism, it was quite easy to set up the internal threading and queueing to simulate a RAPI Pipe over a serial port.

Next steps? 1 - Port the client code to the mobile device so you can access another devices SQL CE / Mobile databases over a Bluetooth COM port. 2 - Add support for the Microsoft stack so a device will be able to identify which partners can serve SQL Mobile data and connect via sockets. 3 - Add support for the Broadcom stack. From what I have seen, there is no sockets support in the Broadcom stack. 4 - Why not infrared?

Friday, August 18, 2006

SQL Mobile Peer to Peer

My first P2P experience with SQL Mobile has just happened: using my iPAQ 2210 with a SanDisk SD Wi-Fi card was able to connect via a wireless network to my i-mate K-Jam, open a local SQL Mobile database and enumerate all tables.

This is especially cool because I am not using revolutionary new technology - I just adapted what I had been using in my products a long time ago. Let's see a little history.

When I first shipped Data Port Wizard almost two years ago, the device component DLL (RemSqlCe.dll) was only a RAPI streamed server. Soon I understood that I could adapt the same stream code and build more transports on top of it and the TCP/IP single-threaded server came about in almost no time. In its first incarnation the server resided in a separate device DLL file, but it was not a very pratical solution. The next release of DesktopSqlCe will have a new version of RemSqlCe.dll with the socket server code included as well as a CF (1.0 and 2.0) sample that shows how to use it. And this is what I used on the K-Jam for the server.

The client uses an adapted version of the desktop's dssce.dll and Primeworks.DesktopSqlCe.dll. (In a future version, these DLL files will change their names to better reflect what they do.) This is how I was able to quickly produce pwdclice.dll (dssce.dll device version) and the new Primeworks.Data.Client.dll (Primeworks.DesktopSqlCe.dll device version). I ported the code for both DLL files in about one day (cool, huh?).

Now, besides being able to open a local device connection I can also open a TCP/IP client connection to a server device. Et voilá: P2P! Shipping in September, but I'm open to send the early builds to anyone willing to test the code.

Three final thoughts:

Why should you want to have a local connection on your device? After all, that is why you make a reference to System.Data.SqlServerCe in your application, right? Well, can you connect to either SQL CE 2.0 and SQL Mobile with the same data provider using the same application code? And can you remotely connect to another device just by changing connection properties? Now you can!

And why not add socket server code to the LocSqlCe.dll, the RemSqlCe.dll desktop counterpart? This would be a very easy way to set up a client server scenario over the internet...

On the issue of pluggable data streams, why not a virtual COM port over Bluetooth as well? Can I sync my sdf with yours via Bluetooth?

Stay tuned!

Thursday, August 10, 2006

How do I know the database version of an sdf file?

Have you ever wondered how to detect the SQL engine version of an SDF file? Here is an answer.

Monday, August 07, 2006

DesktopSqlCe redistributables updated

The redistributable files of DesktopSqlCe (LocSqlCe.dll and RemSqlCe.dll) have been updated to version 1.46.1620. This code corrects a SQL command parameter length verification bug.

Monday, July 03, 2006

SQL CE Console Updated

SQL CE Console was just updated to version 1.2.605. Besides supporting SQL Everywhere on the desktop, I corrected a few minor bugs and added timer and row count reporting to both table dumps and SQL command results. Get the updated setup here.

Thursday, June 15, 2006

Supporting SQL Everywhere

I have just updated SQL CE Console so that it supports the Microsoft SQL Server 2005 Everywhere Edition CTP. The news bit can be read in this page, where you will also find the links to the downloads.

Tuesday, June 13, 2006

Adapt your application to exotic screen resolutions

Microsoft has just launched the Adapt Your App site to help all the Mobile developer community to better adapt to the new exotic device screen resolutions. Here you will find all the resources and downloads to help you adapt you app to, say, landscape Smartphone.

Friday, June 09, 2006

SQL Everywhere CTP

In the same day I returned from MEDC Europe, I'm greeted with some great news: Microsoft released the SQL Server 2005 Everywhere Edition CTP! Thanks to Alex Yakhnin for the heads up.

Thursday, June 01, 2006

Migrating Sync to SQL Server

The first phase of the Data Port Sync migration to SQL Server (2000, MSDE, 2005 and Express) is over with the port of the Jet preparation code. It is quite interesting to see how Microsoft implements the "same" features in all of these databases. With Jet, one can do everything with OLE DB whereas with SQL Server one must resort to T-SQL to get some of the schema information. As I found out when I was developing Data Port Wizard, SQL Server does not allow you to mix direct table access (inserts) with IDENTITY_INSERT: you have to use an SQL INSERT command. Also, Jet and SQL vary wildly in how they manage IDENTITY columns: Jet makes it a piece of cake, SQL is a bit harder especially when you have to know the current seed. Finally, I found out that there is no way to create the equivalent of a ROWGUIDCOL column in Access through a SQL CREATE command - you do have to go through OLE DB.

Having said all this, I am not sure that this will be the final preparation code for SQL Server. As a matter of fact, with a more advanced database engine I will ba able to use triggers to flag any changes to the tracked tables (or so I hope...).

Wednesday, May 24, 2006

Data Port Sync Released

Version 1.0.500 of Data Port Sync has been released. I will be adding more samples to the code to better illustrate what is an arguably complex piece of software. If you want to take a look at how the whole process works, I advise you to start by the JetSync sample (only available in C# but I'm porting it to VB). This sample does the whole synchronization process for one database:
  1. Prepares the Access database for synchronization by adding the tracking columns and tables
  2. Exports the database to the device in SDF format (either SQL CE 2.0 or SQL Mobile)
  3. Tracks the changes on both ends and
  4. Merges them according to a very simple rule: the Access database always wins

There is another C# sample (Prepare) that you may use to prepare and "unprepare" Access databases and I have used it together win an end-user sample that I am also shipping (this time it's a VB sample that I will port to C#) that does steps 2 to 4 and is supposed to act as a template for a custom solution.

All thoughts and suggestions are most welcome for this product.

Friday, May 19, 2006

Standalone device emulator

Good news: the standalone device emulator is here!

Thursday, May 18, 2006

Synchronization stability

Data Port Sync is now undergoing a testing phase and although things are looking bright, I had overlooked one important detail: what happens when something fails?

There are four phases to the synchronization process: 1) preparation of the Access database; 2) exporting the Access database to the device in SDF format; 3) tracking changes on both ends and 4) merging the changes. When I designed the tracking code I assumed it would be okay to immediately mark the changes in the database, both in the user table and in the tracking table. As a matter of fact, this is a reasonable solution if you just want to track changed data, but it is not very safe if you need to process this information subsequently. If that process fails, you essentially lose the tracking information (at least with the way I was doing it).

To solve this issue, the data tracker now makes no change to the database. It generates a list of changed rows (for both databases) and the generation of this list is only affected by the posterior changes made to the database. The importance of this is that if the merge process fails, I can roll everything back and rerun the data tracker. All changes will be there.

Data Port Sync will become a product by the end of this month, before I go to MEDC Europe in Nice. Stay tuned.

Thursday, May 04, 2006

SQL Mobile DateTime parameters

Looks like I have found another small issue with SQL Mobile. I found out about this after a customer reported not being able to run a parameterized INSERT command with a DataTime parameter through DesktopSqlCe. The OLE DB provider reports the DateTime value as having 8 bytes when it in fact has 16. The ADO .NET provider seems to work correctly.

Thursday, April 27, 2006

Preparing for SQL Everywhere - II

After figuring out how to make SQL Mobile work on my XP PC (I have VS 2005 installed so the license works), I decided to port to the desktop the device component DLL I use in all my products. Most of the code (like 99% of it) was immediately reused, barring the occasional precompiler tweaks. The major challenge was not to change too much on the support code and this implied simulating a RAPI or TCP/IP connection and the corresponding data streams.

The solution I came to was quite simple: two asynchronous data buffers. The first is written to by the client code (dssce.dll) and read by the server (LocSqlCe.dll). The second data buffer is used in the exact reverse way.

When I finally managed to make the whole thing work I was amazed by how slow it was... The data streaming code was the culprit because it was chewing up too much CPU bandwidth in its own thread, especially while waiting. The solution? A criteriously inserted Sleep(0) on the reader thread.

The bottom line is that DesktopSqlCe users may now develop their applications against a local SQL Mobile database. Look Ma: no PDA!

Monday, April 24, 2006

Preparing for SQL Everywhere

Since Microsoft announced that SQL Mobile would run on all desktop platforms under the "SQL Everywhere" name, I have been wondering how easy it would be to set up my development PC in order to start testing the native OLE DB interfaces.

Well, it's easy enough. First, make sure you have Visual Studio 2005 installed (one of the versions that carries the SQL Mobile bits). Second, go to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE directory and copy all the sqlce*.dll files to your C:\Windows\System32 directory. Third, register the sqlceoledb30.dll file using regsvr32.exe Finally, open the ssceoledb30.h file and copy the SQL Mobile definitions to a header file of their own.

After this very simple routine, I was able to quickly write a desktop C++ application to create an SDF file on my desktop using code borrowed from eVC3/4! You just need to include your new header file after atldbcli.h and you are done... I will give all the details in an upcoming article.

Ah, and don't forget: this only works on development machines with VS 2005 and Windows XP Tablet PC edition. These license limitations should be lifted when we get the Everywhere bits by the end of this calendar year.

Thursday, April 06, 2006

SQL Server Everywhere Edition

Paul Flessner has just announced what many of us have been asking for: SQL Mobile on desktop PCs. Read all about it here.

Tuesday, April 04, 2006

Synchronizing multiple PDAs - success!

I write this post after my first successful synchronization of one Access database and two PDAs with SQL Mobile (an iPAQ 2210 and an i-mate K-Jam). The sync code now recognizes each client through a GUID value that is stored by the client PDA. IDENTITY space partitioning is working and all PDAs are generating non-conflicting negative IDENTITY values where the Access database generates positive ones. All of this can be configured by the user or consuming application.

My final hurdles are to implement conflict rules and constraint-directed updates. Right now, there is only one conflict rule: if both the PC and the PDA have updated the same row (as identified by a GUID), the PC wins. As for constraint-directed updates (this is a pompous term that means "insert, update and delete in foreign key order") I have already devised a means to calculate an "INSERT order", the order by which it should be safe to insert new rows without FK clashes. It so happens that it is the reverse order for DELETE.

Lots of tests ahead...

Friday, March 31, 2006

MEDC Europe 2006

I am going to MEDC Europe 2006 - are you? This is the place to be if you want to stay ahead on Windows Mobile technologies. As the banner says, register now for an early-bird rebate. See you in June!

Wednesday, March 29, 2006

Synchronizing multiple PDAs

So far, the Data Port Sync preview has already some synchronization capabilities. It will only work safely with one PDA and still has no collision handling code. When the same row is updated on both ends the PDA prevails, but this will change soon as collision-handling policies become available.

The day's work was spent on calculating INSERT order for tables (reverse of DELETE) so that database structure and constraints are not violated. Also, work is under way to support multiple PDAs synchronizing against the same database.

Hope that by the end of the week I will have more to write about.

Tuesday, March 21, 2006

DataPortSync pre-released

I finally managed to get the code to synchronize an Access database with a SQL Mobile one. There are still hundreds of little issues to solve like retrieving error information from the custom Access provider and making sure the IDENTITY values are correctly recorded. But it is working!

If you want to take a look at how the code is coming, download the latest version of DesktopSqlCe (1.3.200) and the DataPortSync pre-release 1.0.200. Be prepared for a bit of a rough ride!

Wednesday, March 15, 2006

Second step to Sync

Before synchronizing two databases, one must know what has changed in both of them. Getting to know what changed involves some exercise, especially when using two database engines that will not support any data tracking (at least with exposed APIs). Microsoft Access and SQL CE / Mobile fall in this category and require some work and data in order to track changes.

After figuring out a simple means of doing this, using additional tracking tables and columns on the tracked database, I wrote a new sample using the pre-release code of Data Port Sync and DesktopSqlCe (some minor errors were corrected meanwhile). After selecting an Access database, this sample prepares it (according to the user's selection of tables and columns) and exports it to the device. Preparation involves adding new tables to the database and new columns to the tracked tables.

After all is ready, you can start playing with both databases and the code will report what changed and in what table. Tracked rows are uniquely identified across both databases with a GUID value. I expect to upload the sample to the site very soon. Stay tuned.

Monday, March 13, 2006

First step to Sync

Today, I published the first sample for a new product - Data Port Sync. This is a .NET component that handles most of the chores required to synchronize a Microsoft Access database with either SQL CE 2.0 or SQL Mobile. It's ultimate purpose is to help developers build their own customized synchronization solutions and builds on three fundamental components:
  1. A Microsoft Access data tracker - prepares a database for tracking and tracks changes made to the database. Tracking is achieved by adding some control tables and columns to the tracked tables.
  2. SQL CE / Mobile data tracker - uses the same data tracking algorithms to track changes made to a mobile database.
  3. Data transport - building on DesktopSqlCe, I'm adding some direct access features for Access databases (so that .NET developers don't have to bother with INSERT commands).
The challenge I'm facing now is to build a number of samples that will cover all the bases, from simple database export and import, to complex merging procedures. So, I'm spending most of my time writing a C# object model to wrap my C++ code, but then I'm starting to get used to it.

Here is the first sample: an Access to SQL CE 2.0 / SQL Mobile export tool. You will need to download the latest version of DesktopSqlCe (soon to be renamed).

Enjoy.

Thursday, March 09, 2006

Another issue with BLOBs and SQL Mobile

I just received a kbAlertz with this interesting title:

An .sdf database file in SQL Server Mobile is larger than the same .sdf database file in SQL Server CE 2.0 when you use the ntext data type or the image data type

SQL Mobile is a rewrite, not an upgrade of the SQL CE 2.0 code, so one should expect some differences in behavior. I am collecting a list of these, both high and low-level and will publish them soon.

Friday, February 24, 2006

The Bookmarks and BLOBs Bug

I can now officially discuss this bug on SQL Mobile: you should be very careful when running a SQL command that returns a scrollable cursor and has BLOB columns in it.

Read more about this issue here.

Wednesday, February 22, 2006

Thread synchronization errors

This is something that scares the hell out of me. Thread synchronization is perhaps one of the most subtle arts in our trade and I keep learning every day. The bottom line is that nothing is atomic and you should always consider the worst case scenario. Always play on the safe side - assume nothing.

My last bang on the wall was quite simple. At the very heart of all my products, both on the desktop and the device sides, lies a thread that reads all the messages and stores them in a queue. The device side is simpler - the queue is simply polled. But the desktop side is a bit more complex and requires signalling events. When a properly formed message arrives and is stored in the queue, a call to SetEvent is made to signal other threads of the availability of data.

Now, when I rewrote the code for the 2.0 versions of the products, I called SetEvent before adding the message to the queue. Access to the message queue is guarded by a critical section, as it should. It so happens that adding a message to the queue is a bit lengthy (requires memory allocation) and this meant that after firing the event any thread that would test the event before the message got to the queue would be in a strange situation: there's a signal telling me there's something on the queue but the queue is still empty...

The solution is obvious - raise the event after the message is added to the queue. Humpf...

Friday, February 17, 2006

Friday, February 03, 2006

CEDB vs EDB

Sue Loh shows us the major differences between CEDB and EDB in this blog entry. She also leaves an interesting challenge - to write an upgrate tool.

Tuesday, January 17, 2006

Installing SQL CE 2.0 and SQL Mobile on a Storage Card

I have just published an article that describes the techniques required to install SQL CE 2.0 and SQL Mobile on a storage card in a Windows Mobile 2003 device. You can read the article here.

Wednesday, January 11, 2006

Supporting Visual Basic 6

It's very interesting to see that old VB6 is still being used by a very large number of developers. When I started to develop my component products, I always thought that the safest bet would be to provide a very low-level C API and use it to build higher-level components in C# for the .NET world.

This experience has been quite easy because the .NET Framework has lots of classes and mechanisms to suport marshalling and binary data conversions, so the supporting API can really be made very basic indeed. Not so with VB6.

Visual Basic 6 is a very old progamming language and (I think) it lacks the skills required to qualify as a systems development language. My case is quite simple. After shipping the first version of DesktopSqlCe (which only supported C and .NET), I started to get a lot of requests to port the higher level code to Visual Basic 6. And that is the unfortunate work I am doing right now.

My major gripe is the apparent lack of functions to convert from the language types to binary streams (C++: why do you need functions for that ???). The .NET Frameworks has a bewildering number of conversion classes, but VB6 lacks almost everything. Bottom line: I am adding a lot more functions to the low-level API so that VB6 can talk to it...

And yes, I am adding VARIANT support to the code...

Wednesday, January 04, 2006

K-Jam and ToolHelp

My good friend Cristiano Severini has just published his first Windows Mobile application: CSB (standing for Call and SMS Blocker). One of the chores CSB must accomplish is to shut down tmail.exe during setup in order to install MAPI filters. The setup process finishes with a soft-reset, restoring all processes.

To shut down a process, you must get a handle to it and this is usually done through the ToolHelp API. This API creates a static snapshot of all running processes and allows the application to enumerate them and kill these processes at will. Interestingly, this technique worked quite well until Cristiano tested it in a K-Jam. Being the fortunate owner of one, I was volunteered to help (forced, actually) and found out that the CreateToolhelp32Snapshot funtion would fail every time when called with the TH32CS_SNAPPROCESS flag.

Thanks to one very clever fellow MVP (Paul Tobey) and Microsoft employee (Ilya Tumanov), I now know that this function was failing because it was generating way too much data in the snapshot. To limit the amount of data it generates, one needs to add another flag: TH32CS_SNAPNOHEAPS. Interestingly, this flag is not defined in tlhelp32.h file, where it was supposed to be. Here it is, with my thanks to Paul and Ilya:

// optimization for text shell to not snapshot heaps
#define TH32CS_SNAPNOHEAPS 0x40000000

Tuesday, December 27, 2005

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.

Friday, December 23, 2005

SQL CE 2.0 Support ends this month

Read more on Nino's post.

Season's greetings, everyone!

Monday, December 05, 2005

Microsoft SQL Server 2005 Mobile Edition Device SDK

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

Friday, December 02, 2005

New site, new forum

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

Wednesday, November 30, 2005

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.

Thursday, November 24, 2005

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!

Wednesday, November 23, 2005

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.

Saturday, November 19, 2005

Sunday, November 13, 2005

Post-TechDays interview

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

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

Friday, November 11, 2005

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.

Thursday, November 10, 2005

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.

Saturday, October 29, 2005

Friday, October 28, 2005

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!

Saturday, October 22, 2005

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.

Friday, October 21, 2005

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.

Sunday, October 16, 2005

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!

Saturday, October 15, 2005

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.

Wednesday, October 12, 2005

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

Monday, October 10, 2005

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.

Sunday, October 09, 2005

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.

Saturday, October 08, 2005

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.

Tuesday, October 04, 2005

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!)

Tuesday, September 27, 2005

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

Sunday, September 25, 2005

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

Thursday, September 22, 2005

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

Wednesday, September 21, 2005

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

Wednesday, September 14, 2005

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.

Monday, September 12, 2005

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.

Monday, September 05, 2005

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.

Thursday, September 01, 2005

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! ;)