Android sqlite rounding numbers without request

Question

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.

`amount TEXT NOT NULL DEFAULT 0` – IntelliJ Amiya Nov 24 '17 at 05:49 — IntelliJ Amiya, Nov 24 '17 at 05:49

MikeT · Accepted Answer · 2017-11-21T20:34:54.500

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

I'm using the 'getDouble' method now. It works, but after geting it I convert to bigdecimal and round, otherwise I would get some small errors like 2,09999999.... instead of 2,10. — Athos, Nov 25 '17 at 19:03

Android sqlite rounding numbers without request

1 Answers1