objective c - SQLite will not prepare query when accessing database in Xcode

Question

I'm a beginner iPhone developer trying to take information out of a sqlite database in Xcode 4.3. I have my database (which is named DB_Info.sqlite) in the same directory as my .h and .m files, and I also dragged the database into the folders section on the left bar in Xcode.

Could you please take a quick look at my code and let me know where my mistake is? I have used NSLogs to identify where the problem occurs, at the very last if statement, and it's written in comments. Thank you so much in advance!

#import <sqlite3.h>
@implementation Player
{
    sqlite3 *DB_Info;
    NSString *databasePath;
    NSString *docsDir;
    NSArray *dirPaths;
}

-(Player*)createPlayer:(NSString*)playerName
{        

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"DB_Info.sqlite"]];
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt *statement;

    if (sqlite3_open(dbpath, &DB_Info) == SQLITE_OK) { //works fine

        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM playerlist WHERE fullName="%@"", playerName];

        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(DB_Info, query_stmt, -1, &statement, NULL) == SQLITE_OK) { //PROBLEM: This is where the problem is, and the if statement never goes through
        //....rest of code here
        } else {
        NSLog(@"Error");
        }
}

Answer 1

First, rather than just saying "Error", log the SQL error message

NSLog(@"%s SQL error '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));` 

It will tell you precisely what's going wrong. A common error on people's first time SQL projects is that the table is not found. If so, read on. (If not, feel free to ignore the rest of this.)

Second, you're looking for your database in your Documents folder. Did you explicitly copy it from your bundle to your Documents folder at some point? Or did you create it programmatically? But if you prepared it in advance, it won't be in the Documents folder until you copy it there.

Third, I'd also suggest that you consider using sqlite3_open_v2 instead of sqlite3_open. You are checking to see if that's SQLITE_OK, but that may be giving a false sense of security. The sqlite3_open will create a database if it's not there, which is clearly not your intent. Your app should presumably being copying the db from the bundle or creating the database and the tables for you before you get to this method (and you're not showing that, so I'm not sure if you're doing that). Anyway, the sqlite3_open_v2 function will not create the database if it's not there unless you explicitly request it does so by including SQLITE_OPEN_CREATE. So, something like the following will not create the database if it's not found:

if (sqlite3_open_v2(dbpath, &DB_Info, SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK) {

On the off chance that a blank database has been created for you, I'd suggest you reset your simulator via "Reset Content and Settings" on the simulator's menu, or explicitly delete the app, so any blank databases that might have been created can be removed. If you're running this on a device, delete the app and reinstall it.

Fourth, if the database has been created in advance, have you confirmed that the database has been included in the "Copy Bundle Resources" setting? E.g.:

Fifth, if you're running the app on the simulator, you can always browse the simulator's file structure and make sure your files are where you think they are. You can also run the Mac OS sqlite program to inspect the database that the simulator is using to make sure everything is ok. You can even test your SQL right in the db that the simulator uses, which can be useful for diagnostic purposes. (Or, if you don't like the Mac text based sqlite program, you can also buy/download graphical tools, such as Base.) Before you can do this, you might first want to configure your Mac so you can easily browse the Simulator's files requires that you fire up the Terminal program, and issue the command:

chflags nohidden ~/Library

Then you can browse to "~/Library/Application Support/iPhone Simulator/5.1/Applications/" and then browse the various apps that you have and make sure you db file is there.