开发者

Query SQLite array after update

I have a tab bar application with 2 UITableViews. In Tab 1 I have a UITableView populated with an array of Todo objects from an Sqlite database. Each Todo object has a status which can be updated by a button in it's detail view; 'complete' or 'in progress' - this then updates the SQlite database with a '1' if a Todo object has been marked as 'complete' or a '0' if it is marked as 'in progress'. This works fine I think (although it doesn't persist after quitting the application).

However, in Tab 2 I have another UITableView which I want to show only the items that have been marked as 'complete'.

Would this require another seperate Sqlite call (select * from todo where complete='1'), and then a seperate array of TodoFav objects to populate the table in Tab 2? If so how would Tab 2 know that Tab 1 has been updated and saved? — I think currently it only saves on exit (although again this doesn't seem to persist after a quit).

Any help would be greatly appreciated, I have been a lurker for a while now, and have searched long and hard for the answer to no avail! Many thanks.

Edit:

Dehydrate code to save update on exit:

- (void) dehydrate {
if (dirty) { // If the todo is “dirty” meaning the dirty property was set to YES, we will need   to save the new data to the database.
if (dehydrate_statment == nil) {
    const char *sql = "UPDATE todo SET priority = ?,complete =? WHERE pk=?";
    if (sqlite3_prepare_v2(database, sql, -1, &dehydrate_statment, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }
}
// Bind our variables to each of the “?”‘s.  Notice the order.  
// The numbers represent the question marks from left to right (1 being the first, 2 being the second, 3 being the third)
sqlite3_bind_int(dehydrate_statment, 3, self.primaryKey);
sqlite3_bind_int(dehydrate_statment, 2, self.status);
sqlite3_bind_int(dehydrate_statment, 1, self.priority);
int success = sqlite3_step(dehydrate_statment);

if (success != SQLITE_DONE) {
    NSAssert1(0, @"Error: failed to save priority with message '%s'.", sqlite3_errmsg(database));
}
sqlite3_reset(dehydrate_statment);
dirty = NO;
}       
}

And code for button to change to complete:

- (void)updateStatus:(NSInteger)newStatus {
self.status = newStatus;
dirty = YES;     // Signifies that the todo has been altered and will need to be saved to the database.
}

Trying to query the database a second time for 'completed' (status='1') items:

- (NSMutableArray *)todosComplete {

NSMutableArray *todoCompleteArray = [[NSMutableArray alloc] init];
self.todosComplete = todoCompleteArray;
[todoCompleteArray release];

NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease];
NSString *query = @"select * from todo where status='1'";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
    while (sqlite3_step(statement) == SQLITE_ROW) {
        int primaryKey = sqlite3_column_int(statement, 0);
        TodoComplete *tdC = [[TodoComplete alloc] initWithPrimaryKey:primaryKey database:database];
        [todosComplete addObject:tdC];
        [tdC release];
    }
    sqlite3_finalize(statement);
}
return retval;

}

One last thing, initialization in app delegate, seems to work fine:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOpti开发者_开发问答ons:(NSDictionary *)launchOptions {    

// Override point for customization after application launch.

[self createEditableCopyOfDatabaseIfNeeded];
[self initializeDatabase];
//[self initializeDatabaseFiltered];

// Add the navigation controller's view to the window and display.
[self.window addSubview:navigationController.view];
[self.window addSubview:tabController.view];
[self.window makeKeyAndVisible];

return YES;

}

Thanks again!!! Sorry for the long post, really appreciate any help. I know I'm missing something obvious - dehydrate needs to be instant for a start? And I'm probably not querying the db correctly for the filtered items or something!?


I'm not an expert in sqlite, but the way you're saving seems overly convoluted. I would think you could do something like the following when the save button is pushed (this is untested, my require a little syntax fixing, and I have 'buttonState' in there to indicate whether it's 0 or 1 before the button push, so adjust that accordingly...also this has the assumption that the row==the primary key, so that'll probably need an adjustment). I'm also not sure whether an open database connection lasts between methods (not saying it doesn't, just that I don't know) so when you go to recall the completed results, may need to open a DB connection [and you'd have closed it in the last method that opened it]:

-(void)didPushButton{
    //something to identify which indexPath.row held the button that was selected
    sqlite3 *pref = [self getNewDBConnection];
    sqlite3_stmt *progStatement = nil;
    NSString *setProg = [NSString stringWithFormat:@"UPDATE todo SET complete=%d WHERE pk=%d",-buttonState+1,indexPath.row];
    char * errorMsg2;
    if(sqlite3_exec(pref,[setProg UTF8String],NULL,NULL,&errorMsg2)!=SQLITE_OK)
    {NSLog(@"error setting completion");} 
    else {NSLog(@"set status for pk:%d",indexPath.row);}
    sqlite3_finalize(progStatement);
    sqlite3_close(pref);
    buttonState=-buttonState+1;
    }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