How to bind text value to sqlite3 database in iphone

Question

I am developing a app for a movie in which I need to fetch data from the database considering some constraints. It works perfectly on the first occasion, but when I try to fetch data 2nd time it throws a runtime exception( the app crashes).I have to bind 3 placeholders. 2 are text and 1 is integer type. Here's the code which I am using to fetch data from the database.

-(void) Data2
{
 databaseName = @"Cinema1.sqlite";

 NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
 NSString *documentsDir = [documentPaths objectAtIndex:0];
 databasePath =[documentsDir stringByAppendingPathComponent:databaseName];

 [self checkAndCreateDatabase];


 sqlite3 *database;
 if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
 {
  if(detailStmt == nil)
  {
   const char *sql = "Select PVR,Fame,Cinemax,Big from listing where UPPER(State) = UPPER(?) and UPPER(City) = UPPER(?) and ZIP = ?";   
   if(sqlite3_prepare_v2(database, sql, -1, &detailStmt, NULL) == SQLITE_OK) 
   {  
    NSLog(@"Hiiiiiii");
    sqlite3_bind_text(detailStmt, 1, [t1 UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(detailStmt, 2, [t2 UTF8String], -2, SQLITE_TRANSIENT);
    sqlite3_bind_int(detailStmt, 3, t3);
    if(SQLITE_DONE != sqlite3_step(detailStmt))
    {
     NSLog(@"Helllloooooo");
     NSString *pvr= [NSString stringWithUTF8String:(char *)sqlite3_column_text(detailStmt, 0)];
     NSString *fame= [NSString stringWithUTF8String:(char *)sqlite3_column_text(detailStmt, 1)];;
     NSString *cinemax = [NSString stringWithUTF8String:(char *)sqlite3_column_text(detailStmt, 2)];
     NSString *big= [NSString stringWithUTF8String:(char *)sqlite3_column_text(detailStmt, 3)];
     pvr1 = pvr;
     fame1 = fame;
     cinemax1 = cinemax;
     big1 = big;
     NSLog(@"PVR %@ Fame %@ Cinemax %@ Big %@",pvr1,fame1,cinemax1,big1);
    }
   }
   sqlite3_finalize(detailStmt);
  }
 }
 sqlite3_close(database);}

Can anyone help me with this.

Answer 1

In my limited Iphone experience when something runs once and then crashes on the next iteration, generally you are releasing memory you shouldnt be or not releasing memory you should be. Try looking at your memory allocations for problems.

Answer 2

You are using the sqlite3_prepare_v2() and sqlite3_finalize() wrong. You only prepare a statement once and then you can bind values to it as much as you'd like. When you're done you call sqlite3_reset(). When you're completely done with this statement and won't ever use it again (i.e. you quit the app), then you use finalize.

Your app crashes because you finalize the statement, but the pointer detailStmt still points to the position where your statement once was, and tries to access that region of the memory.

See also here: http://www.sqlite.org/c3ref/prepare.html

Answer 3

As Toby points out, the variables pvr, fame, cinemax, and big (and the reassigned pvr1, fame1, cinemax1, and big1) are autoreleased when returned from -stringWithUTF8String:, but you never retain them. Any access to these variables after this point will cause a crash.

You say that you are seeing a thrown exception. It might be helpful to know what that exception is, by examining the console output. Also, you can enable a breakpoint on objc_exception_throw in libobjc.A.dylib, then debug with breakpoints on, to find the exact line at which this exception occurs.