Assigning Null value to Parameter

[bazzer747 25]

Hi

I'm struggling to enter a Null value into a field with a Firedac ExecSQL Update. 

My code to update a record looks like this:

 

  dm.fdc.ExecSQL(
  'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr, Competition = :pCo ' +
  ' WHERE ID = :pID',
 [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), Null, iMatchID ]);

 

'dm.fdc' is the connection in a Data Module (dm).

The 'Competition' field is not filled in by the User so needs to be assigned a Null value. Adding a new record does this by default but if the user has added a value and then wants to remove it I need to assign a Null back to the field.

 

The above code has worked but at other times gives an error message:

..' data type is unknown... Specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call.' But what DataType is Null and how does that code look?

 

I've searched for how to do this but can't find a clear result. I've seen that you can assign Null to a field using 'Clear', but how does this get implemented. I've tried

dm.fdcCalendar.Edit;

dm.fdcCalendar.FieldByName('Competition').Clear;

dm.fdcCalendar.Post;

 

And that gives an error - '..update table does not have a PK or row identifier, record has been changed/deleted by another user' which is of little or no help as there is no other user as I'm testing.

 

The Embarcadero wikipedia help is of little help, fofr some reason it refrains from giving code examples on most items I look up. And other sources, whilst nearly echoing what my issue is aren't quite the same issue.

 

[Stano 143]

xx.ParamByName („Konkurencia“). Hodnota: = null;

Edited December 30, 2020 by Stano

[bazzer747 25]

Yay. My code now looks like this:

  dm.fdc.ExecSQL(
    'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr WHERE ID = :pID',
    [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), iMatchID ]);

 

     dm.fdqvCalendar.Edit;
     dm.fdqvCalendar.FieldByName('Competition').Value:= null;  //To set the field to Null
     dm.fdqvCalendar.Post;

 

And this works without any error.

 

I've dropped the Competition field and parameter from the ExecSQL but other fields may well be updated, and then apply the Edit/Post afterwards and this works OK. So many thanks for the quick response.

 

I suppose it would be 'cleaner' if I couldd apply a Null value withing the ExecSQL statement, but, ,hey, if it works I'm  happy with that.

[Stano 143]

Do not use direct assignment to a field! Always use a parameter. Due to SQL injection

[bazzer747 25]

Hi

 

Yes, I agree. That's why I always use parameters in my ExecSQL statements. My problems with the ExecSQL is that I don't know how to assign a null value in the statement.

[Serge_G 87]

Try this
 

dm.fdc.ExecSQL(
  'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr, Competition = NULL ' +
  ' WHERE ID = :pID',
 [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text),  iMatchID ]);

And for setting a Field to null

dm.fdqvCalendar.FieldByName('Competition').Clear; 

 

Edited December 30, 2020 by Serge_G

[bazzer747 25]

Ah,

 

Isn't this what Stano says - don't uuse direct assignment to a field because of possibility of SQL injection?

 

Also, I did try the .Clear setting but it didn't work, as I mentioned earlier..

 

Thanks for responding..

[Lars Fosdal 1792]

We don't allow direct insert/update/delete SQL in our DB schemas, but always use stored procs to do that. 

Unit tests ensure that parameterisation to these calls has been done properly.

[Uwe Raabe 2057]

1 hour ago, Stano said:

Do not use direct assignment to a field! Always use a parameter. Due to SQL injection

That can't happen using this approach. Internally the Post executes a parametrized UPDATE statement, which is exactly what you suggest.

[Guest]

hey Guys

 

maybe you using the "MACRO" replacement by FireDAC engine would be cool in this task:

 

my sample using "SELECT" and with/without a conditional "WHERE"

 

HELP SYSTEM:  sample, then, just "PREPARE" the FDQuery with your conditional, your "xFIELD = NULL" -- some like this: ... {if !xCond} !xCond {fi}

• FireDAC.Comp.Client.TFDCommand.Macros  topic!

//Example 1 - Substitute table name:
ADCommand1.CommandText.Text := 'select * from &Tab';
ADCommand1.Macros[0].AsIdentifier := 'Order Details';
ADCommand1.Prepare;
ShowMessage(ADCommand1.SQLText); // select * from "Order Details"

//Example 2 - Substitute WHERE condition:
ADCommand1.CommandText.Text := 'select * from MyTab {if !cond} where !cond {fi}';
ADCommand1.Macros[0].AsString := 'ID > 100';
ADCommand1.Prepare;
ShowMessage(ADCommand1.SQLText); // select * from MyTab where ID > 100
ADCommand1.Macros[0].Clear;
ADCommand1.Prepare;
ShowMessage(ADCommand1.SQLText); // select * from MyTab

 

hug

Edited December 30, 2020 by Guest