float, decimal separator

[tatamata]

I have a TBufDataSet descendent that loads data from CSV table.
Float numbers are stored with dot (".") as decimal separator and must not be changed in csv table.
How to change decimal separator in bufdataset once data is loaded?
Is there a property in application or TDataSet or bufdataset level that determines default decimal separator?
Is there a property that "knows" what is local system setting for decimal separator?

[eny]

The other day I tried to use the list separator from the locale settings (Windows).
It appeared that Lazarus didn't use the locale setting but used a hard coded default.
So I'm assuming if you want to use the system locale setting (for example the decimal separator) you need to retrieve it 'manually'.

[BigChimp]

Have a look at
SysUtils.FormatSettings.DecimalSeparator, I think it affects FloatToStr etc. but look it up to be sure.

I use it like this for export to XML:

Code: [Select]

 
FFormatSettingsOriginal := SysUtils.FormatSettings;
... change to something else
        SysUtils.FormatSettings.DecimalSeparator := '.';
.. and reset afterwards:
SysUtils.FormatSettings:=FFormatSettingsOriginal;

There might be smarter ways to do this, but I haven't found one yet.

[tatamata]

OK. I have a package with TZMQueryDataset component that loads a query result from csv tables to itself. Please see/download the package with included test project.
http://www.4shared.com/file/O0uay3uR/TZMSQL_015_alpha1tar.html

The JanSQL database engine operates with csv tables and float type fields must use dot (".") as decimal separator.
However, I want to enable TZMQueryDataset to have decimal separator either dot either comma, according to user's preference or locale settings.
Therefore I added DecimalSeparator property to TZMConnection component, so that TZMQueryDataset can use it during loading data to memory.
So, I tried with this:

Code: [Select]

procedure TZMQueryDataSet.InsertDataFromJanSQL;
var
   i,n:integer;
   vFieldValue:string;
   vFloatValue:Double;
   fs : TFormatSettings;
