SQLite reads small decimal values as 0?-CodePudding

I hava a sqlite table with "very small values" in one column. Like 0.00000363455000. When I run a query in "DB Browser for SQlite" the values are shown correct:

Screenshot DB Browser for SQlite 1

In my c# application the small values are always 0.

            using (SQLiteConnection connection = new SQLiteConnection(
                    "Data Source='"   dmaDataSource   "'"))
            {
                SQLiteCommand command = new SQLiteCommand(sqlQuery, connection);
                connection.Open();
                SQLiteDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    response = new DmaResponse();
                    
                    while (reader.Read())
                    {
                        Type t0 = reader.GetFieldType(0); // is decimal
                        Type t1 = reader.GetFieldType(1); // is decimal
                        string f0 = reader.GetName(0);
                        string f1 = reader.GetName(1);

                        response.price_per_ton = (decimal)reader[0];
                        string test = reader[1].ToString(); // "0"
                        response.price_per_mm3 = (decimal)reader[1]; // 0.00000363455000 becomes 0
                    }
                }
            }

Screenshot Comparison DB Browser / VS Debug

Any Ideas? Thanks, Christoph

After some more tests: Both fields are type of NUMERIC:

wich is decimal in c#.

I tried the GetDecimal method before I created this post.

response.price_per_ton = reader.GetDecimal(0);
string test = reader[1].ToString();
response.price_per_mm3 = reader.GetDecimal(1);

GetDecimal(0) works, GetDecimal(1) throwed an exception: {"Input string was not in a correct format."}

Then I used DB Browser to change the decimal separator in "my" row form , to .

Now, reader.GetDecimal(1) doesn't throw an exeption anymore, but gives back 0.

CodePudding user response：

Based on the documentation of sqlite, the fundamental data types are:

64-bit signed integer
64-bit IEEE floating point number
string
BLOB
NULL

In your case, it would be a 64-bit IEEE floating point number.

The Microsoft.Data.Sqlite.Core library (note that I am assuming you are using this), uses decimal.Parse on the result of GetString if you use GetDecimal on the reader.

This means that the field is retrieved as a string, causing an auto-convert of the value from numeric to text, and then a conversion back to a number with decimal.Parse. This has lots of ways that it could go wrong.

If you use GetDouble, the native method sqlite_column_double is used in the driver, and there will be no conversions (and I expect therefore no invalid formats).

A double is less precise than a decimal, so you'd probably want to convert the double to decimal before doing calculations with it.

For the smallest possible margin of error, store the decimals as a string in the database, that way you avoid any auto-convert (but will use more bytes to store the same data).

CodePudding user response：

I'm getting closer to it...

ServerVersion = "3.37.0"

Column[1] is type of System.Decimal and the expected value is 0.00000363455000.
reader[1].ToString() returns "0"
(decimal) reader[1] returns 0
reader.GetDecimal(1) throws an exception "Input string was not in a correct format."
reader.GetString(1) returns "0,00000363455000" (German decimal separator)
which I can cast to decimal.

Now I have my expected value but I don't know why ;-)

CodePudding user response：

You're not really using the reader correctly. You should use the reader's methods to retrieve values correctly. In this case the "GetDecimal" method:

response.price_per_ton = reader.GetDecimal(0);
response.price_per_mm3 = reader.GetDecimal(1);

Method info here: https:https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqlitedatareader.getdecimal?view=msdata-sqlite-6.0.0