Archive
Unspecified Errors in SqlServerCE
Wow… I can’t believe it has been so long since my last post.? I guess that’s what happens when you spend all your time trying to figure out ONE STINKING BUG.
This is the CF application I have been working on for over a year.? It is a huge application for CF, well outside the normal realm of expectiations, I would say.? Dozens of forms, user controls, custom classes, and more than 10 database tables.? Things were actually going quite well, until… unpredictably, seemingly at random, the software would throw an “Unspecified Error Occurred” message.? No further detail was available, but following the stack trace we would discover that it would happen the application was trying to read the database.? Not every time, and not always in the same place.? On the same dataset, it would happen once and not again for a while, then start happenging again and again and again and… need I go on?
It actually started about 6 months or so ago.? Our testers would be able to work for awhile, but once the error happened they had to shut the app down and restart.? Now you might be inclined to think (as I did) “no problem, just put it in debug and find out whats going on”.? Aye… there’s the rub!? You could NOT recreate it in Debug.? In fact, we apparently couldn’t recreate in the office at all!? The user would go to the field and within minutes have the error.? He would bring it back to the office and sit at my desk to show it to me and he could go hours, processing hundreds of records, without a single error.? Over the course of several weeks I processed thousands of records myself and never once received the error.
Naturally, I spent a good bit of time online trying to find some other poor sap who had had the same problem, but found precious little information.? I found a couple of references to open database connections, disposed objects, and Garbage Collection, so I restructered all the database connectivity, added a ton of memory management code, and littered my classes with GC.Collect() and GC.WaitForPendignFinalizers().? Everythign worked great for me, so I sent it back to the field.? No Joy… same error, same problems.
All indications were that it was a memory management problem, but not being a C++ pocket-protector type, I couldn’t find anything else to do.? What I did know, or so I thought, was that the problem was with SqlServerCE.? So I talked to a buddy of mine, and he pointed me to SQLite, a small footprint database that I could use to replace SqlServerCE.? And there is a .NET Managed Provider I can get for it too… and it’s all open source and no cost… so I researched it, and since there were some additional advantages for my particular project (and I had no other tricks in my bag), I spent a couple of weeks tearing SqlServerCE completely out of my project and replacing it with SQLite.? Fortunately, thanks to the consistency of .NET Managed Providers, I was able to complete the work with little trouble.? Again, my testing went just fine, so happy as a clam thinking I had corrected the issue, I sent it back to the field for testing.
The first record on the first day of testing threw the error… well, sort of.? Instead of “Unspecified Error”, SQLite began throwing “Could not insert to database. Database is full.” Huh?? This caused my angst in a number of ways: first, where the error was thrown, nothing was being inserted.? Second, while the project is large, the database is decidedly NOT.? A grand total of 1500 records or so in all 10 tables: I had read about users with tables in the MILLIONS in SQLite.? The internal memory of the device was no where near capacity, and neither was the storage capacity of the Compact Flash card the database was on.
I almost gave up on the project right then and there.? But since that would have meant also giving up my gainful employment, back to the Internet I went.? One nice thing about using something like SQLite is that I was able to communicate with an active community who uses the product, including the guy who actually develops and maintains it.? I won’t get into the gory details, but there was a lot of head scratching.? Eventually, someone suggested that it sounded like a problem with the CF Card.? It turns out that like Rewriteable CDs, CF Cards have a limited number of times they can be written over before they fail.? I was quite interested in this notion… I hadn’t considered a hardware problem, and all the testing was being done on a particular device with the same CF card over and over.? Why, this must be it!? So I trotted off to Staples, bought a new card, shoved the database on it and back out to the field goes the device, sure to work this time!
Do I really have to say what happened?? The tester was actually scared to call me and tell me that he got the error message almost immediately.? OK, now I AM ready to quit.? There is nothing left I can do… I’m physically and emotionally spent at this point.? I can’t sleep for the constant thinking and worrying about this problem.? The only thing left to do is contact the guy who wrote SQLite and pay him to fix the problem.? So I did… contact him that is.? And he replied.? We even spoke on the phone, and he politely told me that he had been following my problem on line and that he had never seen anything like it and that there was no explanation for why the database would do that given the circumstances.? He said that he would take my money but didn’t know if he could get anywhere.? He also suggested that I check first with the guys who wrote the managed provider for SQLite (since it wasn’t him and he isn’t a .NET guy).
So I did that next.? I got on the forums for the managed provider.? I went around the barn again, but to no avail.? We did, however seem to reach the same conclusion: there is something wrong with the memory management.? And the came THE POST. Someone posted that they had seen a problem with CF Cards not remounting correctly after hibernation.? Was I handling the database connection before and afterthe device shut down?
Ummm… no I wasn’t.? What a silly thing to have to do, I thought.? The poster said that he had had a similar problem, and that Microsoft changed somewhere back in 2003 how Windows Mobile handles remounting storage cards after shut down.? It used to operate the way I thought it should, but MicroSoft changed it… on purpose… because the user *might* have replaced the memory card while it was shut down.
And the light bulb went on.? Even back to the original SqlServerCE database, this is what was happening.? The application creates a single database connection when the user starts the application to the database on the CF Card.? User functions for a while, no problem.? Device shuts itself down in a period of idleness.? Application may or may not be able to maintain its connection to the database.? When it can, no probem.? When it can’t: bombo, weird messages ensue.? The problem only happened when the device shut down automatically to conserve power and was restarted.? That’s why when I was in DEBUG it would never happen: plugged in to my PC, the device never shutdown.? It’s why it wouldn’t happen in my office: the user was continuously processing the application, never letting it rest naturally between records as it would in the field.
I, timidly, began to think that this time we might actually have found something useful.? I spent a couple of days rewriting the software to move the database to internal memory (which does not suffer from the same remounting issues) when the application starts, and moving it back to the CF Card when it closes.? In essence, the CF Card becomes the transport mechanism and backup.
It worked great.? In fact, it was faster and more responsive according to the field testers… and nary an error in sight!? I haven’t felt so elated in years.? A great weight shifted off my shoulders.? It has completely reinvigorated me, and I am back working on the project 110%.? Which is a good thing, too, since the deadline is next week!
Deploying CF .NET 2.0 applications using MSI
So I’ve been developing Smart Device apps for the last two years, both in VS2003 and now in VS2005. But as the only CF developer in a tiny shop, I have always been able to install them by just using the Deployment option in Visual Studio. Now, however, one of the products I’ve been working on is ready to go to the field for some testing, which means other people have to be able to install it.
Frankly, most of the documentation on deplying CF.NET apps is pretty scary. CAB files, custom INI files, Registry entries, the works. I’ve definitely been spoiled by the MSI features available to Windows forms apps. So I’ve been digging around and experimenting, and I created a CAB file for install and then found the Smart Device CAB project, so I created that but it still required the user to manually copy it over to the device and double click it and yada yada yada. Too much for most of my target audience, and not very professional.
So I dug some more, and I was about to give up, when I found this article on MSDN about using MSI to handle the install to a Smart Device. I went through the article, and after a little trial an error I got it to work. Here are the highlights:
- Create your solution and build all the projects. Use Release to keep them as small as possible.
- Add a Smart Device CAB project to your solution. Add all your project outputs to the applications folder and add a shortcut to your EXE to the Program Files folder. Build this project.
- Add a new Class Library project to your solution. This is an MSI installer helper class that handles events like before_install and after_install. The cool thing is that these events will be found and used automagically later. Fill out the events and build this project. This file has some Registry stuff in it and references a custom INI file that you will need to create, but the forumla is very simple.
- Add a new Setup project to your solution. This will be the MSI file that is eventually used to communicate with ActiveSync and install the CF application. By adding custom actions to the installer and referencing the helper DLL we created in step 3, the before and after events will automagically find themselves and fire.
OK, so this is in no way a step by step instruction list. For that, you will want to read the article (like 20 times). It is a little dense but thorough, and I was able to get it to work. I did encounter a permissions problem in the afterInstall event, so I still need to hash that one out, but it did install the app on my device.
One quirk I found was that the article instructs you to add a reference in the MSI project to the System folder and to place your output there for the CABs and the INI file. Unfortunately, this doesn’t jive with the pathing samples in the Helper class code in the article, which references System/TEMP/MyProject. In looking at it, I think the TEMP path is the best way to go, so just create the TEMP/MyProject path in the System special folder and dump your output CAB and INI files there.
Next I’ll be tackling how to integrate CF .NET deployment in the same MSI, so stay tuned.
Developing For .NET 