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;
}
精彩评论