How to insert a float into a SQL table in the right locale

Question

I have to insert float values into different SQL Servers. Each one can have different locales so in one it the representation could be "42,2" and at another one "42.2" or whatever. How should i construct the query so it works in any SQL Server?

Do i need to detect the locale of the server before constructing the query or what?

Answer 1

Use prepared statements. Example (in java):

long id=1234;
BigDecimal value = new BigDecimal("42.2");// parse it, or get it form a text field

PreparedStatement pstmt = connection.prepareStatement(
    "UPDATE test_table SET decimal_field=? WHERE id=?");
pstmt.setBigDecimal(1, value);
pstmt.setLong(2, id);
pstmt.executeUpdate();

With prepared statements, you don't have to worry about escaping strings with '' or sql injection.

Answer 2

Unless you're passing basic constant literals (ie. the number 0 and 1 for a column, every time), then you should be using parameterized queries.

To do that, you need to specify in your SQL statement that "here I don't write the value, but I will provide the value of this parameter alongside the SQL statement".

Here's how to do that in .NET:

using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "INSERT INTO tablename (column) VALUES (@value)";
    cmd.Parameters.Add("@value", 42.2);                         ^
    cmd.ExecuteNonQuery(); ^                                    |
}                          |                                    |
                           +-- these have to match up ----------+

Here you can see that I don't do any formatting of the value at all, I'm providing it as a normal C# literal value. I can of course also add it from a variable, again without having to reformat it to text.

You should be passing values this way, or you have to deal with all kinds of formatting issues, and if you're taking values from the user, you can also be setting yourself up for an SQL injection attack, which is bad!

Answer 3

Hi,

People have answered the insert, but not the locale.

SQL has locale support built-in, as you would expect. If you give it a type, say a date, it will store this date in whatever locale/collation you configure on SQL. Consuming this from SQL into .NET is automatic - you don't have to explicitly convert a US SQL date into a GB .NET date.

Note, this intelligence is lost if you send SQL a value encoded into a string, you are responsible for this as all SQL sees is a string - not a date or numeric.

Inserting or retrieving localised values from SQL will occur implicitly where possible - so using a parameterised query (with a parameter being a supported type) will be fine.

I've seen a situation where dates were being stored on the database as strings. Caused havoc because three clients were GB, US, and BR - no implicit locale support from .NET or SQL, they just try and consume it and fail on some.