float, decimal separator

[Zoran]

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?

I believe that you can put it in unit where your component is defined. That should not hurt. Then, a user may or may not put it in his program. Anyway, better test the behaviour yourself.

Will this clocale initialize SySUtils.DecimalSeparator or SysUtils.DefaultFormatSettings.DecimalSeparator?

See in source how SysUtils.DecimalSeparator is declared:

Code: [Select]

  DecimalSeparator : Char absolute DefaultFormatSettings.DecimalSeparator;
Notice that there is absolute directive. This "absolute DefaultFormatSettings.DecimalSeparator" means that these two variables actually reference to same memory address. So, it is absolutely the same, but usage of "bare"  SySUtils.DecimalSeparator is now discouraged (see: http://wiki.lazarus.freepascal.org/User_Changes_Trunk#Locale_global_variables_are_deprecated). Therefore, use SysUtils.DefaultFormatSettings.DecimalSeparator.

[tatamata]

When and how locale settings are initialized from clocale to System.DefaultSettings?

[Zoran]

When and how locale settings are initialized from clocale to System.DefaultSettings?

It's SysUtils.

On Windows, where we don't use clocale, it is done in initialization section of SysUtils. On Unix systems it is done in initialization section of clocale.
How? Let's take a look -- In Windows version of SysUtils the procedure InitInternational is called in initialization section. This procedure calls GetFormatSettings...
On Unix, in initialization section clocale calls its version of GetFormatSettings.

[Bart]

On Windows, where we don't use clocale, it is done in initialization section of SysUtils.

And as I said, ListSeparator is not initialized on Windows, whereas DecimalSeparator is.

Bart

[tatamata]

Thank you guys, you helped me a lot to improve new version of zmsql:
http://www.4shared.com/file/B8SDbOQ1/TZMSQL-015.html

Su, currently it looks like this:
1. in ZMConnection:

Code: [Select]

.....
    property DecimalSeparator:Char read FDecimalSeparator write SetDecimalSeparator;
......
procedure TZMConnection.SetDecimalSeparator(const AValue: Char);
begin
  if FDecimalSeparator=AValue then exit;
  if ((AValue='.') or (AValue=',') or (AValue=#0)) then
    begin
      FDecimalSeparator:=AValue;
    end;
  case FDecimalSeparator of
    '.':
        begin
          SysUtils.DefaultFormatSettings.DecimalSeparator:='.';
          SysUtils.DefaultFormatSettings.ThousandSeparator:=',';
        end;
    ',':
        begin
          SysUtils.DefaultFormatSettings.DecimalSeparator:=',';
          SysUtils.DefaultFormatSettings.ThousandSeparator:='.';
        end;
  end;
end;
2. In ZMQueryDataSet:

Code: [Select]

function TZMQueryDataSet.FormatStringToFloat(pFloatString: string):Double;
{
pFloat string is original string from CSV table or JanSQL resultset.
Result is a float formatted according to current SysUtils.DefaultFormatSettings.DecimalSeparator and SysUtils.DefaultFormatSettings.ThousandSeparator.
These values can be changed by changing ZMConnection.DecimalSeparator property value, which in turn changes SysUtils.DefaultFormatSettings settings.
}
var
  fs:TFormatSettings;
  vFloatValue:Double;
begin
  fs.DecimalSeparator := SysUtils.DefaultFormatSettings.DecimalSeparator;
  fs.ThousandSeparator := SysUtils.DefaultFormatSettings.ThousandSeparator;
  case SysUtils.DefaultFormatSettings.DecimalSeparator of
     '.':
       begin
         pFloatString:=StringReplace(pFloatString,',','.',[rfReplaceAll, rfIgnoreCase]);
       end;
     ',':
       begin
         pFloatString:=StringReplace(pFloatString,'.',',',[rfReplaceAll, rfIgnoreCase]);
       end;
   end;
  vFloatValue:=StrToFloat(pFloatString, fs);
  Result:=vFloatValue;
end;

procedure TZMQueryDataSet.InsertDataFromCSV;
var
   i:integer;
   vFieldValue:string;
begin
  if self.Active=False then self.Open;
  if FSdfDatasetImport.Active=False then FSdfDatasetImport.Open;
  FSdfDatasetImport.First;
 while not FSdfDatasetImport.EOF do begin
   self.Append;
   for i:=0 to FFieldCount-1 do begin
     //Fields of Float type require special transformation.
     if self.Fields[i].DataType=ftFloat then
       begin
         vFieldValue:=FSdfDatasetImport.Fields[i].AsString;
         try
           self.Fields[i].Value:=FormatStringToFloat(vFieldValue); //This seems to work
         except
           self.Fields[i].AsString:=FSdfDatasetImport.Fields[i].AsString;
         end;
       end
     else self.Fields[i].Value:=FSdfDatasetImport.Fields[i].Value;
    end;
   self.Post;
   FSdfDatasetImport.Next;
  end;
 {self.Refresh; //Do not use, until http://bugs.freepascal.org/view.php?id=19631 is solved!}
end;

procedure TZMQueryDataSet.InsertDataFromJanSQL;
var
   i,n:integer;
   vFieldValue:string;
begin
  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
            try
              Fields[n].Value:=FormatStringToFloat(vFieldValue); //This seems to work O.K.
            except
              Fields[n].AsString:=vFieldValue;
            end;
          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;


[marcov]

Note that there has been some work done in trunk wrt decimalseparator in sqldb.

[eny]

The problem with list separator remains, Eny, would you make the bug report about that?

I'm gonna leave that to someone else.
My experiences with bug reports are disappointing.

[Bart]

I filed a bugreport about not initializing ListSeparator in GetLocaleFormatSettings() in Windows (Issue #19976).

Bart

[BigChimp]

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.

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

Tatamata, I don't know if you have implemented this, but PLEASE don't save csv data using localized (decimal, date, whatever) formats.

If a spreadsheet program requires localised decimal separators, it's much better to write export functionality for that program, or an import plugin for the spreadsheet.

As Zoran said, if you do do this, formats will be incompatible between systems.
(You could do something like this if you store the decimal separator in some file, write conversion routines for import, etc, but why? Too much work...)

Sorry to say that I still haven't had time to look at your work - still working on exporting datasets as XML (fpxmlxsdexport)...

Good luck!