Setting double value to NULL in Database using Delphi / FireDac-CodePudding

I am reading and writing data into an Microsoft Access database using Delphi 11.2 and FireDac. I'd like to be able to set numeric values in the database to NULL based on the (double) value in delphi. I am currently reading values as strings from the DB

myQuery.FieldByName('myNumericField').AsString

into my delphi object fields and set double values on application level to NAN if I find empty strings. When I write them back to the DB and the user has not set the value on application level the value is obviously set to NAN in the DB as well, but not to NULL, which would be preferred.

Does anybody know a feasible way to set double / numeric fields to NULL in an (microsoft Access or presumably any other) database via firedac?

Help is much appreciated!

Simplified code samples for update and insert I am currently using

update:

dbMain.ExecSQL('update MyTable set '   'myNumericField = :P1 '   'where myIDField = :P2', [myDataObject.myDoubleValue, myDataObject.myId]);

insert:

 dbMain.ExecSQL
      ('insert into MyTable(myIDField, myNumericField) '
        'values(:P1, :P2)',
      [myDataObject.myId, myDataObject.myDoubleValue]);

CodePudding user response：

Try something like this:

var
  varValue: Variant;

if IsNan(myDataObject.myDoubleValue) then
  varValue := Null
else
  varValue := myDataObject.myDoubleValue;

dbMain.ExecSQL('update MyTable set myNumericField = :P1 where myIDField = :P2', [varValue, myDataObject.myId]);

Alternatively:

if IsNan(myDataObject.myDoubleValue) then
  dbMain.ExecSQL('update MyTable set myNumericField = NULL where myIDField = :P1', [myDataObject.myId])
else
  dbMain.ExecSQL('update MyTable set myNumericField = :P1 where myIDField = :P2', [myDataObject.myDoubleValue, myDataObject.myId]);

Alternatively:

var
  Params: TFDParams;
  Param: TFDParam;

Params := TFDParams.Create;
try

  Param := Params.Add('P1', ftFloat);
  if IsNan(myDataObject.myDoubleValue) then
  begin
    Param.Bound := True;
    Param.Clear;
  end else
    Param.Value := myDataObject.myDoubleValue;

  Params.Add('P2', myDataObject.myId);

  dbMain.ExecSQL('update MyTable set myNumericField = :P1 where myIDField = :P2', Params);
finally
  Params.Free;
end;