how to get last updated value in sqlite3

Question

Hello Friends,

I have an application that will do the synchronization with the database to web-service. I have one table list in that I want to update, delete and insert the list. When the user click on the synchronization button I want to send the data which will be updated or changed in the database. The web-service are different for add, update or delete I want send only updated records.

For that what can I do for that any suggestion.

Thanks in advance, Hardik Patel

Answer 1

You can maintain an extra column for timestamp. So whenever a record is changed you should update the timestamp. (record_timestamp)

You have to maintain the time at which you have sent the data successfully to your server. (last_updated_timestamp)

So at the time of sync, you will send only those records to server whose record_timestamp > last_updated_timestamp

The last_updated_timestamp will be updated every time successful sync is performed.

I hope this helps.

This link will help you compare time stamps in sqlite Timestamp comparison

Answer 2

You use sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

- (NSNumber *)executeSQL:(NSString *)sql withCallback:(void *)callbackFunction context:(id)contextObject {
    NSString *path = [self pathToDB];
    sqlite3 *db = NULL;
    int rc = SQLITE_OK;
    NSInteger lastRowId = 0;
    rc = sqlite3_open([path UTF8String], &db);
    if(SQLITE_OK != rc) {
        NSLog(@"Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return nil;
    }else {
        char *zErrMsg = NULL;
        NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
        rc = sqlite3_exec(db, [sql UTF8String], callbackFunction, (void*)contextObject, &zErrMsg);
        if(SQLITE_OK != rc) {
            NSLog(@"Can't run query '%@' error message: %s\n", sql, sqlite3_errmsg(db));
            sqlite3_free(zErrMsg);
        }
        lastRowId = sqlite3_last_insert_rowid(db);
        sqlite3_close(db);
        [pool release];
    }
    NSNumber *lastInsertRowId = nil;
    if(0 != lastRowId) {
        lastInsertRowId = [NSNumber numberWithInteger:lastRowId];
    }
    return lastInsertRowId;
}