views:

682

answers:

2

Hi

I am trying to reuse a 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]];
}

sqlite3_reset(getsets_statement);

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 1st 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? Thanks.

A: 

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.

Matthew Flaschen
I thought a reset was enough. Anyways, that is what all the Apple examples use. But I will give it a shot. Thanks.
lostInTransit
Tried adding sqlite3_clear_bindings. It returns SQLITE_OK. But I am still getting the error.
lostInTransit
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
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
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
Jason, I thought about that. But sqlite should be making an internal copy immediately every time bind_text is called (because of SQLITE_TRANSIENT). And he said divname and cls remain valid.
Matthew Flaschen
Hmm... I'm still not convinced. It has to be a freed variable getting accessed. Maybe it's the getset_statement?
Jason Coco
I get a SQLITE_OK for every statement there is until I do the bind the second time. I think it might be what Marty is saying. BTW, what does the finalize statement do? And when should it be used?
lostInTransit
did you call finalize? that destroys your getsets_statement and is used when you don't want it anymore. that would definitely be your problem which is why i kept asking you about that... it is certainly NOT SQLITE_TRANSIENT. if you're only preparing your statement once, you must not call finalize until you're cleaning everything up and you won't use that statement again.
Jason Coco
That was it. I was calling finalize before the object was getting dealloced. Thanks Marty and Jason
lostInTransit
NP lost: that error EXC_BAD_ACCESS always means that you're dereferencing bad memory, so you have to look for objects/pointers that have been destroyed before their time ;)
Jason Coco
+1  A: 

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.

Ed Marty
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
Was calling finalize. That was causing the issue. Thanks.
lostInTransit