How do you keep the SQLite database open for the entire app session?

[borg359]

Hi everyone,
I'm trying to improve my SQLite performance in a mapping application which issues a large number of db queries as the user scrolls around the map. I've read several post which suggest keeping the database open for the entire application session, rather than opening and closing the db as needed, is one method to improve such queries. To this end, I've moved my sqlite3_open command into the applicationDidFinishLaunching call, but the problem is that my annotation queries are made in another view controller in which the database file has not been defined.

So my question is this: How do you pass along the database information that is generated in the sqlite3_open command to the rest of your application. Sqlite3 * database is not an NSObject, so it can't be passed from the rootviewcontroller to subsequent view controllers as a property of those views. Any suggestions on how this is done? Below is the relevant code showing what I'm trying to do now. That setup currently results in a EXEC_BAD_ACCESS error, presumably because database is undefined.

Anyway, I'm assuming it's something extremely easy that I'm missing so any tips would be greatly appreciated. Thanks!

Some Code:

myAppDelegate.h

#import "myAppDelegate.h"
#import "sqlite3.h"

- (void)applicationDidFinishLaunching:(UIApplication *)application { 

	// Create the database file name
	NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString * documentsDir = [paths objectAtIndex:0];
	NSString * file = [documentsDir stringByAppendingPathComponent:@"Markers.db"];	
	//	NSString * file = [documentsDir stringByAppendingPathComponent:@"Map2.db"];	
	
	NSLog(@"Loading database file %@", file);
	
        sqlite3 *database = NULL;
        if (sqlite3_open([file UTF8String], &database) == SQLITE_OK) {
		NSLog(@"Database Open!");
	}

mapViewController.m

#import "mapViewController.h"
#import "myAppDelegate.h"

// Defining the SQL callback functions
static int NameCallback(void *context, int count, char **values, char **columns)
{
    NSMutableArray *Name = (NSMutableArray *)context;
    int i;
	for (i=0; i < count; i++) {
        const char *nameCString = values[i];
//		NSLog(@"Saved Title = %s", nameCString);
		
		// Protecting against special characters
		if ([NSString stringWithUTF8String:nameCString] == nil) { 
			[Name addObject:@"Frak"];
		} else {
			[Name addObject:[NSString stringWithUTF8String:nameCString]];
		}
	}
    return SQLITE_OK;
}

@implementation mapViewController

- (void)viewDidLoad {

   //load from nib, blah blah...
   [self loadNamesFromDatabase];
}

- (void)loadNamesFromDatabase {

     sqlite3_exec(database,"select name from Markers", NameCallback, names, NULL);

}

}

-dan

[dljeffery]

Why do you think you can't pass the sqlite3 * to your view controllers? You can... you just can't "retain" it, but that's OK. So you just need to omit "retain" from your property declaration.

Although I would probably just make your sqlite3 *database variable an instance variable of your AppDelegate class (which you need to do anyway) and have the view controllers ask the app delegate for the database.