iphone FMDB / SQLite Update Query doesn't work

Question

I try to do an update in a table using sql wrapper (FMDB). Insert method is oky and when I try to update data in the table problems occur.

- (void)saveUserMoneyValidation:(UserDebtInfo *)entities
{
    NSLog(@"entities.client_index = %d", entities.client_index);

    [self deleteUserMoneyValidation:entities.client_index];

    FMDatabase *newdb = [FMDatabase databaseWithPath:databasePathStore];

    [newdb setLogsErrors:TRUE];
    [newdb setTraceExecution:FALSE];

    if (![newdb open])
    { 
        NSLog(@"!!! Could not open db."); 
        return; 
    }
    else 
    { 
        NSLog(@"DB Open successfuly."); 
    }

    [newdb beginTransaction];
    NSLog(@"entities = %@",entities);

    [newdb executeUpdate:@"INSERT INTO MoneyValidation (client_index, client_name, sum, duration, monthly_payment, payment_type, debt_type, debt_code, categoryType) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
    [NSNumber numberWithInt:entities.client_index],
     entities.client_name,
     entities.sum,
     entities.duration,
     entities.monthly_payment,
     entities.payment_type,
     entities.debt_type,
     [NSNumber numberWithInt:entities.debt_code],
     entities.categoryType
     ];

    [newdb commit];
    [newdb close];
}

Update Method :

   -(void)updateUserMoneyValidation:(UserDebtInfo *)entities{
NSLog(@"entities.client_index = %d", entities.client_index);

FMDatabase *newdb = [FMDatabase databaseWithPath:databasePathStore];

[newdb setLogsErrors:TRUE];
[newdb setTraceExecution:FALSE];

if (![newdb open])
{ 
    NSLog(@"!!! Could not open db."); 
    return; 
}
else 
{ 
    NSLog(@"DB Open successfuly."); 
}

BOOL success = NO;

[newdb beginTransaction];
NSLog(@"entities = %@",entities);

success = [newdb executeUpdate:@"UPDATE MoneyValidation SET client_name = ?, sum = ?, duration = ?, monthly_payment = ?, payment_type = ?, debt_type = ?, debt_code = ?, categoryType = ?  WHERE client_index = %d;", entities.client_name, entities.sum, entities.duration, entities.duration, entities.monthly_payment, entities.payment_type, entities.debt_type, entities.debt_code, entities.categoryType, entities.client_index];

if (success)
{
    NSLog(@"OK");
    [db commit];
    [db close];
}
else 
{
    NSLog(@"FAIL");
}

NSLog(@"OBJ - %@", entities);}

The table structure :

CREATE TABLE MoneyValidation (ID INTEGER PRIMARY KEY, client_index INTEGER, client_name TEXT, sum TEXT, duration TEXT, monthly_payment TEXT, payment_type TEXT, debt_type TEXT, debt_code INTEGER, categoryType TEXT)

• client_index is a unique code;

  Can someone help me with some advice about this problem? Thank you!

Answer 1

I think the problem lies in your update statement. All values passed onto "executeUpdate" should be objects.

In your update SQL statement, entities.debt_code and entities.client_index are being passed as INTEGERS, you need to pass them as NSNumber objects.

Try the following:

success = [newdb executeUpdate:@"UPDATE MoneyValidation 
           SET client_name = ?, sum = ?, 
           duration = ?, monthly_payment = ?, 
           payment_type = ?, debt_type = ?, 
           debt_code = ?, categoryType = ?  
           WHERE client_index = %d;", 

          entities.client_name, entities.sum, 
          entities.duration, entities.duration, 
          entities.monthly_payment, entities.payment_type, 
          entities.debt_type, 
          [NSNumber numberWithInt:entities.debt_code],  // Convert int to object type
          entities.categoryType, 
          [NSNumber numberWithInt:entities.client_index] // Convert int to object type
          ];