Decimal symbols

rickdenhaan posted 5 years ago in Feature discussion
HeidiSQL appears to use the system's locale for determining the decimal symbol. Since I'm in Holland, Heidi is using a comma for decimal separation instead of a period.

This can be extremely confusing, because this means that when editing data from the overview, decimal numbers need to be entered using a comma (e.g. "10,25") instead of a period ("10.25"). However, when updating data using a SQL query, a period needs to be used because, well, that's how SQL works. The same applies for pretty much every other system capable of working with a SQL database. Everything uses a period for decimal separation.

I can see why the choice was made to use the system locale, because that will cause Heidi to display decimal numbers in a format the user is accustomed to seeing, but we've been seeing an enormous amount of user error due to this confusion. Just this morning, a member of our support staff used Heidi to update a product's price from "7,50" to "10.00" -- since Heidi didn't recognize that period as a decimal separator, the price suddenly became "1000,00".

I would really prefer Heidi to just use a period for a decimal separator, or at the very least offer users the choice in a configuration option somewhere in the preferences window.
ansgar posted 5 years ago
I think most database clients do that, but I'm not sure. However, it's very normal that clients use the local number format - which is exactly the purpose of it. But I know what you mean, as I'm located in Germany with the same format settings.
kalvaro posted 5 years ago
I think current settings are OK because they belong to two different contexts:

- Enter data into an application (where the app is supposed to be as user-friendly as possible).
- Write code (where you're supposed to stick to a rigid programming language).

Whatever, I can understand the confusion and HeidiSQL is not 100% coherent so far. For instance, when you have a DATE column you enter "2013-02-26" everywhere.

I was about to suggest that the "." key in the numeric keypad would actually enter the decimal separator ("," in your case) as e.g. Microsoft Excel does, but I'm not really sure whether we want that always.

As about your support staff:

- Don't you think that making it configurable would probably add more confusion?
- You absolutely need a proper app to update prices, but you probably know that already :)
kalvaro posted 5 years ago
Just tested—In Excel, numeric keypad's "." key enters:

- decimal separator in the spreadsheet
- period in macro editor

It doesn't fix the root problem but it's a great usability aid.
rickdenhaan posted 5 years ago
We actually have a custom-built app for managing prices, but it's a brand new app and still buggy as ####, which is why we're updating prices directly in the database until it's been fixed ;-)

If the numpad "." enters the same decimal separator, that would be a great start. But I still think it should be a configurable option.

Heidi is great for managing datasets, but not so great when you need to update a large (or largish) number of rows with the same data. A simple UPDATE-query is often simpler. The confusion about whether or not a decimal number requires a period or a comma also applies in that sense: people may use a comma in the query instead of a period, because Heidi displays a comma in the data view.

Or you could include the decimal setting in the language choice. If I use the Dutch interface language, use a comma. If I switch to English (which is possible in the preferences), use a period as that is the default in the English language.
rickdenhaan posted 5 years ago
By the way, speaking of Excel, it does use the local format in the spreadsheet, but then again, it also translates the names of all functions...

In the Dutch version, instead of using the function =CONCATENATE(A1; B1) to put two strings together, I actually have to use the function =TEKST.SAMENVOEGEN(A1; B1). Not the most user-friendly app ever :-)
kalvaro posted 5 years ago
Binding format to language is not feasible. E.g., in Spain we use comma, in Mexico they use period and both countries speak Spanish.

I shall never use Excel as example for anything xD
ansgar posted 5 years ago
Exactly. German is also spoken in several countries. I think that's what the language country additions are for, e.g. de_DE / de_AT / de_CH. Where HeidiSQL does not use these country shortcuts - a german translation is only available for "de", for simplicity reasons and not to blow up the effort for translating.

I could try out to modify the DecimalSeparator and the ThousandSeparator at application start, after making it optionally to localize. Give me some time to check that.
rickdenhaan posted 5 years ago
Have fun ;-)

Please login to leave a reply, or register at first.