CODEWITHSUNDEEP
delphifiredac
Shea
Shea

Reputation: 23

null converting to 0.00 delphi

I inherited a program that copies all the info from one DB table into a different DB. The program was written in delphi 7 i believe and was using IDAC. Once I got it I converted updated it to Delphi 10.1 and moved it over to use FireDac. The issue I am having is in the original table it has fields with null values. When I move it over to the other DB it converts it from a null to 0.00. In the original program this did not happen and I cannot find anything in the code to tell it to do this. Does anyone have any idea how to have it insert the null instead of converting it.

Upvotes: 0

Views: 1828

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

Change all the references of (SomeField).Value to (SomeField).AsVariant in your code. Because in FireDAC .Value is converted to integer/float/string/... (as .AsInteger did in Delphi 7), so your null values are converted to 0.00.

@MartynA's code will now be :

 var
    SourceField,
    DestField : TField;
    i : Integer;
  begin
    [...]
    for i := 0 to SourceTable.FieldCount - 1 do begin
      SourceField := SourceTable.Fields[i];
      DestField := DestTable.Fields[i];
      DestField.AsVariant := SourceField.AsVariant;
    end;
    [...]
  end;

And any further access to the fields values do it through the .AsVariant method, so null values are not read as 0.

Upvotes: 0

MartynA
MartynA

Reputation: 30735

Somewhere in your (or FireDAC's) code, the field's value is being handled as an integer-type value.

You can avoid this behaviour by doing a field-by-field copy along the following lines:

  var
    SourceField,
    DestField : TField;
    i : Integer;
  begin
    [...]
    for i := 0 to SourceTable.FieldCount - 1 do begin
      SourceField := SourceTable.Fields[i];
      DestField := DestTable.Fields[i];
      if SourceField.IsNull then
        DestField.Clear  //  Sets DestField to Null
      else
        DestField.Value := SourceField.Value;
    end;
    [...]
  end;

This assumes that the source- and destination-tables have the same structure, of course and that the fields are all non-blob types. Any blob field needs to be copied by the field's calling LoadFromStream and SaveToStream methods.

Upvotes: 2

Related Questions