How do I retrieve rows in an sqlite3 database using prepared statements using datetime('now', '-1 day')?

user_619818 0
Viewed 60

Given a table designed like this:

CREATE TABLE calls(timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, callerid TEXT, contactid INTEGER)

If I want to extract all rows where the timestamp is within 1 day I can use this sql:

SELECT * FROM calls WHERE timestamp >= datetime('now', '-1 day');

In code using the unsafe convenience functions I could write this like so:

sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("mydb.db", &db);
const char* sql = "SELECT * FROM calls WHERE timestamp >= datetime('now', '-1 day');";
rc = sqlite3_exec(db, sql, callback, 0, &err_msg);

I would have to setup the callback function, of course, to handle retrieving the records.

But if instead I wanted to perform the same SELECT statement using a prepared statement how would I do that?

I tried like this:

sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("mydb.db", &db);

sqlite3_stmt *stmt = NULL;
const char* sql = "SELECT rowid,* FROM calls WHERE timestamp >= :timestamp;";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

int idx = sqlite3_bind_parameter_index(stmt, ":timestamp");
rc = sqlite3_bind_text(stmt, idx, "datetime('now', '-1 day')", -1, SQLITE_STATIC); break;

// but this while loop exits immediately - ie no data returned
while ((rc = sqlite3_step(stmt)) != SQLITE_DONE) {
  // code here to extract field data
}

rc = sqlite3_finalize(stmt);

But sqlite3_step returns SQLITE_DONE immediately, ie it returns no data.

How should I setup the calls to the prepared statement functions to get this to work.

prepared-statement sqlite c
1 Answers

Use SQLite's concatenation operator || to concatenate the named parameter :days which will be a string like "-1" inside the sql statement:

const char* sql = "SELECT rowid, * FROM calls WHERE timestamp >= datetime('now', :days || ' day');";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
int idx = sqlite3_bind_parameter_index(stmt, ":days");
rc = sqlite3_bind_text(stmt, idx, "-1", -1, SQLITE_STATIC);

Or if you want to pass a string like "-1 day" with a named parameter :period:

const char* sql = "SELECT rowid, * FROM calls WHERE timestamp >= datetime('now', :period);";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
int idx = sqlite3_bind_parameter_index(stmt, ":period");
rc = sqlite3_bind_text(stmt, idx, "-1 day", -1, SQLITE_STATIC);
user_104988280
Related