Problem with inserting data into a table

Question

sqlite3 *database; sqlite3_stmt *statement; NSString *dPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"UserData.sqlite"]; const char *dbpath = [dPath UTF8String]; // NSLog(@"%@",dbpath); //UserArray = [[NSMutableArray alloc]init];

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

    NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO User (UserName,FullName,Email,PhoneNo) VALUES (\"%@\", \"%@\", \"%@\" ,\"%@\")",txtUserName.text,txtFullName.text ,txtEmail.text , txtPhoneNo.text];

    const char *insert_stmt = [insertSQL UTF8String];

    sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL);

    if(sqlite3_step(statement)==SQLITE_DONE) 
    {
        txtUserName.text=@"";
        txtFullName.text=@"";
        txtEmail.text=@"";
        txtPhoneNo.text=@"";
        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Add Record" message:@"Contact Added" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];    
        [alert show];
        [alert release];
        alert=nil;

    }
    else 
    {
        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"record" message:@"record not created" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];   
        [alert show];
        [alert release];
        alert=nil;
    }


    // Release the compiled statement from memory
    sqlite3_finalize(statement);

    sqlite3_close(database);
}

->> i am trying to insert data into table. the code execute properly but the record is not added into the table. so please help me or this problem..thank's for your response.

Answer 1

try executing commit, so setting the db to autocommit

// COMMIT
  const char *sql2 = "COMMIT TRANSACTION";
  sqlite3_stmt *commit_statement;
  if (sqlite3_prepare_v2(database, sql2, -1, &commit_statement, NULL) != SQLITE_OK) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }
  success = sqlite3_step(commit_statement);
  if (success != SQLITE_DONE) {
    NSAssert1(0, @"Error Message: '%s'.", sqlite3_errmsg(database));
  }
}

Answer 2

I am afraid why You are performing INSERT operation on db which is in your application bundle. Try using this sample code Which I am sure might be useful to you

-(void)checkAndCreateDB {
    NSString* databaseName = @"MasterDB.sqlite";
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
    BOOL success1;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:databasePath];
    if(success) return;
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
}

-(void)insertData{

    sqlite3 *database;
    sqlite3_stmt *statement; 
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO User (uId,userName) VALUES (\"%d\", \"%@\")",1,@"RAHUL"];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL);

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

        }
        else 
        {
            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"record" message:@"record not created" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];   
            [alert show];
            [alert release];
            alert=nil;
        }   
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

Here I have copied MasterDB.sqlite file in My Project resource folder . While you are running the application we normally copy the DB in application document folder and then perform any READ/WRITE operation. Its a bear minimum code performing an INSERT opertaion all you need to do is call CheckAndCreatreDB followed by insertData Function. The variable databasePath is declared in .h file for class containing these methods.

Hope this helps !!