Postgres 8.4 and Decimal

baba · Post by **baba** » 29.12.2009, 05:08

Hello,

I read in a DBGrid:
ZEOS: ZQuery1.SQL.Add('SELECT * FROM test.preise');
Decimal(9.50) comes 950 with Format('0,00') comes 950,00

when i use
LAZARUS OWN: SQLQuery1.SQL.Add('SELECT * FROM test.preise');
Decimal(9.50) comes 9,5 Thats OK!

PGAdmin shows 9.5 Thats OK!

What is wrong with ZEOS?

Wild_Pointer · Post by **Wild_Pointer** » 29.12.2009, 10:40

Hello, baba

What version of zeos lib are you using? What is your Operating system. Do you use zeos with lazarus?

please provide the create script of your test.preise table (you can copy it from pgAdmin).

baba · Post by **baba** » 30.12.2009, 03:16

Hello,

this is the full create. It´s the same for all Numeric Fields.

-----------------------------------------------------------------------
EXAMPLE:
preis_01_t character varying(40), // Tip/Hint
preis_01_1 numeric(7,2), // Price 1 - 9.50 - in the Grid: 950
preis_01_2 numeric(7,2), // Price 2
preis_01_3 numeric(7,2), // ...
preis_01_4 numeric(7,2),
preis_01_5 numeric(7,2),
s1_mwst numeric(1), // Tax
-----------------------------------------------------------------------

Zeos 6.6
Lazarus 0.9.28.1 r22267 FPC 2.2.4 i386-win32-win32/win64

-- Table: test.preise

-- DROP TABLE test.preise;

