How to bind literal text to an SQLite query?

Question

I'm trying to use the SQLite C API in my iPhone app. I'm trying to query an SQLite database for the number of records that have been completed after a certain date. The database saves the completed date as text in

YYYY-MM-dd

format. For example the text

2009-04-10

might appear as a completed date.

When I query the database from the commandline my query works, but when run from the app, it doesn't. Here's what I'm doing:

From the commandline, I run this query:

sqlite> SELECT COUNT(*) FROM tasks WHERE completed > '2009-04-09'
...> go
1

As you can see, there is one record found as I expect.

In my app, I execute this code (written in Objective-C, obviously):

static sqlite3_stmt *count_tasks_statement = nil;
if(count_tasks_statement == nil) {
    const char *sql = "SELECT COUNT(*) FROM tasks WHERE completed > '?'";

    if (sqlite3_prepare_v2(database, sql, -1, &count_tasks_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }
}

NSString *today = @"2009-04-09";
sqlite3_bind_text(count_tasks_statement, 1, [today UTF8String], -1, SQLITE_TRANSIENT);

// Get the row count from the query
NSInteger taskCount = 0;
if(sqlite3_step(count_tasks_statement) == SQLITE_ROW) {

    // The second parameter indicates the column index into the result set.
    taskCount = sqlite3_column_int(count_tasks_statement, 0);
}

// Reset the statement for future reuse.
sqlite3_reset(count_tasks_statement);

When I use the debugger on this code, and examine the taskCount variable, it is set to 0, indicating that no records were found. (If I change the code to return primary keys for found rows, it still returns nothing.)

Since it works from the commandline, but doesn't in my code, I assume that I'm doing something wrong with either the quoting of the question mark in my SQL, or with the binding of the literal text date to the query. But, I've tried it lots of different ways with no luck. Help!

Answer 1

Don't put parameter placeholders inside quotes, even if the value is a string or date literal.

const char *sql = "SELECT COUNT(*) FROM tasks WHERE completed > ?";

Answer 2

I think that you do not need the extra quotes around the question mark.

Try

const char *sql = "SELECT COUNT(*) FROM tasks WHERE completed > ?";

and it should work.

Answer 3

:) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :)