System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
2017-10-16
03:01 Closed ticket [b167206ad3]: Large decimals gets rounded (123456789123456780 -> 123456789123457000) plus 4 other changes artifact: acad51dbfe user: mistachkin
2017-09-16
06:28 Ticket [b167206ad3]: 3 changes artifact: c087cd926c user: mistachkin
06:21
Update several tests for ticket [b167206ad3] to use the 'DECIMALTEXT' and 'NUMERICTEXT' type mappings. check-in: 5032831744 user: mistachkin tags: trunk
05:49
Add BindDecimalAsText connection flag to force Decimal typed parameters to be bound as text. Pursuant to [b167206ad3]. check-in: 995978db06 user: mistachkin tags: trunk
04:42 Ticket [b167206ad3] Large decimals gets rounded (123456789123456780 -> 123456789123457000) status still Pending with 3 other changes artifact: 428f084342 user: mistachkin
04:30 Ticket [b167206ad3]: 3 changes artifact: f5d0da2565 user: mistachkin
2017-09-07
20:07 Ticket [b167206ad3]: 3 changes artifact: 494f204d1e user: mistachkin
20:07 Ticket [b167206ad3]: 3 changes artifact: 4cb65654ef user: mistachkin
20:06
Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to [b167206ad3]. check-in: 59d87c8bce user: mistachkin tags: trunk
20:02 Pending ticket [b167206ad3]: Large decimals gets rounded (123456789123456780 -> 123456789123457000) plus 6 other changes artifact: 0626b3f9c0 user: mistachkin
19:54 Ticket [b167206ad3]: 3 changes artifact: 8f4c356467 user: mistachkin
19:44 Ticket [b167206ad3]: 3 changes artifact: 1e06817996 user: mistachkin
19:34 Ticket [b167206ad3]: 3 changes artifact: d12d911b14 user: mistachkin
18:54 Ticket [b167206ad3]: 3 changes artifact: 2081500887 user: mistachkin
18:48 Ticket [b167206ad3]: 3 changes artifact: 9da5342b83 user: mistachkin
18:34 Ticket [b167206ad3]: 6 changes artifact: 73dded48f9 user: mistachkin
08:26 New ticket [b167206ad3]. artifact: 66d3f9c29f user: anonymous

Ticket Hash: 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.