1

I have a project with a set of classes that are responsible for their respective database tables.

Each table managing class contains CRUD methods that follow the pattern of get connection, run crud operation, close connection:

public class PersonManager {

    SQLiteDatabase db;
    DbAdapter dbAdapter; //This is a subclass of SQLiteOpenHelper

    public void addPerson(Person person)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put("email", person.email);
        contentValues.put("first_name", person.firstName);

        db = dbAdapter.getWritableDatabase();
        db.insert("person", null, contentValues);
        db.close();
    }

    ...other crud/utility methods omitted...
}

Now that I am upgrading my database via onUpgrade(), I run into database locked issues.

The exact error message follows:

CREATE TABLE android_metadata failed
Failed to setLocale() when constructing, closing the database
android.database.sqlite.SQLiteException: database is locked

It appears that onUpgrade is either meant to:

1 run db.execSQL() calls or
2 use helper classes that use onUpgrade()'s SQLiteDatabase rather than their own

It would be much easier to use my table managing classes to migrate data in onUpgrade() than db.execSQL() statements, or rewrite all my CRUD methods to take onUpgrade()'s SQLiteDatabase.

Am I setting up my database access correctly? If the above code follows the correct pattern, what should I do to fix this issue?

Thanks!

Shellum
  • 3,159
  • 2
  • 21
  • 26
  • I've encountered this issue before. Just a few questions to help narrow it down, are you doing any threading, and is your SQLiteOpenHelper in this instance a singleton? There should only be one access point (http://stackoverflow.com/a/3689883/429047). – Mo Kargas Mar 02 '12 at 11:32
  • I have a class that extends SQLiteOpenHelper. I instantiate this class rather than return a static instance. I wasn't sure if it was okay to pass a Context reference to a singleton incase that reference went away. I also open and close the connection on each CRUD method. – Shellum Mar 02 '12 at 17:09
  • What I don't understand is why it fails on that specific table on Upgrade - android_metadata should exist prior to upgrading. Can you post your onUpgrade method from your helper? – Mo Kargas Mar 02 '12 at 23:43
  • I'm still solidifying what is going on, but it appears that you cannot call getWritableDatabase() from onUpgrade() even if the getWritableDatabase() call is made indirectly via a table manager class. This is a problem because most of the literature points a person to open and close connections when they are used instead of keeping some global connection open. – Shellum Mar 03 '12 at 00:37

2 Answers2

3

Here's your problem:

db = dbAdapter.getWritableDatabase();

When you're in onUpgrade(), you have to use the SQLiteDatabase handle that onUpgrade() provides you. So your solution is to rewrite your addPerson function to take one more argument -- an SQLiteDatabase handle:

public void addPerson(Person person, SQLiteDatabase db) {...}

If you need to call addPerson() from elsewhere in your project, then keep your current addPerson(Person person) function, have it do that db = dbAdapter.getWritableDatabase() call, and pass db to your two-argument version of addPerson().

Theodore Sternberg
  • 141
  • 1
  • 8
1

I didn't get any answers, so I asked on a dev hangout.

According to the Android Developer Hangout Team, onUpgrade is only meant for structure alterations, not really for data migration/manipulation.

Shellum
  • 3,159
  • 2
  • 21
  • 26