Inserting int value into sqlite using (FMDB with iOS) and retrieving it: not what we would be waiting for

Question

I am a little embarrassed since I spend my whole day on this trouble while it could seems really trivial for you,

As mentioned in the title, I am storing values in a sqlite via FMDB (these values are 1, 2 or 3 in this case).

When in another view, I am connecting to the SQLiteDB and retrieving my value, it display to me a strange number (usually in the order of 144963008) which does not correspond to the value I stored.

Using NSLog() I can see that the value stored are the value I want to store.

2014-01-06 12:14:16.677 GDV[11314:70b] the inserted value in column 'idValue' is: 1
2014-01-06 12:14:16.679 GDV[11314:70b] the inserted value in column 'idValue' is: 2
2014-01-06 12:14:16.682 GDV[11314:70b] the inserted value in column 'idValue' is: 3
2014-01-06 12:14:16.689 GDV[11314:70b] current user ID: 1
2014-01-06 12:14:16.690 GDV[11314:70b] idValue retrieve from sqlite: 144963008
2014-01-06 12:14:16.690 GDV[11314:70b] The value does not correpond.
2014-01-06 12:14:16.690 GDV[11314:70b] idValue retrieve from sqlite: 144985232
2014-01-06 12:14:16.690 GDV[11314:70b] The value does not correpond.
2014-01-06 12:14:16.690 GDV[11314:70b] idValue retrieve from sqlite: 144985664
2014-01-06 12:14:16.690 GDV[11314:70b] The value does not correspond.

The values obtained here should be respectively 1, 2 and 3.

The different method relative to the subject:

table created

    NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [docPaths objectAtIndex:0];
    NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

    if (![[NSFileManager defaultManager] fileExistsAtPath:dbPath]) {
        FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
        [database open];
        [database executeUpdate:@"CREATE TABLE userList (id INTEGER  PRIMARY KEY DEFAULT NULL, idValue INTEGER DEFAULT NULL,name TEXT DEFAULT NULL, gender TEXT DEFAULT NULL, age INTEGER DEFAULT NULL, weight INTEGER DEFAULT NULL, tall INTEGER DEFAULT NULL, qq TEXT DEFAULT NULL, email TEXT DEFAULT NULL, phone TEXT DEFAULT NULL, geneA INTEGER DEFAULT NULL, geneB INTEGER DEFAULT NULL, geneDr INTEGER DEFAULT NULL, geneA2 INTEGER DEFAULT NULL, geneB2 INTEGER DEFAULT NULL, geneDr2 INTEGER DEFAULT NULL, compare INTEGER DEFAULT NULL)"];
        [database close];

        //DB for current user
        [database open];
        [database executeUpdate:@"CREATE TABLE currentUser (id INTEGER  PRIMARY KEY DEFAULT NULL,name TEXT DEFAULT NULL, idValue INTEGER DEFAULT NULL)"];
        [database close];
        NSLog(@"DB created");
    } else{
        NSLog(@"DB exist");
    }
    NSLog(@"task finished");

