2

My app reads an XML file on the internet, takes note of the time and creates/writes an SQLite database. The next time data is required, if the time is >24hrs the database is updated (xml downloaded again).

The problem is that whenever I relaunch the app in AVD it has to re-download and so I notice that all the data in the database is written again (duplicated). So instead of 10 items, I have 20 (10+10 duplicates). If I relaunch again I get another 10 items duplicated.

I thought about how I could prevent the duplication of the database (or delete the old entries), so I decided to increment the database version every time the content is downloaded. I thought this would trigger the onUpgrade() method so the data would be cleared but nothing changes.

Now I am clueless. How should I go about this?

Improve this question
2
  • 1
    What about a DELETE FROM MyTable before you feed the database with the downloaded data? I can't figure out what the actual problem is. Version change is made for a database scheme upgrade (if you add new tables or columns). Commented Dec 15, 2011 at 23:09
  • well this is exactly what i wanted to do (and have done) so thanks Commented Dec 15, 2011 at 23:31

2 Answers 2

Reset to default
5

On your database create you'll want to use the UNIQUE constraint. You may not want the ON CONFLICT REPLACE that i use, but you should get the idea.

For Ex:

private static final String DATABASE_CREATE_NEWS= "create table news (_id integer primary key autoincrement, "title text not null, description text not null, date text not null, LastModified text not null, UNIQUE(title, date) ON CONFLICT REPLACE);";

Here is another solid thread that talks about it as well. SQLite table constraint - unique on multiple columns

Here is some more info on the android sqlite: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

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

1 Comment

thanks for the info, this is what i'll do later when i'm using a real table (not this test-case one).
3

You should create an index on the columns that represent a unique identifier.

see this article on SQLite's website.

CREATE INDEX ix_tblexample ON TableName ( Column1, Column2, Column3 [, Column4, etc..])

Or (as per your comment) you can select the table into a cursor and check for each one.

String sql = "select * from " + tableName + "where column1 = " + param1 + "and column2 = " + param2;
Cursor cur = _db.rawQuery( sql, new String[0] );

if(cur.getCount() == 0)
{
    //upload
}
Improve this answer

3 Comments

At the moment its a very simple table with 3 columns for testing purpose and the unique identifier is the _id field. Even if I were to create unique identifiers the duplicates would fail but they shouldn't be created in the first place - thats what i'm trying to avoid here
oh yes thats a nice way to find out if its empty or not, but this would prevent the updates from occuring (my database is updated every 24hours). I did what @Knickedi suggested which is to explicitly delete the database before updating and it doesnt duplicate now. Thanks anyway.
@user1031312 ahhh okay. I didn't know that you didn't care about retaining only specific data records. buut since you don't care then delete from [TableName] is the best way to go. Gotcha.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.