Page 1 of 1
Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 08.09.2019, 12:05
by MacWomble
I found a big problem with ZEOS in Source SVN Rev 5737 - SVN Rev 5894:
On my form there are 4 TEdits.
When I open the record I see the all stored data.
There is one field with value 0.
When I edit and post the field swith 0 are given empty from post.
e.g. post generates
UPDATE price SET price_one=1.23,price_two=,price_three=,price_four=2.34 WHERE ID = 1
when price_two and price_three had value 0 in TDBEdit.
Only tested on Decimal-fields! Maybe the error is also present on other numeric fields.
With older ZEOSLib it works as expected:
UPDATE price SET price_one=1.23,price_two=0,price_three=0,price_four=2.34 WHERE ID = 1
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 09.09.2019, 20:38
by marsupilami
Hello MacWomble,
I tried to reconstruct your case. Unfortunately in my Testcase everything works without raising an exception. Currently my testcase looks like this:
Code: Select all
procedure TZTestCompMySQLBugReport.TestTicketXXX;
var
Query: TZQuery;
begin
Connection.Connect;
Connection.ExecuteDirect('insert into ticketxxx (id, num, deci) values (1, 1, 1)');
try
Query := CreateQuery;
try
Query.SQL.Text := 'select * from ticketxxx';
Query.Open;
try
Query.Edit;
Query.FieldByName('num').Clear;
Query.Post;
Query.Edit;
Query.FieldByName('deci').Clear;
Query.Post;
finally
if Query.State in [dsEdit, dsInsert]
then Query.Cancel;
Query.Close;
end;
finally
FreeAndNil(Query);
end;
finally
Connection.ExecuteDirect('delete from ticketxxx');
end;
end;
tixketxxx is defined like this:
Code: Select all
create table ticketxxx (
id integer not null,
num numeric(16,4),
deci decimal(16,4),
primary key (id)
);
Note: This is with Zeos 7.3. I assume, you are using Zeos 7.2? I only saw that by digging into the Zeos revisions. Please always tell us which version of Zeos (7.2, 7.3, ...) you are using.
Revision 5737 seems to be quite outdated. Could you please check if the problem also exists in a current revision?
Best regards,
Jan
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 09.09.2019, 22:37
by MacWomble
Sorry, I gave wrong information in my first post. I did not use TDBEdit. My fields are TEdit.
The part of the code where the problem occure:
Code: Select all
with qryPreis do
begin
dsPreis.Edit;
FieldByName('prs_zeit').AsFloat := StrToFloat(F.edZeit.Text);
case F.cmbBruttoNetto.ItemIndex of
0:
begin
FieldByName('prs_lohn').AsFloat := StrToFloat(F.edLohn.Text) / 1.19;
FieldByName('prs_service').AsFloat := StrToFloat(F.edService.Text) / 1.19;
FieldByName('prs_material').AsFloat := StrToFloat(F.edMaterial.Text) / 1.19;
FieldByName('prs_geraet').AsFloat := StrToFloat(F.edGeraet.Text) / 1.19;
FieldByName('prs_fremdleistung').AsFloat := StrToFloat(F.edFremdleistung.Text) / 1.19;
FieldByName('prs_ek').AsFloat := StrToFloat(F.edEKPreis.Text) / 1.19;
end;
1:
begin
FieldByName('prs_lohn').AsFloat := StrToFloat(F.edLohn.Text); //=0
FieldByName('prs_service').AsFloat := StrToFloat(F.edService.Text); //=0
FieldByName('prs_material').AsFloat := StrToFloat(F.edMaterial.Text); //=12.50
FieldByName('prs_geraet').AsFloat := StrToFloat(F.edGeraet.Text); //=0
FieldByName('prs_fremdleistung').AsFloat := StrToFloat(F.edFremdleistung.Text); //=0
FieldByName('prs_ek').AsFloat := StrToFloat(F.edEKPreis.Text); // =8.70
end;
end;
end;
if dsPreis.State in dsEditModes then
qryPreis.post; // Here ZEOS generates a wrong update expression with replacing 0 with NIL
I use CodeTyphon from PilotLogic:
The problem is not with ZEOSLib Ver 6.8.1 Date 08-02-2019 SVN trunk Rev 5541 but with later Revisions including Ver 6.9.2 Date 02-09-2019 SVN trunk Rev 5894.
So I will try to get the newest ZEOS-Lib 7.3 and test again.
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 10.09.2019, 07:32
by marsupilami
MacWomble wrote:
I use CodeTyphon from PilotLogic:
The problem is not with ZEOSLib Ver 6.8.1 Date 08-02-2019 SVN trunk Rev 5541 but with later Revisions including Ver 6.9.2 Date 02-09-2019 SVN trunk Rev 5894.
Sorry - but we cannot support CodeTyphon. They are a moving target and we don't have thr ressources to do that. There never were Zeos versions 6.8.1 or 6.9.2. Also I usually don't know what FPC, Lazarus and Zeos versions they ship.
MacWomble wrote:So I will try to get the newest ZEOS-Lib 7.3 and test again.
It would be good, if you could test with a stable release of Lazaruz and FPC.
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 10.09.2019, 07:58
by Fr0sT
CodeTyphon is nothing more than pre-configured Lazarus in fact
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 10.09.2019, 08:23
by MacWomble
Fr0sT wrote:CodeTyphon is nothing more than pre-configured Lazarus in fact
CT is much more than a preconfigured Lazarus. There are many changes and extensions in the IDE.
So I can understand you wont support CT but Lazarus and I try to solve my problem with PilotLogic.
Thank you for your informations.
There never were Zeos versions 6.8.1 or 6.9.2
That's right. both are ZEOS 7.2 revisions. The Rev-No. is the ZEOS revision.
Re: Big problem when updating '0'-values on DB field DECIMAL(14,6)
Posted: 09.10.2019, 03:52
by EgonHugeist
Hello MacWomble,
As Jan said we currently do not have the man-power to support CT directly. So you're welcome here in the forum.
I know about some FPC bugs with the FmtBCD fields:
1. FPC can't convert the FmtBCD value into a (U)Int64 even if scale is zero and the value would fit into a int64.
2. FPC converts the Doubles/Extendeds using a conversion with a string. If the String contains an Exponent value the TryStrToBCD conversion fails and the Field contains a null value now.
For those fields my advice would be not using Float's, just strings.
But the 14,6 value has a BCD (Currency) range and shouldn't make trouble.
Is the Field a TBCDField?
Is the problem still present?