Accessing an SQLite DB for two separate queries on iPhone App Initialization

Question

I was successfully accessing my database to get a list of cities on the App launch. I tried running a second query against it right afterward to get the list of States but all that happens is that my app blows up with no usable error in the console (simply says "Program received signal: EXEC_BAD_ACCESS" and nothing more).

Here is the code, I was hoping someone could potentially explain to me what I'm doing wrong:

-(void) initializeDatabase{
// The database is stored in the application bundle
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mydatabase.sqlite"];

// Open the database.  The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK){
 [self initializeCities:database];
 [self initializeStates:database];
} else {
 // Even though the open failed, call close to properly clean up resources.
 sqlite3_close(database);
 NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
 // Additional error handling, as appropriate...
}
    }

-(void) initializeCities:(sqlite3 *)db {
NSMutableArray *cityArray = [[NSMutableArray alloc] init];
self.cities = cityArray;
[cityArray release];

// Get the primary key for all cities.
const char *sql = "SELECT id FROM my_table ORDER BY state";
sqlite3_stmt *statement;

if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) == SQLITE_OK){
 while (sqlite3_step(statement) == SQLITE_ROW){
  int primaryKey = sqlite3_column_int(statement, 0);
  City *city = [[City alloc] initWithPrimaryKey:primaryKey database:db];
  [cities addObject:city];
  [city release];
 }
}

// "Finalize" the statement - releases the resources associated with the statement.
sqlite3_finalize(statement);
}

-(void) initializeStates:(sqlite3 *)db {
NSMutableArray *statesArray = [[NSMutableArray alloc] init];
self.states = statesArray;
[statesArray release];

// Get the primary key for all cities.
const char *sql = "SELECT DISTINCT state FROM my_table ORDER BY state";
sqlite3_stmt *statement;

if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) == SQLITE_OK){
 // We "step" through the results - once for each row
 while (sqlite3_step(statement) == SQLITE_ROW){
  NSString *state;
  state = (NSString *)sqlite3_column_text(statement, 0);

  [states addObject:state];
  [state release];
 }
}

// "Finalize" the statement - releases the resources associated with the statement.
sqlite3_finalize(statement);
}

I can't debug this code as the debugger never hits my breakpoints at all. If I remove the initializeStates method the app works as expected (albiet without a list of states).

Answer 1

Your problem is this:

NSString *state = (NSString *)sqlite3_column_text(statement, 0);

According to the documentation, sqlite3_column_text() returns a char*, not an NSString*.

Edit: You wouldn't have had this problem if you'd have used a wrapper ;)

Answer 2

You are releasing "state" without having allocated it. Try something like this:

while (sqlite3_step(statement) == SQLITE_ROW){
    NSString *state = [[NSString alloc] initWithCString:(char*)sqlite3_column_text(statement, 0) encoding:NSASCIIStringEncoding];
    //state = (NSString *)sqlite3_column_text(statement, 0);

    [states addObject:state];
    [state release];
}

Update: add cast above to fix compiler warning