Android SQLiteStatement Insert; Subsequent calls fail

Question

I am trying to insert rows into a SQLite database within Android. A call to insert a record into my DB looks like this: DataHelper.insertChild(context, child). My goal is to wrap the functionality into statically called functions that handle all the nitty gritty. First let me share with you my code.

I am doing this via static methods:

private static SQLiteDatabase prepareChildDatabase(Context context) {
    Log.d(TAG, "DB: prepareChildDatabase");
    OpenHelper openHelper = new OpenHelper(context);
    return openHelper.getWritableDatabase();
}

Then I do my insert:

    public static long insertChild(Context context, Child child) {
    Log.d(TAG, "DB: insertChild");

    SQLiteDatabase db = prepareChildDatabase(context);
    SQLiteStatement insertStmt = db.compileStatement(INSERT);
    insertStmt.bindLong(1, child.getBirthday().getTime());

    // These are optional
    if(child.getName() != null) {
        insertStmt.bindString(2, child.getName());
    } else {
        insertStmt.bindNull(2);
    }

    if(child.getPhoto() != null) {
        String photoUri = child.getPhoto().toString();
        insertStmt.bindString(3, photoUri);
    } else {
        insertStmt.bindNull(3);
    }

    final long id = insertStmt.executeInsert();

    // insertStmt.clearBindings(); // Not sure this is necessary
    insertStmt.close();
    db.close();

    return id;
}

For references, my OpenHelper is here:

    private static class OpenHelper extends SQLiteOpenHelper {

    OpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "DB: OpenHelper: onCreate: " + CREATE_CHILDREN_TABLE);
        db.execSQL(CREATE_CHILDREN_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}

And finally, the variables, statements, and table:

private static final String TABLE_NAME = "children";
private static final String COL_NAME = "name";
private static final String COL_BDAY = "birthday";
private static final String COL_PHOTO = "photo";

private static final String INSERT = "insert into " 
    + TABLE_NAME + " (" 
        + COL_BDAY + ", "
        + COL_NAME + ", " 
        + COL_PHOTO + ") values (?, ?, ?)";

private static final String UPDATE = "update " 
    + TABLE_NAME + "set "
        + COL_BDAY + " = ?, "
        + COL_NAME + " = ?, "
        + COL_PHOTO + " = ?) WHERE "
        + BaseColumns._ID + " = ?";

private static final String CREATE_CHILDREN_TABLE = 
    "CREATE TABLE " + TABLE_NAME + " ("
        + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COL_BDAY + " INTEGER NOT NULL, "
        + COL_NAME + " TEXT NULL, "
        + COL_PHOTO + " TEXT NULL)";

So what is happening is that the first time I call this everything works fine. Insert works, data is there, and I can export the DB from the device to my SQLite browser. Subsequent calls to insertChild(...) break the DB. Opening doesn't work in that SQLiteBrowser shows no tables or rows and opening it within a text editor shows some things; my guess is that the DB is corrupted. No more rows are being inserted and I can't read it. No errors are thrown whatsoever.

I am guessing one of two things going on here. First, my handles aren't opening/closing correctly and some reference somewhere is getting borked by the subsequent calls. Second, my understanding of prepared statements is faulty. The thing is that I look at the code and it seems fine. Open a database, bind to a prepared statement, execute, close statement, close database.

Anyone out there willing to help me out with this? I've been logging, looking for exceptions, and trying things but nothing seems to work. I think I may have simply missed a little detail. That or I am going insane.

Thanks,

Mike

Update

I've turned my attention towards threads because the issue doesn't reproduce on my emulator. Check out these links:

http://www.kagii.com/journal/2010/9/10/android-sqlite-locking.html

android sqlite application is being forcefully closed on device

What are the best practices for SQLite on Android?

score 0 · Answer 1 · answered Mar 28 '11 at 21:19

0

You should open the connection always you need it. Or open it before do ALL the inserts before closing it.

answered Mar 28 '11 at 21:19

Marcos Vasconcelos

18,136
30
106
167

Yes, each time I insert I call getWritableDatabase() which should "Create and/or open a database that will be used for reading and writing." according to the documentation. These inserts are rarely called so I am ok opening and closing the DB each call. Look at my function prepareDatabase(...). It is called at the start of the function and will open a connection. – mhradek Mar 28 '11 at 21:24
prepareChildDatabase and prepareDatabase are the same? – Marcos Vasconcelos Mar 29 '11 at 13:20

score 0 · Answer 2 · answered Mar 28 '11 at 21:46

First of all....when you are working with a DB instance or a cursor instance or a Steatment instance (or whatever needs to be closed at the end) make the use of the following syntax;

    SQLiteOpenHelper db;
    try{
        db = myDB.open();//open the database here
        //do whatever you want with the DB instance
    }finally{
        if(db != null){
            db.close();
        }
    }

If an exception is thrown in the middle of the process you will close the DB.

Remove this final

final long id = insertStmt.executeInsert();

And to debug better, put a catch in that try/finally and you will see better what's going on.

was leaving the try/catch/finally best practice off until I was closer to implementing the DataHelper object. Since this issue isn't throwing it doesn't help me out. I am curious why I should be leaving off the final. That suggestion doesn't conform with Java best practices. It is incrementing fine in the output of the executeInsert(). — mhradek, Apr 06 '11 at 19:05

mhradek · Accepted Answer · 2012-01-30T23:16:05.393

It appears that the device I am testing on has problems with permissions and writing the database files. The issue is actually that the DB is initially created, rows inserted, and then it is locked. Subsequent calls corrupt it.

This is probably the result of rooting the device. Or perhaps because the packages UID was changed.

Android SQLiteStatement Insert; Subsequent calls fail

3 Answers3