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:

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

Doug, here it is:

/// /// create a new row (INSERT INTO todo). ///
- (BOOL) createData:(sqlite3 *)db {     NSLog(@"TodoTask create data.");    if (self = [super init])  {   database = db;
       if (insert_statment == nil)    {    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)    {
       NSAssert1(0, @"Error: failed to prepare create_statement with message '%s'.", sqlite3_errmsg(database));    }   }
       sqlite3_bind_text(insert_statment, 1, [self.titleText UTF8String], -1, SQLITE_TRANSIENT);   sqlite3_bind_text(update_statment, 2, [self.description UTF8String], -1, SQLITE_TRANSIENT);   sqlite3_bind_int(update_statment, 3, self.priority);   sqlite3_bind_int(update_statment, 4, self.status);   sqlite3_bind_int(update_statment, 7, self.posx);   sqlite3_bind_int(update_statment, 8, self.posy);
        NSDateFormatter *dt = [[NSDateFormatter alloc] init];   sqlite3_bind_text(update_statment, 5, [[dt stringFromDate:self.created] UTF8String], -1, SQLITE_TRANSIENT);   sqlite3_bind_text(update_statment, 6, [[dt stringFromDate:self.expires] UTF8String], -1, SQLITE_TRANSIENT);   sqlite3_bind_text(update_statment, 9, [[dt stringFromDate:self.created] UTF8String], -1, SQLITE_TRANSIENT);   [dt release];

     int success = sqlite3_step(insert_statment);   sqlite3_reset(insert_statment);
        if (success != SQLITE_DONE)    {    NSAssert1(0, @"Error: failed to insert into with message '%s'.", sqlite3_errmsg(database));   }
        primaryKey = sqlite3_last_insert_rowid(database);  }    NSLog(@"... data created.");  return TRUE; }

Answer 2

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.

Answer 3

Wait, ok, now for the question. what the difference between transience and static? lol.