How to insert a float into an SQL table in the correct locale

Question

I need to insert float values ​​in different SQL servers. Each of them can have different locales, therefore in one its representation can be "42.2", and another - "42.2" or something else. How should I build a query so that it works on any SQL Server?

Do I need to determine the server locale before constructing the request or what?

Answer 1

If you do not pass basic constant literals (that is, the numbers 0 and 1 for the column each time), then you should use parameterized queries.

SQL-, " , SQL".

.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 ----------+

, , #. , , , .

, , , SQL-, !

Answer 2

. ( 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();

'' sql injection.

Answer 3

, .

SQL , . , , , /, SQL. SQL .NET - SQL SQL GB.NET.

, , SQL , , , SQL , .

SQL , , ( ) .

I saw a situation where dates were stored in the database as strings. Chaos caused, because the three clients were GB, US and BR - not the implied support for locales from .NET or SQL, they just try and consume it and fail on some.