Advertise here




Advertise here

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Google Sign In with OpenID

SQLite Database:Deleting row & updating primary key(rowid)?

Xcode.warriorXcode.warrior Posts: 33Registered Users
edited June 2009 in iPhone SDK Development
Hi all,
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.

Thanks for reading through.
Post edited by Xcode.warrior on

Replies

  • Xcode.warriorXcode.warrior Posts: 33Registered Users
    edited June 2009
  • BSDimwitBSDimwit Posts: 172Registered Users
    edited June 2009
    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?.

    Have you tried something as simple as this...
    NSMutableArray *myPkArray = [[NSMutableArray alloc] init];
    
    sqlite3_stmt *statement;
    const char *sql = "SELECT ProfileID FROM your_table ORDER BY ProfileID";
    	
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
    	int pk = sqlite3_column_int(statement, 0);
    	[myPkArray addObject:[NSNumber numberWithInt:pk]];
    }
    		
    sqlite3_finalize(statement);
    
    // do something here with the array you just populated with primary keys to the rows in your table.
    
    [myPkArray release];
    
  • Xcode.warriorXcode.warrior Posts: 33Registered Users
    edited June 2009
    BSDimwit wrote: »
    Have you tried something as simple as this...
    NSMutableArray *myPkArray = [[NSMutableArray alloc] init];
    
    sqlite3_stmt *statement;
    const char *sql = "SELECT ProfileID FROM your_table ORDER BY ProfileID";
    	
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
    	int pk = sqlite3_column_int(statement, 0);
    	[myPkArray addObject:[NSNumber numberWithInt:pk]];
    }
    		
    sqlite3_finalize(statement);
    
    // do something here with the array you just populated with primary keys to the rows in your table.
    
    [myPkArray release];
    

    :o ahh! I'd just finished coding similar to this where I'm just querying for the next or previous 'ProfileID' from the database itself(the same way you did), before I initialize the object with the 'ProfileID'.
    Sometimes my brain starts thinking too hard for a*very simple solution :p

    Thanks BSDimwit for your helpful reply.
Sign In or Register to comment.