Big problem when updating '0'-values on DB field DECIMAL(14,6)

[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

[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

[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.

[marsupilami]

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.

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.

[Fr0sT]

CodeTyphon is nothing more than pre-configured Lazarus in fact

[MacWomble]

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.

[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?