Why does sqlite converts . To ,

Question

I am completely confused by SQLite right now. I use the System.Data.SQLite Version for C#

I have two Tables:

Table 1: In it I have created two columns (more but they are the important in this case and the statement is way to long to post it). The first one is called RecStat and the second one DOXinM. Both are a Decimal and are created like this: ...

[RectStat] Decimal,
[DOXinM] Decimal,

...

The Create Table statement looks like this:

Create Table If Not Exists "Table1"
(..Columns..);

When I insert a value in this table the decimal works perfectly fine and when I read it out it works too on the xaml GUI.

After that I created a second Table which also has a Decimal Column. I Created the table the same way (this is a short one, So i can give the whole statement)

Create Table If Not Exists "Table2" (
 [ID] Integer Not Null Primary Key,
 [Material] VarChar(10],
 [Thickness] Decimal);

C# Code for the creation (Note that I work with a const string for all SQL Commands):

mySQLiteCommand.CommandText = @"" + DatabasePara.CREATE_SQL + " " + DatabasePara.TABLE_SQL + " " + DatabasePara.IF_SQL + " " + DatabasePara.NOT_SQL + " " + DatabasePara.EXISTS_SQL + " " + DBTableFilter.Filter + "     
mySQLiteCommand.CommandText += @"([" + DBTableFilter.ID + "] " + DatabasePara.INTEGER_SQL + " " + DatabasePara.NOT_SQL + " " + DatabasePara.NULL_SQL + " " + DatabasePara.PRIMARYKEY_SQL + ", ";
mySQLiteCommand.CommandText += @"[" + DBTableFilter.Material + "] " + DatabasePara.VARCHAR_SQL + "(360), ";
mySQLiteCommand.CommandText += @"[" + DBTableFilter.Thickness + "] " + DatabasePara.DECIMAL_SQL + ");";
mySQLiteCommand.ExecuteNonQuery();

In both case I do the same insert for the Decimal Column:

Insert into Table1 ('RectStat') values ('10.5');
Insert into Table1 ('DOXinM') values ('1.3');


Insert into Table2 ('Thickness') values ('0.25');

For the Table2 Insert:

for (int FilterIndex = 0; FilterIndex < Database.FilterTable.FilterList.Count; FilterIndex++)
         {
           mySQLiteCommand.CommandText = @"" + DatabasePara.INSERTINTO_SQL + " " + DBTableFilter.Filter + "(" + DBTableFilter.Material + ", " + DBTableFilter.Thickness + ") ";
           mySQLiteCommand.CommandText += @"" + DatabasePara.VALUES_SQL + " ('" + Database.FilterTable.GetFilterAt(FilterIndex).Material_Property + "', " + Database.FilterTable.GetFilterAt(FilterIndex).Thickness_Property + ");";
           mySQLiteCommand.ExecuteNonQuery();
          }

Now the Database inserts the first two Statements as 10.5/1.3 and the last one as 0,25 When I read out the value the first two works perfectly fine but the last one is shown without the numbers after the decimal "point".

So my question is. Why does this happens? (Btw I have more than 20 Decimals columns in my table and the "Thickness" is the only one with this problem)

I hope you can help me.

Thanks in Advance Richard

What I have tried:

Tried different Values (Had the hope that it was because of the 0.25) Tried it without the ''. But than the statement thought it had three values for two columns...

id guess because of the default language settings its using. — BugFinder, Jul 28 '16 at 12:32
read http://stackoverflow.com/questions/28084261/comma-vs-dot-as-decimal-separator — BugFinder, Jul 28 '16 at 12:35
@BugFinder my problem is (sadly) not exactly the same as in your link. My problem is that out ot 20 Decimals only one doesn't work. And i work with parameters for all my insert statements. (Maybe i miss the point thou) — Evosoul, Jul 28 '16 at 12:44
it would suggest that the language settings are different... — BugFinder, Jul 28 '16 at 12:46
@Evosoul - of course you're missing the point, because it get's turned into a comma! But seriously, your insert commands don't look like they properly use parameters. See http://stackoverflow.com/a/809275/1336590 for a clear example. — Corak, Jul 28 '16 at 12:56
Set a breakpoint at `ExecuteNonQuery` and check the value of `mySQLiteCommand.CommandText`. I bet, it contains a `,` instead of a `.` because your way of constructing queries implicitly calls `decimal.ToString()` which depends on your locale. — derpirscher, Jul 28 '16 at 12:59
@Corak you are right. It works this way. Thanks for the example! If you create an answer I will accept it — Evosoul, Jul 28 '16 at 13:13
@derpirscher Yes, it makes the typecast. But like I said I don't know why the toString method changes. All other Decimals, are build the same way and used in the same way, but they saves the . But nvm Corak gave me the answer. — Evosoul, Jul 28 '16 at 13:14
@Evosoul Either your are changing the locale somewhere, or you are constructing the query somehow different. — derpirscher, Jul 28 '16 at 13:16

score 2 · Accepted Answer · answered Jul 28 '16 at 13:11

First of all, you should use parameterized queries. They will get rid of conversion problems and prevent sql injection. Most likely your issues are created by the fact, that your type of query creation calls decimal.ToString() implicitly. For certain locales, this replaces the . with a , which leads to a syntax error.

 var query = "INSERT INTO yourtable COLUMNS(col1, col2, col3) VALUES(@p1, @p2, @p3)";
 var cmd = new SQLiteCommand(query, connection);
 cmd.Parameters.AddWithValue("@p1", 1);
 cmd.Parameters.AddWithValue("@p2", 2.2);
 cmd.Parameters.AddWithValue("@p3", "three");
 cmd.ExecuteNonQuery();

This will, among other things, send the decimals to your server in a correct format, and also take care of possible ' quotes in your string literals and prevent sql injection, thus making your code more secure.

Why does sqlite converts . To ,

1 Answers1