Getting no rows from sqlite3 in iOS app

The following code returns no rows, but when I run the query in sqlite3 in Terminal, I get the result I expect. So I guess there is some trivial mistake here:

- (void)initializeDa开发者_运维知识库tabaseWithFilter:(NSString *)filter
{
    NSMutableArray *fristArray = [[NSMutableArray alloc] init];
    self.frister = fristArray;
    [fristArray release];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Frister.sqlite"];
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK){
        // Get primary key for all entries with filter
        const char *sql = "SELECT fristKey FROM frister WHERE ? = 1";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
            NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        }
        sqlite3_bind_text(statement, 1, [filter UTF8String], -1, SQLITE_TRANSIENT);
        NSLog(@"%@",filter); // lets me see if the filter column name is passed to the method.
        int i = 0; // this is to see if I get some rows in return
        while (sqlite3_step(statement) == SQLITE_ROW) {
            i++;
            int primaryKey = sqlite3_column_int(statement,0);
            Frist *td = [[Frist alloc] inithWithOwnerID:primaryKey database:database];

            [frister addObject:td];
            [td release];
        }
        NSLog(@"%d",i); // If I get some results, I should get a number here...
        sqlite3_finalize(statement);
    } else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
}

I would really like some help here :)

---

I see you've already accepted an answer, but I think I see the problem in your sqlite call.

In the terminal, I guess you're doing a query something like this:

SELECT fristKey FROM frister WHERE column = 1

But your code is not doing an equivalent query. It is instead doing a query like this:

SELECT fristKey FROM frister WHERE 'column' = 1

Since the string "column" is never going to be equal to 1, your query is not going to give any results. You cannot bind a column name into a parameterized query, only a literal value.

---

(Adding this as an answer since it would be pretty cumbersome to put it as a comment)

I strongly suggest using FMDB to interact with the database instead. It's a wrapper around the SQLite C API that makes it very easy to interact with a database. Your code could be changed to use it with pretty minimal intervention. Here's what it would look like:

- (void)initializeDatabaseWithFilter:(NSString *)filter
{
    self.frister = [NSMutableArray array];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Frister.sqlite"];

    FMDatabase *db = [FMDatabase databaseWithPath:path];
    if ([db open]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT fristKey FROM frister WHERE %@ = 1", filter];
        FMResultSet *results = [db executeQuery:sql];
        while([results next]) {
            int primaryKey = [results intForColumn:@"fristKey"];
            Frist *td = [[Frist alloc] initWithOwnerID:primaryKey database:db];
            [[self frister] addObject:td];
            [td release];
        }
        [db close];
    }
}

Doesn't that look a TON more readable? It's pretty easy to see that you're executing a query, iterating through the results, and pulling the "fristKey" out in order to make a "Frist" object. You don't have to worry about sqlite3_open, preparing statements, binding parameters, or finalizing statements.

The other thing you'd have to change is the Frist object: it needs to accept a FMDatabase* as it's "database" instead of a sqlite3*. You may also consider just opening the database once and then leaving it open until you don't need it anymore (otherwise you'll have to -open it every time you want to execute something; this is entirely up to you).

To include FMDB in your app, download the code from Github, and then copy the FMDatabase, FMDatabaseAdditions, and FMResultSet files (all 6) into your app, and #import them where you'll need to interact with the database. You'll still need to link against sqlite3.dylib.

Just be sure you honor the (very permissive) license.