Postgres 8.4 and Decimal

baba · Post by **baba** » 31.12.2009, 14:36

Hello,

sure, but i find its a good idea, an i can use Skype all the Time and my iPhone ist 24/7 on my Side.

Wild_Pointer · Post by **Wild_Pointer** » 31.12.2009, 14:37

It seems to be FPC bug.

http://forum.lazarus.freepascal.org/ind ... pic=8150.0

I've wrote a test procedure:
procedure TForm1.Button2Click(Sender: TObject);
var _real: Double;
_dc: char;
begin
_dc := DecimalSeparator;
DecimalSeparator:='.';
_real := StrToFloatDef('1.2', 0);
DecimalSeparator:= _dc;
end;

if your locale decimal separator is not '.' then the result of _real is 12 - not 1.2. I don't know if the zeos code should take into account the bugs from FPC...

Post by **mdaems** » 31.12.2009, 15:22

This thread doesn't confirm the bug as far as I can read it.
I'll check with the fpc/lazarus people next year.

Mark

baba · Post by **baba** » 31.12.2009, 17:40

Wild_Pointer wrote:It seems to be FPC bug.

but the Lazarus-SQL-Comp works fine.

Post by **mdaems** » 03.01.2010, 14:05

Which is logical as they probably know their own limitations

Anyhow... I'd like to be sure to know what postgres returns in your cases. Is it effectively a ',' instead of '.'? In that case we don't have to talk about StrToFloatDef but about how we handle different decimal separators returned on different platforms.

To be honest, I think I had the same problem with mysql, but I just changed my settings so mysql returns decimal points... As zeoslib just assumes points are used. (By setting decimalseparator in the following function:

Code: Select all

function SQLStrToFloatDef(Str: string; Def: Extended): Extended;
var
  {$IFDEF DELPHI12_UP}
  OldDecimalSeparator: WideChar;
  {$ELSE}
  OldDecimalSeparator: Char;
  {$ENDIF}
begin
  OldDecimalSeparator := DecimalSeparator;
  DecimalSeparator := '.';
  if Pos('$', Str) = 1 then
    Str := Copy(Str, 2, Pred(Length(Str)));
  If Str = '' then
    Result := Def
  else
    Result := StrToFloatDef(Str, Def);
  DecimalSeparator := OldDecimalSeparator;
end;

As said above : I think we need something to fix this assumption:
a) make sure it's true by provinding the right settings at connection time OR
b) check what settings are active on the specific connection and use that information when decoding numbers.

Last option seems best to me, but is more difficult to implement, I believe.

Wild_Pointer · Post by **Wild_Pointer** » 03.01.2010, 17:38

I guess I've found the problem - its thousand separator. On locale settings I have it set to ".". FPC eliminates all thousand separator characters before trying to get float from string. As my thousand is set to "." FPC eliminates all "." characters in Str and so Decimal separator is lost too. My guess is that Delphi treats Str differently. I tried looking into SysUtils.TextToFloat, but I'm not so good at asm.
Do you have "." as thousand separator, baba?

Wild_Pointer · Post by **Wild_Pointer** » 03.01.2010, 17:45

mdaems wrote:Anyhow... I'd like to be sure to know what postgres returns in your cases. Is it effectively a ',' instead of '.'?

No. Postgresql returns 12345.67 (no thousand separator, "." as decimal separator) to me.

Post by **mdaems** » 03.01.2010, 20:12

Wild_Pointer,

That makes it a fpc specific problem, but it's not unreasonable. If zeoslib sets thousands=decimals in the coding, it's our own stupid fault, isn't it?
In my fpc version (2.5.1) and version 2.3.1 this seems fixed, in 2.2.2 it wasn't yet. Don't know about 2.2.4.

I guess we could solve this by overriding the thousand separator as well when using fpc, effectively forcing thousands := ',' and decimals:='.' in the code above? Even Delphi 5 support this, apparently.

Can you give this code a try?

Code: Select all

function SQLStrToFloatDef(Str: string; Def: Extended): Extended;
var
  {$IFDEF DELPHI12_UP}
  OldDecimalSeparator: WideChar;
  OldThousandSeparator: WideChar;
  {$ELSE}
  OldDecimalSeparator: Char;
  OldThousandSeparator: Char;
  {$ENDIF}
begin
  OldDecimalSeparator := DecimalSeparator;
  OldThousandSeparator := ThousandSeparator;
  DecimalSeparator := '.';
  ThousandSeparator := ',';
  if Pos('$', Str) = 1 then
    Str := Copy(Str, 2, Pred(Length(Str)));
  If Str = '' then
    Result := Def
  else
    Result := StrToFloatDef(Str, Def);
  DecimalSeparator := OldDecimalSeparator;
  ThousandSeparator := OldThousandSeparator;
end;

Mark

Post by **mdaems** » 03.01.2010, 21:32

Just made a small demo console program that confirms the conclusion above. I'll attach it to this post.

Compiled it using fpc 2.2.2 and both strtofloatdef and old (zeos) SQLStrToFloatDef give bad results, depending on the decimal and thousand separator settings. New (zeos) SQLStrToFloatDef works like expected.

Compiled it using fpc 2.5.1 and all 3 functions work like expected.

So I did commit the change to testing branch. (SVN rev. 761)
Does somebody think we should ifdef the changes so we only do them when using FPC versions below 2.3?

Mark

Wild_Pointer · Post by **Wild_Pointer** » 04.01.2010, 08:27

Hello, mdaems.

having decimal separator = thousand separator is not too good, so I vote for *not* using ifdef. Let's make things simple and clear.

Post by **mdaems** » 04.01.2010, 08:56

Can you confirm it effectively solves the issue?

Mark

Wild_Pointer · Post by **Wild_Pointer** » 04.01.2010, 09:37

Your test program works ok on my computer:

StrToFloatDef('12.5')=
Using locale (thousands=. decimals=,): 125,00
Using thousands=, decimals=.: 12.50
Using thousands=. decimals=.: 125.00
Using thousands=. decimals=,: 125,00
OldSQLStrToFloatDef('12.5')=
Using locale (thousands=. decimals=,): 125,00
Using thousands=, decimals=.: 12.50
Using thousands=. decimals=.: 125.00
Using thousands=. decimals=,: 125,00
SQLStrToFloatDef('12.5')=
Using locale (thousands=. decimals=,): 12,50
Using thousands=, decimals=.: 12.50
Using thousands=. decimals=.: 12.50
Using thousands=. decimals=,: 12,50

Also I changed the function SQLStrToFloatDef to the one you provided and it works OK. Thank you.

As baba is the one who opened the issue, I think it's he who should confirm the resolution.

baba · Post by **baba** » 04.01.2010, 12:58

Hello,

for the first time, i have switched to the LazComp. There works and i needed a Solution for Tuesday. (Tomorrow)

The SQLStrToFloatDef Funktion i will test later, with my Testprogramm.

Thx, and a Happy new Year to all.

baba · Post by **baba** » 04.01.2010, 15:30

Also I changed the function SQLStrToFloatDef to the one you provided and it works OK

In which File? There is no Function named "SQLStrToFloatDef"

Wild_Pointer · Post by **Wild_Pointer** » 04.01.2010, 16:45

In which File? There is no Function named "SQLStrToFloatDef"

Please, look in Zeos\src\core\ZSysUtils.pas