sqlite with android no such table

Question

I have been testing my app which uses the dbadapter from Reto Meir's earthquake example. Everything was going ok for several days but when debugging with the app on the device today I got the 'no such table' error. I changed the name of the database and all runs well again. This doesn't give me much confidence regarding potential other users.

Since it seems impossible to see the database on the phone, by design I suppose, I can't see how to find out what caused the problem and take steps to avoid it. The database appears to open ok at the start of the program but errors when handling a select query. Just changing the name of the table doesn't fix it, it has to be a new database name.

As the change of name allows it to run ok I can't see that the code is wrong. I wonder if the data becomes corrupted.

I've also found that after successfully inserting a row, then later getting a cursor to allitems sometimes produces a -1 error against a get for one of the column names. How can a column name drop out of the columns index?

I've googled this type of problem and whilst there are a lot of folks with the problem and a lot of replies I can't find anything which informs on the underlying reason for these problems - which is what I am after.

Answer 1

I was having the same problem with this. I was changing the version number, uninstalling application etc... in the end it was due to me not submitting the transaction of the sql call. It might be worth checking out the answer in the following for some clarity - https://stackoverflow.com/a/13568419/1634369

Answer 2

Please explicitly link to the example you reference. The way I understand it, you create your own database at launch, unless the database already exists on the device, in which case the database creation is skipped. Correct?

If that is correct, then the only thing you need to do is uninstall the application whenever you update the database design (to remove the old database, with the old table names, etc) and re-install, so the database is recreated with the new table names.

Otherwise you will have updated application code that tries to work with an outdated database.

Answer 3

The database is available on the phone and if you can 'adb shell' to the phone, you can also use sqlite3 command line shell to connect, examine, query and modify your database. It's pretty much the same functionality as you'd get with oracle's or mysql's command line tools.

You can see some more details here for example: http://www.infinitezest.com/articles/using-sqlite-from-shell-in-android.aspx

As for "no such table" case - it really depends on where you store your database. You can choose (at DB creation time) where the database is stored - it might be the internal /data/data (usually) folder where your application resides or you could (mistakenly) create it in cache space - cache can be cleaned at any time whenever Android's OS finds that it needs more space, so this might explain why your database disappeared.

Another possibility is that instead of reinstalling the application, you uninstalled it and installed in two steps. Uninstalling application also deletes all the private data for the application (on most phones, but not on all), whereas reinstalling application (For example by clicking Run in eclipse) does not delete the data. In any case - in you application's onCreate you should recreate the database including all tables - always if you find it has not been yet created - because you never know if you run application for the first or subsequent time.