CODEWITHSUNDEEP
javaandroiddatabasesqliteandroid-sqlite
Athos
Athos

Reputation: 59

Android sqlite rounding numbers without request

I have a simple sqlite table created with:

private static final String TABLE_CREATE = "CREATE TABLE tab1("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "amount NUMERIC NOT NULL DEFAULT 0"
                + ");";

db.execSQL(TABLE_CREATE);

But when I insert values into the table they get rounded:

INSERT INTO tab1(amount) VALUES (123456789.12) // Becomes 1.23457e+08
INSERT INTO tab1(amount) VALUES (12345.67)     // Becomes 12345.7
INSERT INTO tab1(amount) VALUES (300087.59)    // Becomes 300088
INSERT INTO tab1(amount) VALUES (3000001.22)   // Becomes 3e+06

How can I save the full number into the table and still be able to use queries like:

SELECT * FROM tab1 WHERE amount < 0

Edit:

@MikeT thanks for your sugestion, I was using the Cursor getString method. Now I'm using the getDouble method and then converting to BigDecimal and rounding, it solved the problem.

Upvotes: 1

Views: 420

Answers (1)

MikeT
MikeT

Reputation: 56948

I think that your issue is how you are extracting the values, rather than how they are stored.

It would appear that you are using the Cursor getString method.

Try using the Cursor getFloat or getDouble methods instead.

The following (extracted from How flexible/restricive are SQLite column types?) shows you the results of the various Cursor get???? methods (except the exception from getBlob has been trapped and handled ) :-

For Double myREAL = 213456789.4528791134567890109643534276; :-

08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Column=REAL_COL<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>6037<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:      Unable to handle with getBlob.

SQlite is very flexible with regard to storing values. In short any value of can be stored in any column type and each row/column has it's own unique type.

You may wish to have a read of Datatypes In SQLite Version 3

Upvotes: 3

Related Questions