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

user_13952888 0
Viewed 1069

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]);
firedac delphi sqlite
1 Answers

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.

user_139116960
Related