Android SQLite: Database empty in second activity

Question

I've searched all over the net for a solution to this so lets hope someone here can help.

My app has a start up task which populates a SQLite database before loading the main menu. A second activity that can be accessed from the main menu needs access to this. Therefore I close the database in the first activity in order to stop locking errors.

For some reason, the database seems to have no rows as soon as I close the connection, in both the same activity and the second activity.

Heres a code sample:

    SQLiteDatabase db = getWritableDatabase(); // get instance of current database 

    db.beginTransaction(); // set exclusive mode to speed up

    for(GulbArticle g : gulbArticles){
        this.insert(g);
    }

    db.setTransactionSuccessful();
    // counting here returns 315 rows using the all2() function below
    db.close();
    // counting here returns 0 rows using the all2() function below

Here is a function I made to get the count back

public void all2(){
      SQLiteDatabase db = getReadableDatabase();
      String sql = "SELECT COUNT(*) FROM "+TABLE_NAME;
      SQLiteStatement statement =db.compileStatement(sql);
        long count = statement.simpleQueryForLong();
        Log.v("ccount2",count+"");
}

So in both cases I'm initializing an instance of the database, but for some reason as soon as I close it once I can't reopen it/there seems to be nothing in the database. Maybe I'm missing something simple but this has really stumped me.

Ognyan · Accepted Answer · 2011-07-07 11:25:11Z

4

It seems you forgot to call db.endTransaction();, i.e. to commit the transaction. It should read:

db.setTransactionSuccessful();
db.endTransaction();
db.close();

Also it is good idea to surround it in try-catch-finaly like this:

try {
    db.beginTransaction();
    // do your DB manipulation
    db.setTransactionSuccessful();
} catch(...) { 
    ...
} finally {
    db.endTransaction();
}

db.close();

answered Jul 7, 2011 at 11:25

Ognyan

13.6k5 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

redroot Over a year ago

Sorted, I for some reason assumed db.setTransactionSuccessful() would close it but I guess that just toggles a boolean. Thanks

Barmaley · Accepted Answer · 2011-07-07 11:31:18Z

1

You don't need to close database when you start second activity. SQLite is normal DB management system - which means that these kind of situation should be resolved.

Resolving of this usually done by transactions mechanism - which you're tried to use in your code.

In your case I believe you have to use non-eclusive transactions - i.e. when record is locked in exclusive mode - record in unaccessible, so basic idiom should looks like:

db.beginTransactionNonExclusive();   
try 
{     
   // do smth     
   db.setTransactionSuccessful();   
} 
finally 
{     
   db.endTransaction();   
}

answered Jul 7, 2011 at 11:31

Barmaley

16.4k18 gold badges77 silver badges150 bronze badges

2 Comments

redroot Over a year ago

I don't seem to have access to that NonExclusive function but thanks for pointing that out I'll investigate a bit further

ZoltanF Over a year ago

Unfortunatly, the beginTransactionNonExclusive() is only avaialable in API level 11 (android 3.0) or newer...

Collectives™ on Stack Overflow

Android SQLite: Database empty in second activity

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related