trying (&failing) inserting data in sqlite from iphone

Question

Hello I m developing an app which uses a sqlitedb. I read some examples and tried do create my app. the table I want to insert has 3 columns. ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, DURATION REAL, TIME REAL I write the below code based on examples I have read. but I always see failed to add a record. PS: I tested db creation , its ok. I used this tuterial it was okay on another application. but here doesnt work

        NSTimeInterval endTime = [[NSDate date] timeIntervalSinceReferenceDate];

    double elapsedTime = endTime-startcache;

    NSString *my=[NSString stringWithFormat:@"%f",elapsedTime];
    TextField1.text=my;

    // saving in database
   NSString * durationstring=[NSString stringWithFormat:@"%f",elapsedTime];
  NSString *   timestring=[NSString stringWithFormat:@"%f",endTime];

    sqlite3_stmt    *statement;
    const char *dbpath = [_databasePath UTF8String];

    if (sqlite3_open(dbpath, &_activityDB) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO ACTIVITYTABLE (name, duration,time ) VALUES (\"%@\", \"%@\", \"%@\")",
                               activityname, durationstring,timestring];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(_activityDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            self.status.text = @"record added";


        } else {
            self.status.text = @"Failed to add record";
        }
        sqlite3_finalize(statement);
        sqlite3_close(_activityDB);
    }

Answer 1

Its because you are directly writing data to database in resources folder.it is not writable first you need to copy the database in document directory.

NSError *err=nil;
    NSFileManager *fm=[NSFileManager defaultManager];

    NSArray *arrPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, -1);
    NSString *path=[arrPaths objectAtIndex:0];
   NSString path2= [path stringByAppendingPathComponent:@"ProductDatabase.sql"];


   if(![fm fileExistsAtPath:path2])
    {

      bool success=[fm copyItemAtPath:databasePath toPath:path2 error:&err];
        if(success)
            NSLog(@"file copied successfully");
       else
           NSLog(@"file not copied");

    }

and remove the bind statements from the code and change it to

if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, nil)== SQLITE_OK)
        {


        if(sqlite3_step(statement)==SQLITE_DONE) 
        {
            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Added" message:@"" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];    
            [alert show];

        }
        else 
        {
            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Not Added" message:@"An error has occured" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];   
            [alert show];
            alert=nil;
        }   
}

If you're really interested in learning how to use SQLite to manage data in an iPhone app, I would recommend you to complete the following tutorial, as it's a very good introduction:

http://www.icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/

The tutorial handles Selecting, Updating, Inserting and Deleting data in an iPhone app, using SQLite.