ios - how to save the data in sqlite3 in iphone

Question

i am trying to create the database using sqlite manager.But the values are not stored in database.If i click the save button the Alert Message will be displayed on like this "Data Insertion Failed".i am trying to rectify these problem.In this case i visit so many tutorials.But i cant rectify my problem.Yesterday onwards i am totally blocked to this issue.please give me any idea or suggestion how to save the data.Thanks for all to visit the question.T.C.

DataBase.m

// creation of DATABASE

-(BOOL)createDB

{

NSString *docsDir;

NSArray *dirPaths;


// Get the document directory


dirPaths=NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);


docsDir=dirPaths[0];


// Build the path to the database file


databasePath=[[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"Feedback.db"]];


BOOL isSuccess=YES;


NSFileManager *fileManager=[NSFileManager defaultManager];


if([fileManager fileExistsAtPath:databasePath]==0)


{


const char *dbpath=[databasePath UTF8String];


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


{


char *errMsg;


const char *sql_stmt= "create table if not exists Feeback details (Traineeid integer, Trainername text,Traineename text,Rating float)";


if(sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)!=SQLITE_OK)


{


isSuccess=NO;


 NSLog(@"Failed to create table");


}


sqlite3_close(database);


return isSuccess;


}


else


{


isSuccess=NO;


NSLog(@"Failed to open/Create database");


}


}

return isSuccess;

}

// save data in the Database

-(BOOL) saveData:(NSString *)Traineeid Trainername:(NSString *)Trainername Traineename:(NSString *)Traineename Rating:(NSString *)Rating;


{

const char *dbpath=[databasePath UTF8String];


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


{


NSString *insertSQL=[NSString stringWithFormat:@"insert into Feedbackdetails(Traineeid,Trainername,Traineename,Rating) values("%d","%@", "%@", "%@")",[Traineeid integerValue],Trainername,Traineename,Rating];


const char *insert_stmt=[insertSQL UTF8String];


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


if(sqlite3_step(statement)==SQLITE_DONE)


{

return YES;

}

else

{

return NO;

}

sqlite3_reset(statement);


}

return NO;

}


FeebBackForm.m

-(IBAction)saveData:(id)sender

{

BOOL success=NO;

NSString *alertString = @"Data Insertion failed";

if (Traineeid.text.length>0 &&Trainername.text.length>0 &&Traineename.text.length>0 &&Rating.text.length>0)

{

success=[[DBManager getSharedInstance]saveData:Traineeid.text Trainername:Trainername.text Traineename:Traineename.text Rating:Rating.text];

}

else

{

alertString = @"Enter all fields";

}

if (success == NO) 

{

UIAlertView *alert = [[UIAlertView alloc]initWithTitle:alertString message:nil
delegate:nil cancelButtonTitle:@"OK" otherButtonTitles:nil];


[alert show];


}

}

Answer 1

Your putting string quotes around your int params in your sql statement.

This may not be your only issue but you should bind params. It looks like you're putting quotes around integer values in the sql insert statement which are defined as integers in your table.

"create table if not exists Feeback details (Traineeid integer, Trainername text,Traineename text,Rating float)"

"insert into Feedbackdetails(Traineeid,Trainername,Traineename,Rating) values("%d","%@", "%@", "%@")"

Notice your double quotes around ints.

Also, log out the path to your database (even if running in simulator). Go to the sqlite cmd line and ensure the db exists and the empty table is there. This helps in troubleshooting.

Finally, take a look @ the fmdb sqlite wrapper - it helps using sqlite but it's code also shows good patterns for using sqlite raw if that's your preference.

Here's a similar function from one of my samples which shows how to bind params. You should also finalize what you prepare:

- (void)updateContact: (Contact*)contact error:(NSError**)error
{
    if (![self ensureDatabaseOpen:error])
    {
        return;
    }

    NSLog(@">> ContactManager::updateContact");

    // prep statement
    sqlite3_stmt    *statement;
    NSString *querySQL = @"update contacts set name=?,address=?,phone=? where id=?";
    NSLog(@"query: %@", querySQL);
    const char *query_stmt = [querySQL UTF8String];

    // preparing a query compiles the query so it can be re-used.
    sqlite3_prepare_v2(_contactDb, query_stmt, -1, &statement, NULL);     
    sqlite3_bind_text(statement, 1, [[contact name] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 2, [[contact address] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 3, [[contact phone] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_int64(statement, 4, [[contact id] longLongValue]);

    NSLog(@"bind name: %@", [contact name]);
    NSLog(@"bind address: %@", [contact address]);
    NSLog(@"bind phone: %@", [contact phone]);
    NSLog(@"bind int64: %qi", [[contact id] longLongValue]);

    // process result
    if (sqlite3_step(statement) != SQLITE_DONE)
    {
        NSLog(@"error: %@", sqlite3_errmsg(_contactDb));
    }

    sqlite3_finalize(statement);
}