Take the 2-minute tour ×

I am trying to reuse an sqlite statement in my application in a method. Here's the relevant code

if(getsets_statement == nil){
    const char *sql = "SELECT DISTINCT num,roll FROM mytable WHERE cls like ? and divname like ?";
    if(sqlite3_prepare_v2(database, sql, -1, &getsets_statement, NULL) != SQLITE_OK){
        NSAssert1(0, @"Error: Failed to prepare stmt with message '%s'", sqlite3_errmsg(database));

sqlite3_bind_text(getsets_statement, 1, [cls UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(getsets_statement, 2, [divname UTF8String], -1, SQLITE_TRANSIENT);

while(sqlite3_step(getsets_statement) == SQLITE_ROW){

    setNumber= sqlite3_column_int(getsets_statement, 0);        

    roll = sqlite3_column_int(getsets_statement, 1);

    [numArr addObject:[NSNumber numberWithInt:setNumber]];
    [rollArr addObject:[NSNumber numberWithInt:roll]];


The statement executes perfectly the first time it is called. But the next time I call this method, I get a sqlite3error. The values of divname and cls are present (did an NSLog and checked) but I don't understand why I am getting this error. I get the error at the first bind_text statement.

This is in the gdb console

Program received signal:  “EXC_BAD_ACCESS”.
(gdb) where
#0  0x9041857f in sqlite3Error ()
#1  0x9041acea in vdbeUnbind ()
#2  0x9041b2c8 in bindText ()

Any help?

share|improve this question
Are you sure that cls and divname are valid objects? –  Jason Coco Apr 28 '09 at 13:07
add comment

2 Answers

up vote 2 down vote accepted

Make sure nothing else is actually the issue (is the database being closed somewhere else? Is the getsets_statement pointing to the same object? Has it been finalized?). Then try using NULL or SQLITE___STATIC instead of SQLITE_TRANSIENT. Since [NSString UTF8String] returns data that doesn't need to be freed (by you), it obviously doesn't need a destructor, and it will stay valid until after your function exits, at which time you're done running the statement anyway.

share|improve this answer
The docs say, "If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns." I would think either should be fine here, but SQLITE_TRANSIENT is the surefire-bet (albeit at a performance cost), because a copy is made immediately. –  Matthew Flaschen Apr 28 '09 at 13:59
Was calling finalize. That was causing the issue. Thanks. –  lostInTransit Apr 28 '09 at 14:03
add comment

I believe you need to call sqlite3_reset and sqlite3_clear_bindings on the prepared statement before attempting to reuse it. Also, you should really check your return codes.

share|improve this answer
I thought a reset was enough. Anyways, that is what all the Apple examples use. But I will give it a shot. Thanks. –  lostInTransit Apr 28 '09 at 13:01
Tried adding sqlite3_clear_bindings. It returns SQLITE_OK. But I am still getting the error. –  lostInTransit Apr 28 '09 at 13:05
in order to reset bindings to null, you do have to call clear_bindings but in this case it's irrelevant because the statement has only 2 parameters and both are being bound on each invocation. –  Jason Coco Apr 28 '09 at 13:08
What exactly returns SQLITE_OK? As I said, you should check /every/ return value, and tell us the first one that fails to return SQLITE_OK or crashes. –  Matthew Flaschen Apr 28 '09 at 13:14
i think the strings in the bind_text may be bad? There really just isn't enough information here, lost. As Matthew says, post more context. –  Jason Coco Apr 28 '09 at 13:20
show 6 more comments

Your Answer


