Integer not stored in database (sqlite3)

Question

I have changed the code based on my understanding of the different answers I received on my previous post. The code is as follows:

float lastClicked = 0.0;

- (void)viewDidLoad
{
[super viewDidLoad];

NSString *contentDirectory;
NSArray *directoryPath;
const char *dbpath = [databasePath UTF8String];

directoryPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
contentDirectory = [directoryPath objectAtIndex:0];

databasePath = [[NSString alloc] initWithString: [contentDirectory stringByAppendingPathComponent: @"MCFRatingDatabase.db"]];

NSFileManager *filemanager = [NSFileManager defaultManager];
if ([filemanager fileExistsAtPath:databasePath] == NO){


    if (sqlite3_open(dbpath, &connectDB) == SQLITE_OK)
    {
        char *errorMessage;
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS IMAGERATING (CONTENTNAME STRING, RATING FLOAT)";
        if (sqlite3_exec(connectDB, sql_stmt, NULL, NULL, &errorMessage) != SQLITE_OK)
        {
            NSLog(@"FAILED TO CREAT TABLE");
        }
        sqlite3_close(connectDB);

    } else {
        NSLog(@"FAILED TO OPEN/CREATE DATABASE");
    }
}
[filemanager release];

sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &connectDB) == SQLITE_OK)
{
    NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM IMAGERATING WHERE CONTENTNAME = '%@'", titleString];
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(connectDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            display = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            rate = sqlite3_column_double(statement, 2);
            NSLog(@"contents are name = %@ and rating = %f", display, rate);                
        }else{

        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(connectDB);
}    
}


-(IBAction)rateSubmitClick:(id)sender{

sqlite3_stmt *statement;
NSString *insertSQL;

const char *dbpath = [databasePath UTF8String];

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

    if(isHalfClicked){
        rating = lastClicked + 0.5;
        insertSQL = [NSString stringWithFormat: @"INSERT INTO IMAGERATING (CONTENTNAME, RATING) VALUES (\"%@\", %f)", titleString, rating];
    }else{

        rating = lastClicked;
        insertSQL = [NSString stringWithFormat: @"INSERT INTO IMAGERATING (CONTENTNAME, RATING) VALUES (\"%@\", %f)", titleString, rating];
    }

    const char *insert_stmt = [insertSQL UTF8String];

    sqlite3_prepare_v2(connectDB, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE){

        NSLog(@"Added records are = %@  %f", titleString, rating);
        isRated=YES;
    }else{
        NSLog(@"FAILED TO ADD RECORD");
    }
    sqlite3_finalize(statement);
    sqlite3_close(connectDB);
}   
}

But now the problem is nothing is getting inserted. Log shows "FAILED TO ADD RECORD".

I am really messed up with this database stuff and need your help.

Maybe you should shorten the question by only posting relevant code. — Mundi
– Mundi, Commented Jun 7, 2012 at 12:03

wadesworld · Accepted Answer · 2012-06-07 13:13:53Z

1

You are wrapping your integer/float values in the insert statement with quotes. Don't do that.

Your SQL stement should look like:

INSERT INTO USERRATING (CONTENTNAME, RATING) VALUES ("mycontentname", 4.5);

as an example.

Or put another way, your code should be:

 if(isHalfClicked)
    {
        rating=lastClicked+0.5;
        insertSQL = [NSString stringWithFormat: @"INSERT INTO USERRATING (CONTENTNAME, RATING) VALUES (\"%@\", %f)",titleString,rating];
    }
    else {

        insertSQL = [NSString stringWithFormat: @"INSERT INTO USERRATING (CONTENTNAME, RATING) VALUES (\"%@\", %d)",titleString,lastClicked ];
          }

answered Jun 7, 2012 at 13:13

wadesworld

13.8k15 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dipanjan Dutta Over a year ago

I did as you said but still have the same issue

wadesworld Over a year ago

Sorry, I missed the fact that you're trying to store a float in an int field. Don't do that. Change rating to a REAL type. Additionally, it would be good practice to put NOT NULL constraints on each column when you do your table creation.

Mundi · Accepted Answer · 2012-06-07 13:34:11Z

1

In your insert statement you are linking lastClicked to the rating. You should link the rating to it instead. Looks like a typo.

Also, you create a field of type integer when you create the table, it should be float if you are also storing other values.

Edit:

From your log statements it is clear that the second time you are not adding the record to the database. The string RECORD ADDED is not being logged - the log line must come from somewhere else.

edited Jun 7, 2012 at 13:34

answered Jun 7, 2012 at 12:06

Mundi

80.3k17 gold badges119 silver badges142 bronze badges

4 Comments

Dipanjan Dutta Over a year ago

lastClicked is used to store integer rating (1, 2, 3, 4 or 5) whereas rating stores float value (1.5, 2.5 and so on)

Mundi Over a year ago

I still think it is your insert statement. Try removing the quotes. Also, see my edit

Dipanjan Dutta Over a year ago

Even integer values are not stored although field type is integer

Mundi Over a year ago

See my edit. I think you have a problem in your program flow logic and are not inserting the record into the database.

Collectives™ on Stack Overflow

Integer not stored in database (sqlite3)

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related