I hope everyone is in good health.
I need your help and guidance in one of the problems I'm facing in my application development.
In my application I'm utilizing SQLite database for data storage, which is the contact list details of the user, and which is synced with the remote server and updated every now and then. So whenever the server informs my application to delete a certain contact details, the application deletes the corresponding row of data from the corresponding table of database. Similarly it adds the new contact information as well, if server informs so. I've the table in database which stores these contact details, which is linked with a 'ProfileID' which is a PRIMARY KEY and AUTO INCREMENTS.
Now, everything works smooth like butter. But the glitch is that, for the display purpose I'm iterating over an integer variable which is used to query contact details from the database, treating the variable as a 'ProfileID'.
But when I do sync with the server and if server asks me, for example, to delete first 2 old contacts out of total 5 contacts and add 1 new contact, I've a problem when displaying the contact's information, since I'm querying the database using an integer variable, which is not in sync with the valid 'ProfileID' s now, because 2 old contacts were deleted and 1 new contact was added! So when the application queries with 'ProfileID' = 1, it doesn't have the corresponding contact details or row, since it was deleted now!
Has anyone else faced such problem before? what workaround did you use, if you've faced such an issue with the database?
I know that SQLite database's primary key doesn't update itself when there is a deletion of a row, and its a good practice, as well, for preventing the data corruption, but what is a solution, if we've to only query valid 'ROWID's from the database? Is there a way to get the array of rowids present in the table of database directly?
I, also, know that in VC++ there is something called 'DataSet' which makes it virtually easier for the developers to query such databases which are constantly updated, by populating an array which consists of the data set of only the valid 'ROW's of data from the database. And from this data set then it becomes easier to iterate over it, without worrying about the missing 'ROW's of data.
Any sorts of help or pointers in this regards would be appreciated.