1

I've followed the instructions given here for introducing an existing SQLite database to your Android app.

When I query the table "android_metadata" this is fine. But when I run a similar query on my own table "words" (which has _id for primary integer key) I get a table does not exist exception and the app crashes.

Why is that?

Code:

Cursor c = myDatabase.query("android_metadata", null, null, null, null, null, null, null);

works but

Cursor c = myDatabase.query("words", null, null, null, null, null, null, null);

returns a table does not exist exception.

This is how I'm creating the database (the references to paths and filenames are correct):

private void copyDatabase() throws IOException
{
    //Open local db as the input stream
    InputStream myInput = mContext.getAssets().open(DB_NAME);

    //Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //Transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length = 0;
    while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
}

(Note: To my eyes, the table is there. I'm looking right at it in my SQLite browser.)

Ken
  • 30,811
  • 34
  • 116
  • 155
  • no one is gonna be able to answer without some code... – tobyodavies Feb 07 '11 at 02:35
  • it's not a coding issue - if i can find one table and not another, it's an issue with how the sqlite database has been edited to suit android. i'm sure someone must have come across this.... – Ken Feb 07 '11 at 02:38
  • for further code, see the "here" link i enclosed. it's pretty standard SQLiteDatabase helper stuff. – Ken Feb 07 '11 at 03:04
  • What system(s) are you testing on? There have been known problems with using copied databases on the HTC Desire. – Dan Breslau Feb 07 '11 at 03:23
  • Just the simulator (on Mac, via Eclipse). How can I possibly find one table and not another? It makes no sense to me at all. Is there a standard bug I'm currently not aware of? – Ken Feb 07 '11 at 03:50
  • At least it's becoming clearer to me why 99% of the discussion on Android and SQLite I'm digging up starts out by creating a new database within the app(!) *this is easier to do by an order of magnitude* – Ken Feb 07 '11 at 04:03
  • Is the path to your database following the rule: /data/data/your.applications.package/databases Android is expecting your database at that location. http://stackoverflow.com/questions/2318784/the-sqlite-database-path – Darokthar Feb 07 '11 at 03:18
  • Yes it is. Like I said, I can query the "android_metadata" table but when I query my own "words" table I get an exception. – Ken Feb 07 '11 at 03:23
  • @SK9 +1. I am developing an app which has to copy the db from the asset folder on starting and it is not a piece of cake apparently. – Paranoid Android Oct 07 '12 at 18:43

2 Answers2

3

You're following a red herring. The android_metadata table is created in every android database no matter what.

The real mechanism for determining your issue is to simply run your app in the emulator and check out the database. If your emulator is running and the application has setup the database, run:

adb shell
cd /data/data/your.application.package/databases
ls

if ls shows the database name you expect, run:

sqlite3 <your db file>

at the sqlite3 prompt run : .schema

This will print out the tables of the databases. My guess is that you'll find just the meta data table because android is not actually reading your database from your external location. If thats the case, comment back and I can try to help you through that process.

Nick Campion
  • 10,479
  • 3
  • 44
  • 58
  • perhaps this is what you meant you did when you said '(Note: To my eyes, the table is there. I'm looking right at it in my SQLite browser.)', if so i'll remove this answer. This is the definitive mechanism though. – Nick Campion Feb 07 '11 at 04:26
  • You're bang on. That's helped me to narrow it down. – Ken Feb 07 '11 at 09:19
  • OK - you're exactly right about the red herring. Because the table "android_metadata" is created by default, and because I'd added it in to my own database to suit Android, I thought it was being found. Consequently I was under the mistaken impression that copyDatabase was being called and my database file had been loaded in. Rather, copyDatabase was not being called at all and seeing this clears the problem. I'd have been much better off coding this by myself from the outset - lesson to self: Don't overly trust publicly available code, I advise against using the code linked in my question. – Ken Feb 07 '11 at 09:48
  • Good. I'm glad this fixed - thank you, thank you. To be clear, even if I'm bringing in an SQLite database file of my own via assets (and it already contains some data) I don't need to create a table called "android_metadata" myself in this file? It's redundant, because Android will do this anyway? – Ken Feb 07 '11 at 10:28
  • Also, correct me if I'm wrong, but the Android docs do not say that a table called "android_metadata" is created by default...? (This is based on searching the docs for the string "android_metadata".) In which case, what an evil red herring. – Ken Feb 07 '11 at 10:51
  • They don't comment on 'android_metadata' because my guess is that they don't want you to touch it. Its used by Android to store special information about the table. Your mechanism for importing your own table is fairly close. Here are the steps i use: 1. Select a db name 2. open that database as readonly 3. Close the database 4. Open the file with a file writer 5. open the original database in your assests dir 6. copy from your original to the open db 7. Close both streams and re-open database – Nick Campion Feb 07 '11 at 13:52
  • Take a look at this answer i wrote: http://stackoverflow.com/questions/4753896/externally-generated-sqlite-database-considered-read-only-by-androids-sqliteopen/4753927#4753927 – Nick Campion Feb 07 '11 at 13:53
0

You could try to create your DB instead with the SQLiteHelpter. It checks if one exists and if not it creates one, without any extra effort for you.

I was already posting an example on other question, check it here:
Android - Sqlite database method undefined fot type

Hope this helps :)

Community
  • 1
  • 1
Beasly
  • 1,517
  • 4
  • 20
  • 30
  • Thanks for this. My database is a readonly asset. I've already managed to create databases from new, this is a different problem. – Ken Feb 07 '11 at 08:59