Getting No Results from SQLite DB in iOS, but getting them using SQLite itself

Question

I'm checking and creating a database using the following function:

-(void) checkAndCreateDatabase
{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
    BOOL dbExists;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    dbExists = [fileManager fileExistsAtPath:_databasePath];

    // If the database already exists then return without doing anything
    if(dbExists)
    {
        //sqlite3_open([self.databasePath UTF8String], &_database);
        NSLog(@"DB DOES EXIST");
        return;
    }
    NSLog(@"DB DOES NOT YET EXIST");
    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:_databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:_databasePath error:nil];
}

The database name and path are properly set here, in the init function for my Search class:

- (id)init
{
    if ((self = [super init]))
    {
        _databaseName = DB_NAME;

        NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDir = [documentPaths objectAtIndex:0];
        _databasePath = [documentsDir stringByAppendingPathComponent:_databaseName];

        if (sqlite3_open([_databasePath UTF8String], &_database) != SQLITE_OK)
        {
            [[[UIAlertView alloc]initWithTitle:@"Missing"
                                       message:@"Database file not found"
                                      delegate:nil
                             cancelButtonTitle:@"OK"
                             otherButtonTitles:nil, nil]show];
        }
    }
    return self;
}

Then, I query the database using:

-(void)search:(NSString *)searchTerm
{
    const char *dbpath = [_databasePath UTF8String];
    sqlite3_stmt *statement;

    /* Begin Database Work */
    if(sqlite3_open(dbpath, &_database) == SQLITE_OK)
    {
        NSString *querySQL = @"SELECT * FROM types";
        const char *query_stmt = [querySQL UTF8String];
        if (sqlite3_prepare_v2(_database, query_stmt, -1, &statement, NULL))
        {
           if(sqlite3_step(statement) == SQLITE_ROW)
           {
               NSLog([[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text(statement,1)]);
           }
           else
           {
               NSLog(@"nope2: query = ");
               printf(query_stmt);
           }
        }
        else
        {
            NSLog(@"nope1");
        }


    }
}

I'm always getting "nope2" returned, which seems to imply that no results are returned. When I run the exact same query on the database in SQLite directly, however, I get the results I expect. This leads me to believe that I'm doing something wrong in the code. What looks wrong here?

Answer 1

A couple of thoughts:

1. A very common problem is that, at some point in the past, the copy failed and the sqlite3_open would therefore create a blank database (this is why I generally use sqlite3_open_v2 with the SQLITE_OPEN_READWRITE option, but not the SQLITE_OPEN_CREATE option, to prevent it from ever creating a blank database). Anyway, if that ever happened, from that point on, checks for the existence of the file would succeed, even though the database in the Documents path was a blank database. You can confirm this by opening the database in the Documents path on your Mac and inspecting it (if using the simulator you can navigate to the ~/Library/Application Support/iPhone Simulator, finding the app, and then navigating to its Documents folder; if a device, you can use the Xcode organizer to select the app and download the app's package, and then look at the version of the database there). Even easier, just remove the app from your device/simulator, reinstall the app, and that should fix it.

   But if you have questions, make sure to just examine the copy of the database in the Documents folder, and you should be able to quickly diagnose the problem.

2. Another issue is that you should replace:

   if (sqlite3_prepare_v2(_database, query_stmt, -1, &statement, NULL)) 
   {
       ...
   }
   else
   {
       NSLog(@"nope1");
   }
   

   with

   if (sqlite3_prepare_v2(_database, query_stmt, -1, &statement, NULL) == SQLITE_OK) 
   {
       ...
   }
   else
   {
       NSLog(@"%s: sqlite3_prepare_v2 error: %s", __FUNCTION__, sqlite3_errmsg(_database));
   }
   

   Note that (a) I'm checking for SQLITE_OK, which, because it's is 0, and your code effectively does the exact opposite as what you obviously intended; and (b) if there's an error, I'll get the error message from SQLite. The error messages after sqlite3_prepare_v2 are generally very illuminating.

3. You should also check the result of the copyItemAtPath call and make sure it's returning YES. If not, you should report an error. Thus, replace:

   [fileManager copyItemAtPath:databasePathFromApp toPath:_databasePath error:nil];
   

   with

   NSError *error = nil;
   if (![fileManager copyItemAtPath:databasePathFromApp toPath:_databasePath error:&error])
   {
       NSLog(@"%s: copyItemAtPathError: %@", __FUNCTION__, error);
   }
   

   In your original code, if you, for example, neglected to include the database in the bundle, the copy would fail, but you would not detect this failure, and, worse, because you're using sqlite3_open (instead of sqlite3_open_v2), you wouldn't see an error during the open process.