Is there a way to INSERT Null value as a parameter using FireDAC?

Question

I want to leave some fields empty (i.e. Null) when I insert values into table. I don't see why would I want to have a DB full of empty strings in fields.

I use Delphi 10, FireDAC and local SQLite DB.

Edit: Provided code is just an example. In my application values are provided by user input and functions, any many of them are optional. If value is empty, I would like to keep it at Null or default value. Creating multiple variants of ExecSQL and nesting If statements isn't an option too - there are too many optional fields (18, to be exact).

Test table:

CREATE TABLE "Clients" (
    "Name"  TEXT,
    "Notes" TEXT
);

This is how I tried it:

var someName,someNote: string;
begin
{...}
someName:='Vasya';
someNote:='';
FDConnection1.ExecSQL('INSERT OR REPLACE INTO Clients(Name,Notes) VALUES (:nameval,:notesval)',
    [someName, IfThen(someNote.isEmpty, Null, somenote)]);

This raises an exception:

could not convert variant of type (Null) into type (OleStr)

I've tried to overload it and specify [ftString,ftString] and it didn't help.

Currently I have to do it like this and I hate this messy code:

FDConnection1.ExecSQL('INSERT OR REPLACE INTO Clients(Name,Notes) VALUES ('+
    IfThen(someName.isEmpty,'NULL','"'+Sanitize(someName)+'"')+','+
    IfThen(someNote.isEmpty,'NULL','"'+Sanitize(someNote)+'"')+');');

Any recommendations?

Edit2: Currently I see an option of creating new row with "INSERT OR REPLACE" and then use multiple UPDATEs in a row for each non-empty value. But this looks direly ineffective. Like this:

FDConnection1.ExecSQL('INSERT OR REPLACE INTO Clients(Name) VALUES (:nameval)',[SomeName]);
id := FDConnection1.ExecSQLScalar('SELECT FROM Clients VALUES id WHERE Name=:nameval',[SomeName]);
if not SomeString.isEmpty then
    FDConnection1.ExecSQL('UPDATE Clients SET Notes=:noteval WHERE id=:idval)',[SomeNote,id]);

You need to provide more explanation of what you are trying to achieve: If you want to add a row with its Notes column set to Null, why not simply omit Notes from the list of fields, i.e. do Insert into Client(Name) Values()? And why are you trying to insert rows with the Name coliumn set to Null? — MartynA
Variable may or may not be an empty string. Code I've provided is just an example, in my real program I have about 20 fields that I recieve from controls on form and various functions. — Alexander
Your real problem is IfThen function that doesn't work with variants. You use the version form System.StrUtils and the Null value thet you pass as the second argument gets implicitly converted to string which causes the exception. To overcome that, you can write your own routine to convert an empty string to Null variant: function StrToVar(const S: String): Variant; begin if S = '' then Result := Null else Result := S; end;. — Peter Wolf
Hi Alexander. Actually you can have FireDAC handle this specific problem for you. You can set up the connection so that empty strings are converted to NULL when inserting or updating the database. Have a look at docwiki.embarcadero.com/Libraries/Rio/en/… — Frazz

mrNone mrNone · Accepted Answer · 2020-07-18T13:18:44

According to Embarcadero documentation ( here ):

To set the parameter value to Null, specify the parameter data type, then call the Clear method:

with FDQuery1.ParamByName('name') do begin
  DataType := ftString;
  Clear;
end;
FDQuery1.ExecSQL;

So, you have to use FDQuery to insert Null values, I suppose. Something like this:

//Assign FDConnection1 to FDQuery1's Connection property
FDQuery1.SQL.Text := 'INSERT OR REPLACE INTO Clients(Name,Notes) VALUES (:nameval,:notesval)';
with FDQuery1.ParamByName('nameval') do
  begin
  DataType := ftString;
  Value := someName;
  end;
with FDQuery1.ParamByName('notesval') do
  begin
  DataType := ftString;
  if someNote.IsEmpty then
    Clear;
  else
    Value := someNote;
  end;
if not FDConnection1.Connected then
   FDConnection.Open;
FDQuery1.ExecSql;

It's not very good idea to execute query as String without parameters because this code is vulnerable to SQL injections.

Some sources tells that it's not enough and you should do something like this:

with FDQuery1.ParamByName('name') do begin
  DataType := ftString;
  AsString := '';
  Clear;
end;
FDQuery1.ExecSQL;

but I can't confirm it. You can try it if main example won't work.

Is there a way to INSERT Null value as a parameter using FireDAC?

1 Answers