ios - sqlite prepare statement

问题:

I am trying to read data from a sqlite database in an ios app. When I run the app, it is able to open the database but in the log file it shows the message - "Problem with the prepare statement". I don't know what is wrong with my prepare statement Here's my code -

-(NSString *)dataFilePath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:kFilename];
}

In the viewDidLoad I have -

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    myarray = [[NSMutableArray alloc]init];

    sqlite3 *database;
    if(sqlite3_open([[self dataFilePath]UTF8String], &database)!=SQLITE_OK){
        sqlite3_close(database);
       NSAssert(0, @"Failed to open database");
    }

    const char *createSQL = @"SELECT ID, TITLE FROM FIRST ORDER BY TITLE;"; //first is the table in the database
    sqlite3_stmt *sqlStmt;
    if(sqlite3_prepare_v2(database, [createSQL UTF8String], -1, &sqlStmt, nil)!=SQLITE_OK){
        NSLog(@"Problem with prepare statement"); //this is where the code gets stuck and I don't know why
    }else{

        while(sqlite3_step(sqlStmt)==SQLITE_ROW){
            NSInteger number = sqlite3_column_int(sqlStmt, 0);
            NSString *title = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStmt, 1)];
            [myarray addObject:title];
        }
        sqlite3_finalize(sqlStmt);
    }
    sqlite3_close(database);
}

回答1:

If your prepare statement fails, rather than just reporting "Problem with prepare statement", try retrieving the error message, e.g.,

NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));

This might give you a better indication of the problem.

A problem I've seen in the past is that the database might not be found (because it wasn't included in the bundle, typo in the name, etc.) but the standard sqlite3_open function will create it if it's not there, and thus the sqlite3_open will succeed, but the table in question won't be found in the blank, newly created database. Better than sqlite3_open would be:

sqlite3 *database;
if (sqlite3_open_v2([[self dataFilePath] UTF8String], &database, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK) {
    sqlite3_close(database); // not sure you need to close if the open failed
    NSAssert(0, @"Failed to open database");
}

That way you get a warning if the database is not found. But if you've done sqlite3_open already, you might have a blank database, so you might want to reset your simulator and/or remove the app from your device, before trying it with sqlite3_open_v2.

回答2:

Several things you can try.

1. Clean your app, Remove from simulator or device & try installing a fresh copy again and see if it works.

2. Open your DB in Terminal & try to run your sql statement at there. Check wether you are getting desired output.

回答3:

Try changing the nil to NULL. Also, try defining the SQL statement as a const char.

....

const char *sql = "SELECT ID, TITLE FROM FIRST ORDER BY TITLE";
sqlite3_stmt *sqlStmt;

if(sqlite3_prepare_v2(database, sql, -1, &sqlStmt, NULL)!=SQLITE_OK){

....