System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: b167206ad343da45eaefe988bc71ff24ec1b8284
Title: Large decimals gets rounded (123456789123456780 -> 123456789123457000)
Status: Closed Type: Code_Defect
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Unable_To_Reproduce
Last Modified: 2017-10-16 03:01:54
Version Found In: 1.0.105.2
User Comments:
anonymous added on 2017-09-07 08:26:14:
When having a table, with a column of datatype DECIMAL(38,9) (or just NUMERIC), data gets read wrong, when using SQLiteDataAdapter.Fill(DataTable dt).

I have value 123456789123456780 stored in DB and it gets read as 123456789123457000M.

My workaroud: 

new public int Fill(DataTable dataTable)
{
	var rowsAddedToDataSet = 0;

	var reader = _command.ExecuteReader();
	var dataSet = dataTable.DataSet;

	for (var i = 0; i < reader.FieldCount; i++)
		dataTable.Columns.Add(reader.GetOriginalName(i), reader.GetFieldType(i));

	while (reader.Read())
	{
		rowsAddedToDataSet++;

		var row = dataTable.NewRow();

		for (var i = 0; i < reader.FieldCount; i++)
		{
			if (reader.GetFieldType(i) == typeof(Decimal))
				row[i] = reader.GetDecimal(i);
			else
				row[i] = reader.GetValue(i);
		}

		dataTable.Rows.Add(row);
	}

	reader.Close();

	return rowsAddedToDataSet;
}

Feel free to contact me for more info.

mistachkin added on 2017-09-07 18:48:16:
Is the value inserted into the database as a string?  Do you have a code snippet
that shows how this is done?

Also, what is the schema of the table involved in this?

mistachkin added on 2017-09-07 19:34:14:
I've been able to recreate the symptoms described in this ticket.  It boils down
to the fact that the Decimal type internally maps to the Double type affinity.

The only viable alternative would be to map it to the Text affinity; however,
that would break backwards compatibility.

Your workaround seems like the correct way to deal with this situation.  The
GetValue method uses automatic type detection that relies upon the affinity
mappings; hence, it will not produce the desired result in this case.

mistachkin added on 2017-09-07 19:44:26:
This issue has existed since the start of the project in 2005.

[/artifact?ln=430-431&name=a746c57897de5d6d]

mistachkin added on 2017-09-07 19:54:45:
I'm adding a new connection flag (for use in the connection string) that will
force the Decimal columns to be treated as text.

mistachkin added on 2017-09-07 20:02:56:
The new connection flag is "GetDecimalAsText".  It can be activated for a specific
connection by adding the following property to the connection string:

    Flags=GetDecimalAsText;

When doing this, be sure there is a semicolon after the previous property, e.g.:

    "Data Source=test.db;Flags=GetDecimalAsText;"

Alternatively, it can be activated for the process by using the associated
environment variable, e.g.:

    Environment.SetEnvironmentVariable("DefaultFlags_SQLiteConnection",
        "GetDecimalAsText");

mistachkin added on 2017-09-07 20:07:17:
Fixed on trunk via check-in [59d87c8bce].

mistachkin added on 2017-09-07 20:07:44:
Can you compile the code on trunk and let us know if this change works for you?

mistachkin added on 2017-09-16 04:30:19:
    class Program
    {
        static void Main(string[] args)
        {
            var toSave = 12345678912345.1556346M;

            Console.WriteLine("Testing problem A - english culture");
            ProblemA(toSave);

            Console.WriteLine("-----------------------------------");

            Console.WriteLine("Testing problem B - danish culture");
            ProblemB(toSave);

            Console.ReadKey();            
        }

        static void ProblemA(decimal toSave)
        {
            System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
            System.Globalization.CultureInfo.CurrentUICulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");

            Problem("Data Source=testProblemA.db;Flags=GetDecimalAsText;", toSave);
        }

        static void ProblemB(decimal toSave)
        {
            System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("da-DK");
            System.Globalization.CultureInfo.CurrentUICulture = System.Globalization.CultureInfo.GetCultureInfo("da-DK");

            Problem("Data Source=testProblemB.db;Flags=GetDecimalAsText;", toSave);
        }

        static void Problem(string connnectionString, decimal toSave)
        {
            using (var connection = new SQLiteConnection(connnectionString))
            {
                connection.Open();

                using (var cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS Test (Id INTEGER PRIMARY KEY AUTOINCREMENT, Num NUMERIC);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = $"INSERT INTO Test (Num) VALUES (@Num)";
                    cmd.Parameters.AddWithValue("@Num", toSave);
                    cmd.ExecuteNonQuery();
                }
            }

            Console.WriteLine($"Saved {toSave}");

            var dt = new DataTable();

            using (var connection = new SQLiteConnection(connnectionString))
            {
                connection.Open();

                using (var cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = "SELECT * FROM Test";

                    var adapter = new SQLiteDataAdapter(cmd);

                    adapter.Fill(dt);
                }
            }

            foreach (DataRow row in dt.Rows)
                Console.WriteLine($"Read ID: {row.Field<long>("Id")} Num: {row.Field<decimal>("Num")}");
        }
    }

mistachkin added on 2017-09-16 04:42:58:
Looks like there needs to be a new parameter binding flag too.

mistachkin added on 2017-09-16 06:28:48:
The new connection flags are now "GetDecimalAsText" and "BindDecimalAsText".
They can be activated for a specific connection by adding the following property
to the connection string:

    Flags=GetDecimalAsText,BindDecimalAsText;

When doing this, be sure there is a semicolon after the previous property, e.g.:

    "Data Source=test.db;Flags=GetDecimalAsText,BindDecimalAsText;"

Alternatively, they can be activated for the process by using the associated
environment variable, e.g.:

    Environment.SetEnvironmentVariable("DefaultFlags_SQLiteConnection",
        "GetDecimalAsText,BindDecimalAsText");

It should also be noted that in order to work around automatic type affinity
detection provided by the SQLite core library, the column types should be
"DECIMALTEXT" and "NUMERICTEXT" instead of "DECIMAL" and "NUMERIC".

mistachkin added on 2017-10-16 03:01:54:
With the latest changes on trunk, I'm unable to reproduce the issue if the missing
decimal separators.