I did eventually figure this one out. Not why it happens, but how to avoid it.
It appears that if you open a database- file in the application directory, the file is not always actually opened. Sometimes it is, and sometimes not.
If, on the other hand, you open a database-file based on it's full path from root, you'll always get it to work.
It took me the better part of 4 days to figure this out, as I got no error messages what so ever. AIR claimed the file was open.
I tried this so many times, and there seems to be no pattern on when things work and when they don't. A little message saying" you have opened a new, blank, empty file" would have saved med from all this lost time.
Perhaps a good idea for the next version?
The application directory should be considered read-only, because in fact, it is on some operating systems and configurations. Although you can try to work around this using the full path, this only gets around the AIR restrictions, so your app will still fail for some users.
A good strategy is to treat the database file in the application directory as a template and copy it elsewhere before using it in the application.
I agree that better error messages would be useful, although ActionScript doesn't have a concept of warnings and opening new, blank, empty files is a valid operation. As a workaround, you could do a sanity check for a known table before proceeding.
I do agree 100% with you, but my problem was not quite that simple.
On my first 10 -12 attempts I could read the file.
Then it intermittently stopped working. I had the same type of file in the same folder. Perhaps if one database-file is copied to the application preferences-folder it stops looking in the application-folder for other files? I tried to dump the url of the file out in debugging, and everything looked correct as well. (I'm accessing quite a number of sqlite-databases from one application, not sure if that is normal. All are read-only)
One error message that would be very nice is to get a distinct error that says something like " there are NO tables in you database. Are you CRAZY??" . I thought the problem was that I could not read one specific table, and it turned out there was nothing there, because it was apparently an empty file.