Floating error sqlite ios

Question

I am using FMDatabase in my application and I have a small error when I try to insert these values -0.02, -0.01, -0.03.

I've tried to insert a simple plain query with exact values which is working, but when I had tried to insert values above with FMDatabase I got -0.019999999, 0.009999999. Can anyone suggest me how to fix this issue?

In the code below I got -0.02, so I don't know where is the problem:

if (strcmp([obj objCType], @encode(BOOL)) == 0) {
    sqlite3_bind_int(pStmt, idx, ([obj boolValue] ? 1 : 0));
}
else if (strcmp([obj objCType], @encode(int)) == 0) {
    sqlite3_bind_int64(pStmt, idx, [obj longValue]);
}
else if (strcmp([obj objCType], @encode(long)) == 0) {
    sqlite3_bind_int64(pStmt, idx, [obj longValue]);
}
else if (strcmp([obj objCType], @encode(long long)) == 0) {
    sqlite3_bind_int64(pStmt, idx, [obj longLongValue]);
}
else if (strcmp([obj objCType], @encode(unsigned long long)) == 0) {
    sqlite3_bind_int64(pStmt, idx, (long long)[obj unsignedLongLongValue]);
}
else if (strcmp([obj objCType], @encode(float)) == 0) {
    float fl = [obj floatValue]; // the values is -0.02
    sqlite3_bind_double(pStmt, idx, [obj floatValue]);
}
else if (strcmp([obj objCType], @encode(double)) == 0) {
    sqlite3_bind_double(pStmt, idx, [obj doubleValue]);
}

My plain example query which is working:

INSERT INTO Transactions VALUES('aaaaaa','aaaaaa',0,-0.02,1,1,'0','0',1,'0','0','aa');

Prepared statement:

INSERT INTO UTransaction (id, note, data, price, repeat, forecast, cat_id, info_id, mutable, rem_id, original_id, is_id) VALUES (:id, :note, :data, :price, :repeat, :forecast, :cat_id, :info_id, :mutable, :rem_id, :original_id, :is_id)

Your first mistake is using floats and doubles interchangeably. Show the FMDB code where you fetch the value. You are inserting doubles into the plain query. — borrrden, Jun 24 '13 at 09:33
The FMDB code is huge, I don't think so that I can just copy it, I am using FMDatabase. Where is my first mistake? — flatronka, Jun 24 '13 at 09:38
I agree with @borrrden. but this may gone if you format the float / double upto 2 decimal place this problem may not arise ie rounding off would help you. — Ashim, Jun 24 '13 at 09:39
"but when I had tried to insert values above with FMDatabase I got -0.019999999, 0.009999999" - got how? In any case, I suggest always using double to maintain precision. — borrrden, Jun 24 '13 at 09:40
I got -0.019999 in the database. But I can't see where I am using float and double interchangeably. — flatronka, Jun 24 '13 at 09:43

Apurv · Accepted Answer · 2013-06-24T09:48:49.747

0

You can not store float accurately into database. Float values always create problem of rounding. You should use decimal or numeric type for it.

While assigning the value do it by following way:

else if (strcmp([obj objCType], @encode(float)) == 0) {
    float fl = floorf(([obj floatValue]*100)/100); // the values is -0.02
    sqlite3_bind_double(pStmt, idx, [obj floatValue]);
}

edited Jun 24 '13 at 09:48

answered Jun 24 '13 at 09:40

Apurv

17,116
8
51
67

thank you for your answer, but my plain query is working where I am using -0.02 – flatronka Jun 24 '13 at 09:44
actually I just realized that the precision loss comes from NSNumber to double somewhere in the library. I think that the sqlite working with double and casting my NSNumber which contains a float value to double causes the precision loss. – flatronka Jun 24 '13 at 10:18

Floating error sqlite ios

1 Answers1