Friday, December 15, 2006
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 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
Wednesday, November 29, 2006
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):
- 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.
- 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
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;
int nScale = -1;
for(pCur = pszText; *pCur == ' '; ++pCur)
if(*pCur == '-')
sign = 0;
sign = 1;
for(; *pCur && nScale; ++pCur, --nScale)
big = big * 10 + (*pCur - '0');
else if(*pCur == '.')
if(nScale > 0)
nScale = scale + 1;
memset(val, 0, sizeof(val));
for(i = 0; i < 16 && big != 0; ++i)
val[i] = (BYTE)(big & 0xFF);
big >>= 8;
It's faster, believe me.
Tuesday, November 14, 2006
Thursday, November 09, 2006
Tuesday, November 07, 2006
Anybody else out there with some bright ideas?
Monday, November 06, 2006
Thursday, November 02, 2006
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
- 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
Tuesday, October 24, 2006
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
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
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
Wednesday, September 27, 2006
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
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
Let's see if I can find a crack for this product myself (hehehe).
Tuesday, September 12, 2006
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
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
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
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?
Thursday, August 10, 2006
Monday, August 07, 2006
Monday, July 03, 2006
Thursday, June 15, 2006
Tuesday, June 13, 2006
Friday, June 09, 2006
Thursday, June 01, 2006
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
- Prepares the Access database for synchronization by adding the tracking columns and tables
- Exports the database to the device in SDF format (either SQL CE 2.0 or SQL Mobile)
- Tracks the changes on both ends and
- 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
Thursday, May 18, 2006
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
Thursday, April 27, 2006
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
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
Tuesday, April 04, 2006
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
Wednesday, March 29, 2006
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
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
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
- 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.
- SQL CE / Mobile data tracker - uses the same data tracking algorithms to track changes made to a mobile database.
- 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).
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).
Thursday, March 09, 2006
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
Wednesday, February 22, 2006
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
Tuesday, January 17, 2006
Wednesday, January 11, 2006
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
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