We recently decided to add some basic persistence to Pylon, our latest mobile application for accessing SharePoint. Nothing complicated, just logging levels, username and SharePoint URI.
We decided to use SQLite for persistence instead of the BlackBerry Persistent Store because of our previous experiences with the latter – where we encountered some stability issues such as the insidious 523 error which requires removing the battery to fix. We were also looking for an approach that would be more compatible when we port our application to iOS and Android.
The BlackBerry code samples for basic SQLite operations are straight-forward: http://docs.blackberry.com/en/developers/deliverables/8682/Code_samples_702046_11.jsp
However after basing our code on the samples we did run into a couple of issues.
SQLite not working on some phones – “Error: file system not ready”
The first issue we ran into was that the database simply didn’t work for certain phones. The confusing thing was that it wasn’t even consistent for phones of the same model.
It turns out the key was this line in the BlackBerry documentation:
“You can create database files in memory, on devices that support it, by specifying the corresponding file system path.”
Our application would use an SD Card if there was one, otherwise we would use device memory. If the device didn’t have an SD Card AND it didn’t have eMMC memory then we would get a “file system not ready” error when creating a database.
Phones that support SQLite DB in device memory (eMMC)
Phones that DON’T support SQLite DB in devicememory
Even with an SD Card, SQLite might not work if Media Card Support is turned off or if the phone is tethered to a computer and Mass Storage Mode is turned ON.
Intermittent Error – “Error: File system error (12)”
Another error we ran into was that sometimes opening the database soon after reading or writing to it would fail with the error “File system error (12)”. Just for testing I added code that would try to open the database a second time in case of failure after sleeping 100 milliseconds. This second attempt to open the database would always succeed.
Looking for “File system error (12)” online I found a few instances that were determined to be caused by bad queries or by failing to close the database or a SQL Statement. However the exact same query that worked most of the time was being called when the error would occur and after reviewing the code we determined that all databases and SQL statements were being correctly closed.
It turns out that we were following the BlackBerry examples a little too closely and opening/closing the database for every read/write operation. The overhead of this meant that the database wasn’t ready to be opened in time for the next operation.
We fixed the problem by only opening the database once the first time and then reusing that handle all subsequent times until we close the database on application exit.