"Not a Number" is read as "0"

[PW]

Hello,

I have some trouble reading float values from the DataBase. I am using the ZeosLib 7.0 Alpha, PostgreSQL 8.4 and Delphi 2010 on Windows 7.

If I store a float value, which is set to NaN in the DataBase, it is correctly written as NaN. But reading it with
"Query.FieldByName('x').AsFloat;", I get a "0".

Any Ideas?

Patrick

[PW]

Hi,

Now I have helped myself.

To solve it, you have to modify the following function in the core\ZSysUtils.pas:

Code: Select all

function SQLStrToFloatDef(Str: string; Def: Extended): Extended;

Add the two lines at the beginning:

Code: Select all

  if Str = 'NaN' then
    Exit(NaN);

I found out that even the Delphi core files cannot parse a "NaN"-String into a float, although Delphi creates such strings with FloatToStr...

[seawolf]

Thank you, but what is the meaning of NaN?

[guidoaerts]

from the postgresql doc's :
<quote>
In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning "not-a-number". Any operation on NaN yields another NaN. When writing this value as a constant in a SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.

The types decimal and numeric are equivalent. Both types are part of the SQL standard.
<end quote>

[mdaems]

guidoaerts,
PW,
Is there a SQL standard telling that a SQL response of 'Nan' for a numeric value corresponds to 'null'? In that case we ccould consider solving it using the SQLStrToFloatDef patch. if not, this should be handled in the zdbcPostgres* units, not in the general zsysutils unit.

Mark

[guidoaerts]

afaik, the sql standard does not allow for non-values, they should raise an error.

[mdaems]

In my opinion this means the solution should be in a dbc postgres unit insead of sytem. Before calling SQLStrToFloatDef the NaN situation should be handled.

Mark