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?

(id)init? Copying a database file? ... Well, since rar is known to delete topics without the simplest form of appreciation, I have no advice to give. — El Tomato
– El Tomato, Commented Jun 17, 2013 at 4:27
Hello again, I explained that on another post. Is there something I should be doing besides copying the database file? — muttley91
– muttley91, Commented Jun 17, 2013 at 4:29
If sqlite3_prepare_v2 succeeds, it returns SQLITE_OK (which is 0). If it fails, it returns a non-zero value. So your check for the success of the prepare statement is backwards. You're also not looking at the error message returned by sqlite3_errmsg, which often will give you a very useful error message. — Rob
– Rob, Commented Jun 17, 2013 at 4:51

Rob · Accepted Answer · 2013-06-17 05:37:06Z

3

A couple of thoughts:

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.
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.
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.

edited Jun 17, 2013 at 5:37

answered Jun 17, 2013 at 4:42

Rob

441k74 gold badges850 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

muttley91 Over a year ago

Thanks very much for the thorough answer! I have made the changes you recommend. However, for some reason, I'm still getting an empty DB (verified as you recommended). No error is being reported on copy, despite the changes, and I'm getting "nope1" now, which means the prepare is failing. if (sqlite3_prepare_v2(_database, query_stmt, -1, &statement, NULL) == SQLITE_OK) is not true, so "nope1" is printed.

Rob Over a year ago

@rar Did you delete the app (or completely reset the simulator) before running it again? Just reinstalling the app isn't enough. You actually have to delete the old app and reinstall it fresh.

muttley91 Over a year ago

That did it. I then had to make sure the checkAndCreate function was called. It looks like it's working now. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related