begin
  //Get locale settings
 //GetLocaleFormatSettings(0, fs);
  if self.Active=False then self.Open;
  with self do begin
    for i:=0 to FRecordCount-1 do begin
      Append;
      //Iterate columns
      for n:=0 to FFieldCount-1 do begin
        vFieldValue:=ZMConnection.JanSQLInstance.RecordSets[FRecordsetIndex].records[i].fields[n].value;
        if Fields[n].DataType=ftFloat then
          begin
            case ZMConnection.DecimalSeparator of
              '.':
                begin
                  vFieldValue:=StringReplace(vFieldValue,',','.',[rfReplaceAll, rfIgnoreCase]);
                  fs.DecimalSeparator := '.';
                  fs.ThousandSeparator := ',';
                  SysUtils.DecimalSeparator := '.';
                  SysUtils.ThousandSeparator := ',';
                end;
              ',':
                begin
                  vFieldValue:=StringReplace(vFieldValue,'.',',',[rfReplaceAll, rfIgnoreCase]);
                  fs.DecimalSeparator := ',';
                  fs.ThousandSeparator := '.';
                  SysUtils.DecimalSeparator := ',';
                  SysUtils.ThousandSeparator := '.';
                end;
            end;
            vFloatValue:=StrToFloat(vFieldValue, fs);
            Fields[n].Value:=FormatFloat('#,##0.00',vFloatValue, fs); //This seems to work O.K.
            //Fields[n].AsFloat:=FormatFloat('#,##0.00',vFloatValue, fs); //This raises design-time exception: "/root/my-documents/my-documents/MojiProgrami/TZMSQL-0.1.5/zmquerydataset.pas(802,71) Error: Incompatible type for arg no. 1: Got "AnsiString", expected "Double""
          end
        else Fields[n].Value:=vFieldValue;
      end;
      Post;
    end;
    {self.Refresh; //Do not use, until http://bugs.freepascal.org/view.php?id=19631 is solved!}
  end;
end;

So,

• First of all, I am not sure that this is good approach.
• Fields[n].AsFloat:=FormatFloat('#,##0.00',vFloatValue, fs); //This raises design-time exception: "/root/my-documents/my-documents/MojiProgrami/TZMSQL-0.1.5/zmquerydataset.pas(802,71) Error: Incompatible type for arg no. 1: Got "AnsiString", expected "Double"". Why?
• How to get info about locale settings? The Delhi GetLocaleFormatSettings(0, fs) seems not to be existent?
• DisplayFormat in Columns of a dbgrid (see the test project) does not seem to affect the float representation at all?

What am I doing wrong?

[Zoran]

How to get info about locale settings? The Delhi GetLocaleFormatSettings(0, fs) seems not to be existent?

Use DefaultFormatSettings variable of type TFormatSettings. It is initialized to system defaults when the application starts, so you can use DefaultFormatSettings.DecimalSeparator to obtain local decimal separator.

[eny]

It is initialized to system defaults when the application starts

Lazarus does no such thing; these variables are completely useless.

It appeared that Lazarus didn't use the locale setting but used a hard coded default.

[avra]

However, I want to enable TZMQueryDataset to have decimal separator either dot either comma, according to user's preference or locale settings.

Are you sure that you want to have database field format dependent of regional settings? User can not give database to anybody without fear that database load will fail. Just imagine that to see someone else's data you always have to change regional settings, and to go back to your data you have to do it again. Not to mention that you can't open both at once. It is far better to have have always the same database format and only vary presentation of the data depending on locale settings or user preference. I remember scratching my head why other people Matrikon OPC Explorer files didn't work on my pc. It was because decimal point on their pc was '.' and on my it was ','. I reported this as a bug and they found it enough annoying to fix it. If I recall well, similar was with ancient localized version of Excel.

[Zoran]

It is initialized to system defaults when the application starts

Lazarus does no such thing; these variables are completely useless.

It appeared that Lazarus didn't use the locale setting but used a hard coded default.

Eny, before I posted that, I had seen your post, then tested it and it worked well. However, I actually had tested it only on Windows.
Now I tried it in Ubuntu and I see that it really does not work there.
On Windows it works okay, there must be some problem with implementing that on Linux.

I have just reported the bug: http://bugs.freepascal.org/view.php?id=19963

[Zoran]

However, I want to enable TZMQueryDataset to have decimal separator either dot either comma, according to user's preference or locale settings.

Are you sure that you want to have database field format dependent of regional settings? User can not give database to anybody without fear that database load will fail. Just imagine that to see someone else's data you always have to change regional settings, and to go back to your data you have to do it again. Not to mention that you can't open both at once. It is far better to have have always the same database format and only vary presentation of the data depending on locale settings or user preference. I remember scratching my head why other people Matrikon OPC Explorer files didn't work on my pc. It was because decimal point on their pc was '.' and on my it was ','. I reported this as a bug and they found it enough annoying to fix it. If I recall well, similar was with ancient localized version of Excel.

I totally agree.
Tatamata, I didn't understand that you ment this, but if you really ment to store data in format dependent on local settings, that is a very bad idea.

[tatamata]

Well, generally agree and actually I was talking mainly about data presentation.

However, in zmsql, a "database" is just a folder containing csv tables. So, when saving dataset as csv table, it has some sense to be able to save a table in localized decimal format. Then it can be easily opened in a spreadsheet program, for example with accurate float recognition....

A question: what is scope of Sysutils.DecimalSeparator? Application?

What does it influence?

[eny]

Eny, before I posted that, I had seen your post, then tested it and it worked well. However, I actually had tested it only on Windows.
Now I tried it in Ubuntu and I see that it really does not work there.
On Windows it works okay, there must be some problem with implementing that on Linux.

And how did you test that?

It most definitely doesn't work for Windows (XP).
The screenshot displays how the list separator is displayed in a message box (a comma) together with the PC's locale setting (a semicolon).

[Bart]

ListSeparator isn't set in GetLocaleFormatSettings()
(rtl\win\sysutils.pp)

There should probably be some line in this procedue like:

Code: [Select]

  ListSeparator := GetLocaleChar(LID, LOCALE_SLIST, ';');

Bart

[Zoran]

A question: what is scope of Sysutils.DecimalSeparator? Application?

What does it influence?

Yes, application. When the application starts, the fields of this variable are filled with system settings. It influences expected input formatting or output formatting of various functions... for example StrToFloat, FloatToStr, StrToCurr, CurrToStr, DateToStr, StrToDate, StrToTime...

It most definitely doesn't work for Windows (XP).
The screenshot displays how the list separator is displayed in a message box (a comma) together with the PC's locale setting (a semicolon).

I didn't test list separator character. It most definitely does work for decimal separator in Windows XP.
Here we were talking about decimal separator and I assumed that testing of one field would be enough.

[Zoran]

Now, Jonas Maebe answered in my bug report:

That's because it depends on libc functionality, and the default RTL units do not link to libc on Linux. Use the clocale unit: http://www.freepascal.org/docs-html/rtl/clocale/index.html (just like you have to use cthreads for thread functionality)

Tatamata, this should solve the problem with decimal separator. The problem with list separator remains, Eny, would you make the bug report about that?

[tatamata]

Now, Jonas Maebe answered in my bug report:

That's because it depends on libc functionality, and the default RTL units do not link to libc on Linux. Use the clocale unit: http://www.freepascal.org/docs-html/rtl/clocale/index.html (just like you have to use cthreads for thread functionality)

Tatamata, this should solve the problem with decimal separator. The problem with list separator remains, Eny, would you make the bug report about that?

Hm, where to put it:

Code: [Select]

uses
  {$ifdef unix}clocale{$endif}, ?
In the ZMConnection unit of my package or to leave it for users to put it in their programs?
Will this clocale initialize SySUtils.DecimalSeparator or SysUtils.DefaultFormatSettings.DecimalSeparator?