CREATE TABLE test.preise
(
id serial NOT NULL,
"lock" character varying(5),
datum date,
zeit character varying(5),
sachbear character varying(20),
titel character varying(40),
standard character varying(1),
fr_prs numeric(10,2),
fr_mwst numeric(1),
hp_prs numeric(10,2),
hp_mwst numeric(1),
vp_prs numeric(10,2),
vp_mwst numeric(1),
s1_txt character varying(20),
s1_prs numeric(10,2),
s1_mwst numeric(1),
s2_txt character varying(20),
s2_prs numeric(10,2),
s2_mwst numeric(1),
s3_txt character varying(20),
s3_prs numeric(10,2),
s3_mwst numeric(1),
datum_1 date,
datum_2 date,
preis_01_t character varying(40),
preis_01_1 numeric(7,2),
preis_01_2 numeric(7,2),
preis_01_3 numeric(7,2),
preis_01_4 numeric(7,2),
preis_01_5 numeric(7,2),
preis_02_t character varying(40),
preis_02_1 numeric(7,2),
preis_02_2 numeric(7,2),
preis_02_3 numeric(7,2),
preis_02_4 numeric(7,2),
preis_02_5 numeric(7,2),
preis_03_t character varying(40),
preis_03_1 numeric(7,2),
preis_03_2 numeric(7,2),
preis_03_3 numeric(7,2),
preis_03_4 numeric(7,2),
preis_03_5 numeric(7,2),
preis_04_t character varying(40),
preis_04_1 numeric(7,2),
preis_04_2 numeric(7,2),
preis_04_3 numeric(7,2),
preis_04_4 numeric(7,2),
preis_04_5 numeric(7,2),
preis_05_t character varying(40),
preis_05_1 numeric(7,2),
preis_05_2 numeric(7,2),
preis_05_3 numeric(7,2),
preis_05_4 numeric(7,2),
preis_05_5 numeric(7,2),
preis_06_t character varying(40),
preis_06_1 numeric(7,2),
preis_06_2 numeric(7,2),
preis_06_3 numeric(7,2),
preis_06_4 numeric(7,2),
preis_06_5 numeric(7,2),
preis_07_t character varying(40),
preis_07_1 numeric(7,2),
preis_07_2 numeric(7,2),
preis_07_3 numeric(7,2),
preis_07_4 numeric(7,2),
preis_07_5 numeric(7,2),
preis_08_t character varying(40),
preis_08_1 numeric(7,2),
preis_08_2 numeric(7,2),
preis_08_3 numeric(7,2),
preis_08_4 numeric(7,2),
preis_08_5 numeric(7,2),
preis_09_t character varying(40),
preis_09_1 numeric(7,2),
preis_09_2 numeric(7,2),
preis_09_3 numeric(7,2),
preis_09_4 numeric(7,2),
preis_09_5 numeric(7,2),
preis_10_t character varying(40),
preis_10_1 numeric(7,2),
preis_10_2 numeric(7,2),
preis_10_3 numeric(7,2),
preis_10_4 numeric(7,2),
preis_10_5 numeric(7,2),
preis_11_t character varying(40),
preis_11_1 numeric(7,2),
preis_11_2 numeric(7,2),
preis_11_3 numeric(7,2),
preis_11_4 numeric(7,2),
preis_11_5 numeric(7,2),
preis_12_t character varying(40),
preis_12_1 numeric(7,2),
preis_12_2 numeric(7,2),
preis_12_3 numeric(7,2),
preis_12_4 numeric(7,2),
preis_12_5 numeric(7,2),
preis_13_t character varying(40),
preis_13_1 numeric(7,2),
preis_13_2 numeric(7,2),
preis_13_3 numeric(7,2),
preis_13_4 numeric(7,2),
preis_13_5 numeric(7,2),
preis_14_t character varying(40),
preis_14_1 numeric(7,2),
preis_14_2 numeric(7,2),
preis_14_3 numeric(7,2),
preis_14_4 numeric(7,2),
preis_14_5 numeric(7,2),
preis_15_t character varying(40),
preis_15_1 numeric(7,2),
preis_15_2 numeric(7,2),
preis_15_3 numeric(7,2),
preis_15_4 numeric(7,2),
preis_15_5 numeric(7,2),
preis_16_t character varying(40),
preis_16_1 numeric(7,2),
preis_16_2 numeric(7,2),
preis_16_3 numeric(7,2),
preis_16_4 numeric(7,2),
preis_16_5 numeric(7,2),
preis_mwst numeric(1),
CONSTRAINT test_preise_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE test.preise OWNER TO postgres;

Wild_Pointer · Post by **Wild_Pointer** » 31.12.2009, 11:15

I can confirm this behavior is also observed using Zeos 7 and Lazarus 0.9.28 beta, fpc 2.2.4 rev 21921 win32/win64.

Post by **mdaems** » 31.12.2009, 12:04

Is this a new problem since postgres 8.4? Or was it like that before and did the change you find a link to below break the code?
http://fisheye2.atlassian.com/changelog/zeos?cs=697

Mark

Wild_Pointer · Post by **Wild_Pointer** » 31.12.2009, 13:24

I've tested on postgresql 8.3.4 so I guess the problem is not related to server version (not 8.4 at least).

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

Hello,

now i have tested with 8.3 and 8.4 it´s the same. 9.5 -> 950

Post by **mdaems** » 31.12.2009, 13:42

My best guess is that you debug to see the result of TZPostgreSQLResultSet.GetString for these fields. I think the problem might be the decimal sign that's returned by the server. It should be the decimal point to be processed by the ZSysUtils.SQLStrToFloatDef function correctly.
Is it possible postgres returns a comma instead? This may depend on your system locale.

Mark

baba · Post by **baba** » 31.12.2009, 13:46

Hello,

pgAdmin shows 9.50.

I will check it with TZPostgreSQLResultSet.GetString.

baba · Post by **baba** » 31.12.2009, 13:54

Hello,

i need a litle help.

There is no "TZPostgreSQLResultSet."

??? TZPostgreSQLResultSet.GetString.

Post by **mdaems** » 31.12.2009, 13:59

Of course pgAdmin shows the right value.
Zeoslib should do the same! But doesn't because of some bug.
If your debugging shows the problem exists because the decimal separator returned by the database is a comma, we might try to find a solution by
- forcing postgres to send a decimal point to the zeoslib connection
- checking the connection's decimal separator and use it to decode the number strings.

Mark

Update : ZDbcPostgreSqlResultSet.pas contains the Getstring function. It's the function zeoslib internally uses to retrieve every field value from postgres.

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

Hello

this is my Code:

procedure TForm1.Button2Click(Sender: TObject);
begin
ZConnection1.Connect;

DataSource1.DataSet := ZQuery1;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('SELECT fr_prs FROM test.preise');
ZQuery1.Open;

//OK, RESULT: 950

ShowMessage(DecimalSeparator); // IS .
end;

Post by **mdaems** » 31.12.2009, 14:12

I'm on the forum chat right now. But a more normal insant messenger would be more practical.

Mark

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

Hello,

at this Moment, i install skype on my iPhone

Post by **mdaems** » 31.12.2009, 14:25

Skype not allowed from your desktop computer? In that case you could just use the forum chatbox. There's a link on the portal page and it uses simple html to communicate.

Mark