When to use SQLITE_TRANSIENT vs SQLITE_STATIC?

Question

I would like to create/update text columns in sqlite3. When i retrieve rows after the create/update, the text is '?'. Integer values are properly persisted however.

My text statements look like this:

const char *sql = "INSERT INTO todo(title, description, priority, status, created, expires, posx, posy, updated)"
                  " VALUES('?', '?', '?', '?', '?', '?', '?', '?', '?');";
if (sqlite3_prepare_v2(database, sql, -1, &insert_statment, NULL) != SQLITE_OK)
    ...
sqlite3_bind_text(update_statment, 5, [[dt stringFromDate:self.updated] UTF8String], -1, SQLITE_TRANSIENT);

I've tried SQLITE_TRANSIENT as well as SQLITE_STATIC. Both cases seem to yield same results ('?'). I have also verified that the text values are valid when they are passed into the appropriate sql statements here.

Any ideas?

Answer 1

Remove the ' characters around ? in your sql string.

SQLITE_TRANSIENT tells SQLite to copy your string. Use this when your string('s buffer) is going to go away before the query is executed.

SQLITE_STATIC tells SQLite that you promise that the pointer you pass to the string will be valid until after the query is executed. Use this when your buffer is, um, static, or at least has dynamic scope that exceeds that of the binding.