| Detailed description |
Description:
Using the money format with system locales that uses as decimal separator the character ',' and as thousand separator the character '.' produces wrong values inserted in the DB (money format works correctly with locales that uses as decimal separator the character '.' and as thousand separator the character ',').
System:
Windows XP, with current locale set as it-IT.
PostgreSQL 9.0 (postgresql.conf, lc_monetary = 'Italian_Italy.1252')
NPGSQL 2.0.11.0
Example of code used for debugging:
NpgsqlCommand = new NpgsqlCommand("UPDATE prices SET price = @price WHERE id = 1", conn);
decimal myMoney = 8.99m;
NpgsqlParameter np = command.Parameters.Add("@price", NpgsqlTypes.NpgsqlDbType.Money);
np.Value = myMoney;
Result: the row is updated but the amount of money is wrong (€899,00 instead of €8,99).
Some examples, setting:
myMoney = 8.99m I obtain in DB €899,00 (expected €8,99)
myMoney = 100m, I obtain in DB €100,00 (correct)
myMoney = 75.25m, I obtain in DB €7.525,00 (expected €75,25)
NPGSQL logging has shown these lines:
Entering NpgsqlParameterCollection.Add(@prezzo, Money)
Entering NpgsqlParameter.NpgsqlParameter(@prezzo, Money, 0, )
Set NpgsqlParameter.ParameterName = @prezzo
Set NpgsqlParameter.NpgsqlDbType = Money
Entering NpgsqlParameterCollection.Add(Npgsql.NpgsqlParameter)
Get NpgsqlParameter.ParameterName
Get NpgsqlParameter.ParameterName
Set NpgsqlParameter.Value = 22,34
...
String written: UPDATE prezzi SET prezzo = ((E'22.34')::money) WHERE id = 1.
The money value in the query is written using the '.' as decimal separator, but current system locale uses the ',' as decimal separator and the '.' as thousand separator, so the value insert in DB is €2.234,00. |
|
