NSThread-ing and SQLITE calls

Discussion in 'iOS Programming' started by mraheel, Jul 23, 2010.

  1. mraheel macrumors regular

    Apr 18, 2009
    HI guys,

    I've been struggling with answers to the right way of calling SQLite methods in the background...
    I've tried numerous and even found one that seems to work but i know that its NOT the right way to do it..

    Heres what I'm trying to accomplish:

    1-Normally, in simple methods, the UI freezes (maybe for milliseconds) when tapping the button that sends SQLite calls until it completes and fills up the UITableView. As it sends in "performselectorinMainThread" I'm looking for a way to make sure that the method is independent of such calls, and thats why i've been trying the performSelectorInBackgroundThread.. and NSThread detach..

    Technically, i've achieved this via NSThread detachNewThreadSelector

    2- In such a scenario when the UI is not frozen, that makes buttons free to touch and execute, How can i avoid any clashes with NSThread and the UI.. specifically UITableView.. Heres an example of the condition..
    Consider a UISearchBar function "TextDidChange"

    Normally, addition of a new Character yields results in tableView and until the results REACH tableView, the buttons of the keypad remain in the "Touched" state. In other words, the UI freezes..

    I tried doing this with [NSThread detachNewThreadSelector].. it works Ok if i type slowly, but if i type faster, the app crashes.

    This makes me believe that my Threading method is completely flawed..

    heres what I'm doing.

    This is in app delegate, Its a common method for both Search and Normal calls based on CategoryID.
    - (void) loadTitlesOfCategory:(NSInteger )category_ID containingString:(NSString *)searchString animated:(BOOL)animated{ 
    	[currentTitlesArray removeAllObjects];
    	if(nil == database){
    		//start database here	
    		[self initialiseDatabase];
    	sqlite3_stmt *query;									
    	BOOL doSearch = (nil != searchString) && ![searchString isEqualToString:@""];
    	if(category_ID == 0) return;
    	//UniversalSearch Function 
    		//NSLog(@"in search");
    		NSString *sql;		
    		sql = [NSString stringWithFormat:@"SELECT ID, TITLES from Ttable where TITLES LIKE ?"];
    		if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &load_search_titles_query, NULL) != SQLITE_OK) {
    			NSAssert1(0, @"Error: Failed to prepare load_titles_of_category_search_query: '%s'.", sqlite3_errmsg(database));
    		query = load_search_titles_query;
    		const char *search_chars = [[NSString stringWithFormat:@"%%%@%%", searchString] UTF8String];
    		sqlite3_bind_text(query, 1, search_chars, -1, SQLITE_TRANSIENT);
    		sqlite3_bind_text(query, 2, search_chars, -1, SQLITE_TRANSIENT);
    	else {
    		NSString *sql;
    		sql = [NSString stringWithFormat:@"SELECT ID, TITLES from Ttable where Category =?"];
    			if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &load_titles_query, NULL) != SQLITE_OK) {
    				NSAssert1(0, @"Error: Failed to prepare load_titles_query: '%s'.", sqlite3_errmsg(database));
    	//	}
    		query = load_titles_query;
    		sqlite3_bind_int(query, 1, category_ID);
    	NSMutableString *title = [NSMutableString string];
    		while(sqlite3_step(query) == SQLITE_ROW) {
    		int eid = sqlite3_column_int(query, 0);
    		char *titleTitle	= (char *)sqlite3_column_text(query, 1);
    		TitleObj *title = [[title alloc] initWithID:eid title:titleTitle delegate:self];
    		[currenttitlesArray addObject:title];
    		[title release];
    	[self.mainController cacheArray:currenttitlesArray];
    This is in the Main Controller that houses the tableView, accessed from AppDelegate.
    -(- (void) cacheArray:(NSArray *)CurentArray{
    self.currMainArray = CurentArray;
    	[self.tableView reloadData];
    Finally, the Culprit, heres the problem, this is How im calling this method after numerous tryouts, this seemed to work.
    - (void)meth2:(NSNumber *)number {
    	NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
    	CurrentCatID = [number integerValue];
    	[delegate loadTitlesOfCategory:CurrentCatID containingString:nil animated:YES];
    	[pool release];
    AND this is what happens when you TAP THE UIBUTTON:
    NSNumber *number = [NSNumber numberWithInteger:4];
    [NSThread detachNewThreadSelector:@selector(meth2:)  toTarget:self  withObject:number];
    This works just fine, the problem is when an avid user like myself (or someguy in the world), who types in too fast or is very impatient, there are times when the taps between buttons have very different corresponding SQL result rows that translates to very big difference between the tableView rows.
    And so i get the error "Index beyond bounds".

    Now, Either i should be able to CANCEL the previous NSThread call .. or override it somehow.. This question is not just limited to its usage in the search function. So i do need to know how to solve it..!

    Any right direction is helpful please..

    thanks for takin the time to read it, its as much pain as typing it.. sheesh!
  2. PhoneyDeveloper macrumors 68040


    Sep 2, 2008
    You can't call reloadData or in any way message the tableView from a background thread.

    Here's how I've done this:

    Use NSOperation and NSOperationQueue. They provide a good structure for doing threaded operations. You can easily cancel a running operation.

    In the operation it accesses the database and gets the results in chunks. I found that 300 rows per chunk was a good size, YMMV. After each chunk has been accumulated into an array that array is send to the main thread using performMethodOnMainThread. The main thread then adds the results from the array to its data model and calls reloadData. Any number of chunks will be sent to the main thread until the search is complete. If there's a reason to cancel the search then the main thread cancels the operation and the operation checks if it's been cancelled and then quits if so.
  3. mraheel thread starter macrumors regular

    Apr 18, 2009
    You've pointed me to the right direction. Been reading about it.. Most examples have created NSOperation objections. Is that necessary in my case??

    I'm getting into it more..

    Im thinking, create a property of NSOperation, use that single property to do all my relevant sql calls.

    The sqlite part itself remains within nsoperation..

    when it finishes, it notifies the main thread.

    Its in the background, so my requirements are met.. i think?

    About cancelling, the next call requires detecting THAT property (NSOp) and cancelling it and resetting it to new one?
  4. mraheel thread starter macrumors regular

    Apr 18, 2009
    Also, i've noticed many examples creating NSOperation Objects.

    In that case, i'd have to rewrite the whole sql proceedure.. into that object, redirect all calls to it from everywhere..

    Kinda tough work?
  5. mraheel thread starter macrumors regular

    Apr 18, 2009
    Okay, here are some changes..

    This is the Buttons method.. there are many such buttons that beg to be pushed.. (even together sum-times)
    NSNumber *number = [NSNumber numberWithInteger:nCat.CatID];
    NSOperationQueue *queue = [NSOperationQueue new];
    		NSInvocationOperation *operation = [[NSInvocationOperation alloc] initWithTarget:self
    		[queue addOperation:operation];
    		[operation release];
    Which adds this to the queue...
    -(void)nsop:(NSNumber *)number {
    	CurrentCatID = [number integerValue];
    	[delegate loadTitlesOfCategory:CurrentCatID containingString:nil animated:YES];

    The SQLite function method essentially remains the same..

    the place where I choose to reloadData is CacheArray:
    - (void) cacheArray:(NSArray *)CurentArray{
    	self.currMainArray = CurentArray;
    [self.tableView performSelectorOnMainThread:@selector(reloadData)  withObject:nil waitUntilDone:NO]; 
    Should the Wait until Done be Yess? NO??


    should i create a property of NSOperations?? and access it without releasing it?? there arent too many xamples from here on..

    thanks for the help
  6. PhoneyDeveloper macrumors 68040


    Sep 2, 2008
    Usually you want the operationQueue to be an ivar. You can then cancel any operations and also release the operationQueue. Wait until done should be NO.

    Make sure that your background code doesn't access any ivars that can also be changed from the main thread. It looks like you are modifying some arrays in the background thread that can also be read from the main thread. This is wrong.
  7. mraheel thread starter macrumors regular

    Apr 18, 2009
    The background thread is handling currentTitlesArray which is in app delegate.

    and in cacheArray method, new list of objects is added to CurrentMainArray which is in the tableViewController and thats the tableView data source not the currentTitlesArray in appdelegate.

    Hopefully i didnt miss anything??

    and using for searching, assuming i've created the ivar for nsoperationsQueue (which i did), whats the best way to cancel the operations after each character is tapped?

    eg, tapping A, yeilds Titles with A, furthur tapping S, yeilds results with "AS".
    Whats the best way to cancel the operation after tapping S may be searching for only "A".

    [queue cancelAllOperations]; at the start of searchBar's textDidChange??

    any way to particularly detect such a single operation sent from search?

    thanks for your help man.
  8. deviloper macrumors newbie

    Oct 18, 2010
    Hi mraheel,

    I have actually exact the same problem. First I have implemented a try/catch block to figure out the out of bounds exception. But that causes that the tableview shows one item multiple in the TableView. That occurs most on an iPhone 3G with quick typing. On a iPhone4 the problem occurs very rarely.
    I have already implemented NSOperationQueue.

    Do you have a complete solution example for me? Could you solve your problem with PhoneyDeveloper's solution?


Share This Page