CODEWITHSUNDEEP
iphoneobjective-cdatabasesqlite
Jack Nutkins
Jack Nutkins

Reputation: 1555

Inserting Data into sqlite database on iPhone - not working

I'm new to using sqlite and the iPhone and I'm having some trouble adding a record to my database. I've used what I can online to try and solve this problem but I'm stuck in a rut.

I'm using this piece of code to attempt to add a record into the database in sqlite:

    -(void)insertData{

    sqlite3 *database;
    sqlite3_stmt *statement;
    NSString *databasePath = [[NSBundle mainBundle] pathForResource:@"ProductDatabase" ofType:@"sql"];
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {
        //nutrional values
        float caloriesFloat = [caloriesTextField.text floatValue];
        float saturatesFloat = [saturatesTextField.text floatValue];
        float fatFloat = [fatTextField.text floatValue];
        float fibreFloat = [fibreTextField.text floatValue];
        float sugarFloat = [sugarTextField.text floatValue];
        float saltFloat = [saltTextField.text floatValue];

        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO products (name,category,calories, saturates,fat,fibre,sugar,salt) VALUES ('%@',' %@','%.02f','%.02f','%.02f','%.02f','%.02f','%.02f',);",nameTextField.text, categoryLabel.text, caloriesFloat, saturatesFloat, fatFloat, fibreFloat, sugarFloat, saltFloat];
        const char *insert_stmt = [insertSQL UTF8String];
        if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, nil)== SQLITE_OK)
        {
            sqlite3_bind_text(statement, 1, [nameTextField.text UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [categoryLabel.text UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", caloriesFloat] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", saturatesFloat] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", fatFloat] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", fibreFloat] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", sugarFloat] UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 3, [[NSString stringWithFormat:@"%f", saltFloat] UTF8String], -1, NULL);
        }

        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;
        }   
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

The alert view "Product not added" always appears, can anyone explain why my record isn't being added?

Thanks,

Jack

Upvotes: 0

Views: 3802

Answers (2)

iosdev1111
iosdev1111

Reputation: 1112

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.

NSString *databasePath=[[NSBundle mainBundle]pathForResource:@"ProductDatabase" ofType:@"sql"];

Add the code written below after the above line

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;
        }   
}

Upvotes: 2

Swapnil Luktuke
Swapnil Luktuke

Reputation: 10475

Firstly, copy your db to documents. You cannot modify it in the main bundle.

Secondly, there are two ways to create and execute sql statements on sqlite3 database.

One way is to create the entire sql string using string operations (us %@, %d etc for adding string and numeric values) and execute the sql on the db.

The other way is to have a question mark (?) for each of your variables in the sql string and bind a variable to each of these ? using sqlite3_bind statements

Each approach has it's pros and cons but you can choose one you want. You are dong both in your code which is wrong

Also, you don't need to convert every numeric value to string if you want to bind it to the statement. you can use sqlite3_bind_int, sqlite3_bind_double etc.

Upvotes: 1

Related Questions