Geek Answers Handbook

How to bind literal text with SQLite query?

I am trying to use the SQLite C API in my application for iPhone. I am trying to query the SQLite database for the number of records that were executed after a certain date. The database saves the completed date as text in the format

  YYYY-MM-dd
. For example, text
  2009-04-10
may display as completed date.

When I query the database from the command line, my query works, but when I start from the application it is not. That's what I'm doing:

From the command line, I run this query:

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

As you can see, there is one entry that I expect.

In my application, I execute this code (explicitly written in Objective-C):

 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 in this code and examine the taskCount variable, it is set to 0, indicating that no entries were found. (If I change the code to return the primary keys for the rows found, it still doesn't return anything.)

Since it works from the command line, but not in my code, I assume that I am doing something wrong with quoting a question mark in my SQL or binding the date of literal text to a query. But I tried many different ways, no luck. Help!

+4
source share
2 answers

Do not put parameter landlords inside quotation marks, even if this value is a string or date literal.

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

I think you do not need extra quotation marks around the question mark.

Try

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

and it should work.

+2
source

More articles:


All Articles