CODEWITHSUNDEEP
androidsql
Daniel Guillamot
Daniel Guillamot

Reputation: 863

Is it ok to repeatedly intentionally fail a DB insert on duplicate row?

Is it okay to hit many "Duplicate row on insert" when inserting into the sql db or is there a better way?

I am downloading "comments" from my server and displaying them in a listview on my android phone..

Here is how I do it:

1 - Whenever I display the listview, I fetch that last 15 comments via xml from the server..

2 - I insert the comments into a local sql database on the phone

3 - I update the listview by using an adapter that loads the data from the local sql database.

Every 30 seconds I am repeating steps 1, 2 and 3.. I also repeat steps 1, 2, and 3 when the activity hits onResume...

Unless another android client has uploaded a new comment, all comments fetched from server are duplicates and inserting them into the db returns an SQLConstraintException because I don't allow duplicates of the same comment.

Is this okay? I am throwing this exception for 10-15 rows every 30 seconds.. Is there a better way that wont hurt performance? I don't see the point of checking if the row exists before insert, since that will surely be slower.. I could potentially pass a variable to the server when I query it, telling it what my newest comment is..

I'm sure many many people have reached this crossroads. Where should we go from here?

Thanks for the discusson..

Upvotes: 3

Views: 134

Answers (1)

Wade
Wade

Reputation: 44

If your server's db has a sequential, incremental key then yes, I would just query with an additional condition "...AND SeqIdField > [my last id]". If no records are returned then you just reset your timer and wait to query again.

Even if there's no sequential id but you can get records inserted after a particular date/time then just make sure that date/time is included in your results and pass the latest one back in as a parameter. If you don't want / need to store that date/time on your local device then just save the latest somewhere so you know where to pick back up when your app runs again.

Upvotes: 2

Related Questions