Android Question Prevent automatic rounding in Sqlite

[ernschd]

Hello,

I have a field of data type "Numeric" in a Sqlite database where floating point numbers are stored. Unfortunately, Sqlite seems to automatically round the decimal places when they exceed a certain length (at least on Android).
For example, 12345.678 becomes 12345.7
This is unacceptable as I need the high accuracy.
That's why I experimentally changed the data type to "Real", but unfortunately with the same result.

Of course I could store the value without decimal point as integer (12345678) and divide it by 1000, but shouldn't actually a modern database system be able to handle floating point values directly?

Does anyone have a suggestion for a better solution?

Many thanks in advance.

Regards.

 

[Erel]

How are you getting the result? Don't use ExecSingleQuery.

 

[ernschd]

I looked into the Sqlite database with an app (SQLite Database Editor).

 

[Mahares]

or example, 12345.678 becomes 12345.7
This is unacceptable as I need the high accuracy.

Use a data type TEXT for that. column SQLite will accept it and will not tamper with any rounding. It will keep it as is. You can still do any mathematical calculations as if it was stored numeric.

 

[klaus]

I suppose that you get the value with GetString.
You should use GetDouble.
Unfortunately, SQLit returns REAL numbers as Float numbers when you use GetString.
I had seen this some time ago.

 

[Mahares]

SQLite data that is numbers:

If you store column as TEXT data type:
cursor1.GetDouble("Model") or cursor1.GetString("Model") will display without any truncation. 12345.678

If you store column as NUMERIC or REAL data type:
cursor1.GetString("Model") will display truncated 12345.678 shows as 12345.7
cursor1. GetDouble ("Model") will display as is without truncation 12345.678

cursor1.getlong("Model") will truncate all decimals and displays: 12345 for all types

 

[MikeSW17]

Just remember 'Accuracy' can have different meanings. For scientific/mathematical calculations, you need to establish the degree of accuracy required/meaningful to the task.
If you're dealing with accounting figures, you must take care to avoid any rounding in calculations - generally by working with integers only for storage and calculation, then inserting a decimal point at display/print time. Works well for addition/subtraction, but additional checks and corrections often needed for multiplication/division.