TField.AsCurrency causes "450.0" is an invalid float
lazarus / 16-Databases

----------------------------------------------------------------------
garlar27 · 2016-08-05 12:37

Hi,
    I'm using Lazarus 1.6 32 bit with ZEOS DBO 7.1.4 connecting to an
SQLite DB on a Windows 10 x64b. My country is Argentina.

I was recompiling and testing an old project and I got this weird
exception:
QuoteException class: EConvertError
Message: "450.0" is an invalid float

Testing with the old Lazarus I used before (1.1 SVN 40379) this
exception is never thrown.
Do you have any Idea on what might be causing this exception?

the total field  definition:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i [TOTAL]
REAL  NOT NULL,
This code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i       try
// except         Action := 'calculating totals';       
 //ZReadOnlyQuery.SQL.Add(Format( 'SELECT COALESCE(SUM(T.TOTAL),0) AS
TOTAL ' // this line causes the same exception than the next one.    
   ZReadOnlyQuery.SQL.Add(Format( 'SELECT SUM(T.TOTAL) AS TOTAL '    
                             + 'FROM TRANSACTION T '                 
                + 'INNER JOIN ACCOUNTED_INVOICE V ON V.TRANSACCION_ID
= T.TRANSACCION_ID '                                   + 'WHERE
T.INFORMEZ_ID = %S;', [QuotedStr(AGUID_ZZ_EnCurso)]));          if
(ZReadOnlyQuery.RecordCount > 0) then begin            Action :=
'getting total value';            TotalValue :=
ZReadOnlyQuery.FieldByName('total').AsCurrency;         end;        
 Result := True;       except         on E: Exception do begin       
   CreateExceptionLog(emftNone, Action, 'MyObject.GetTotals', E);    
   end;      end; 
causes this exception:
Quote - ERROR: [MESSAGE: "Exception getting total value with the
message: "450.0" is an invalid float"; TEXT: "Program exception!
Stacktrace:

Exception class: EConvertError
Message: "450.0" is an invalid float
  $0041CAED
  $0041CABE
  $005CD25C
  $00615821
  $00618B06
  $0061936F
  $0043E0D3
  $0043DE89
  $0043E07C
  $0052C025
  $0052BE13
  $0040E248
  $0052A0A6
  $005204D9
  $005216EC
  $0042D982
  $005810AF
"; EXTRA: "MyObject.GetTotals"]

----------------------------------------------------------------------
Thaddy · 2016-08-05 13:12

Isn't that a correct exception? I thought Argentina writes floats with
a comma instead of a dot. e.g. 100,00 is valid 100.00 isn't valid.

----------------------------------------------------------------------
garlar27 · 2016-08-05 14:27

That's right. But to avoid problems caused by the comma vs. dot in
SQL, XML, JSON, etc, I internally use US format for numbers and the
User regional format for the output.

Nevertheless, the SQLite engine accumulated the totals pretty well but
the TField.AsCurrency failed.-

----------------------------------------------------------------------
Thaddy · 2016-08-05 15:06

I try to get together a simple example. You can define the decimal
separator yourself.

----------------------------------------------------------------------
Leledumbo · 2016-08-05 17:02

I believe it uses SysUtils.DefaultFormatSettings under the hood, which
should default to whatever your system is configured for. So adjust
that to your wanted locale somewhere in your application
initialization.

----------------------------------------------------------------------
Bart · 2016-08-05 20:37

I had a similar error once in Delphi.
It may very well be that in Lazarus 1.1, DefaultFormatSettings were
not updated when Windows sends a certain message around.
However now Lazarus does.
You can alter that by setting Application.UdpdateFormatSettings to
False (after you set DefaultFormatSettigs.DecimalSeparator to '.' (or
whatever suits your needs).

Bart

----------------------------------------------------------------------
LacaK · 2016-08-09 07:34

Can you test which field type Fields[0] (sum() column?)
I suspect, that it will be string, which explains why comes into play
locale decimalseparator.

----------------------------------------------------------------------
garlar27 · 2016-08-09 20:34

Sorry for the delay,

Leledumbo, change the local configuration will be my last resort.

Bart, It didn't work. Not mentioning it won't make a difference on
Linux.

LacaK, by definition, all fields in SQLite are .... well, they're a
little "extrange". See Datatypes In SQLite Version 3 and Determination
Of Column Affinity.
for example: once I located a DBGrid a ZConnection a ZTable (or it was
a TSQLite3Connection a TSQLTransaction and a TSQLite3DataSet :-? ) and
a DataSource on a form. Connected everything and set it to a DB and a
table. And when I run it   EVERY column in the DBGrid was huge   . I
took a look at the columns property and all have 300 or more
characters wide, even the defined as VARCHAR(10). In other words all
fields are string even if you declare it as DOUBLE.

I even tried to cast the SUM column to DOUBLE or REAL and got the same
result. 

I find it hard to believe that none had a problem like this. I need to
find out if this is something related to TField or if it is related to
ZEOS. In a few days I'll try to research a little more to get to the
problem.-