Inserting value into my tables

        NSDictionary *tempDic = [dataArray objectAtIndex:i];
        NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDir = [docPaths objectAtIndex:0];
        NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

        FMDatabase *database = [FMDatabase databaseWithPath:dbPath];

        // Save or change current user
        [database open];
        [database executeUpdate:@"DELETE FROM currentUser"];
        [database close];

        [database open];
        [database executeUpdate:@"INSERT INTO currentUser (id, name, idValue) VALUES (?, ?, ?)", [NSNumber numberWithInt:0], [NSString stringWithFormat:@"%@", [txt_user_name text]], [NSNumber numberWithInt:[[txt_user_psw text] intValue]]];
        [database close];

        // Save all users
        [database open];
        [database executeUpdate:@"INSERT INTO userList (id, idValue, name, gender, age, weight, tall, qq, email, phone, geneA, geneB, geneDr, geneA2, geneB2, geneDr2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [NSNumber numberWithInt:(int)[tempDic objectForKey:@"id"]*200], [NSNumber numberWithInt:(int)[tempDic valueForKey:@"id"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"name"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"gender"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"age"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"weight"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"tall"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"qq"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"email"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"phone"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneA"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneB"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneDr"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneA2"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneB2"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneDr2"]], nil];
        NSLog(@"the inserted value in column 'idValue' is: %@" ,[tempDic objectForKey:@"id"]);
        [database close];

and finally retrieving my value in another view

    NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [docPaths objectAtIndex:0];
    NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    FMResultSet *results2 = [database executeQuery:@"SELECT * FROM userList"];
    while([results2 next]) {
        NSLog(@"idValue retrieve from sqlite: %d", [results2 intForColumn:@"idValue"]);
        if ([results2 intForColumn:@"idValue"] == myID) {
            NSLog(@"YES");
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneA"]]];
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneB"]]];
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneDr"]]];
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneA2"]]];
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneB2"]]];
            [valueOfCurrentUser addObject:[NSNumber numberWithInt:[results2 intForColumn:@"geneDr2"]]];
        } else{
            NSLog(@"The value does not correpond.");
        }
    }
    [database close];

(I sincerely know that my code is awfully written, I am quite new in programming and I planned to spend time to clean it at the end of my project.)

Thank you in advance for your time,

Sincerely

did you check your DB file, i think you are logging the correct value but inserting the wrong one — Retro, Jan 06 '14 at 08:37
Thank you for your answer, is there a method for checking our DB file? Or do you suggest me to Log the DBFile and not the value I am inserting into it? — sebastien FCT, Jan 06 '14 at 08:41
NSLog(@"%@",[NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) objectAtIndex:0] should give you the path to the directory. — Martol1ni, Jan 06 '14 at 08:53

score 0 · Accepted Answer · answered Jan 07 '14 at 00:55

Thank to your comments, I spend more time on it and it appear that in some cases, casting value as int won't work with sqlite, so the wrong part of my code was the following one:

// Save all users
        [database open];
        [database executeUpdate:@"INSERT INTO userList (id, idValue, name, gender, age, weight, tall, qq, email, phone, geneA, geneB, geneDr, geneA2, geneB2, geneDr2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [NSNumber numberWithInt:(int)[tempDic objectForKey:@"id"]*200], [NSNumber numberWithInt:(int)[tempDic valueForKey:@"id"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"name"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"gender"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"age"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"weight"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"tall"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"qq"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"email"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"phone"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneA"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneB"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneDr"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneA2"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneB2"]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"geneDr2"]], nil];
        NSLog(@"the inserted value in column 'idValue' is: %@" ,[tempDic objectForKey:@"id"]);
        [database close];

It appear that the good way to it is to call the method intValue:

// Save all users
        [database open];
        [database executeUpdate:@"INSERT INTO userList (id, idValue, name, gender, age, weight, tall, qq, email, phone, geneA, geneB, geneDr, geneA2, geneB2, geneDr2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [NSNumber numberWithInt:[[tempDic objectForKey:@"id"]intValue]*200], [NSNumber numberWithInt:[[tempDic valueForKey:@"id"] intValue]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"name"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"gender"]], [NSNumber numberWithInt:[[tempDic objectForKey:@"age"] intValue]], [NSNumber numberWithInt:(int)[tempDic objectForKey:@"weight"]], [NSNumber numberWithInt:[[tempDic objectForKey:@"tall"] intValue]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"qq"]], [NSString stringWithFormat:@"%@", [tempDic objectForKey:@"email"]], [NSNumber numberWithInt:[[tempDic objectForKey:@"phone"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneA"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneB"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneDr"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneA2"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneB2"] intValue]], [NSNumber numberWithInt:[[tempDic objectForKey:@"geneDr2"] intValue]], nil];
        NSLog(@"the inserted value in column 'idValue' is: %@" ,[tempDic objectForKey:@"id"]);
        [database close];

Thank for your help, I am learning everyday thank to stackOverflow community.

Inserting int value into sqlite using (FMDB with iOS) and retrieving it: not what we would be waiting for

1 Answers1