Jump to content
bazzer747

Assigning Null value to Parameter

Recommended Posts

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.

 

Share this post


Link to post

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

Edited by Stano

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post

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.

Share this post


Link to post

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 by Serge_G

Share this post


Link to post

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..

Share this post


Link to post

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.

Share this post


Link to post
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.

Share this post


Link to post

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"

image.thumb.png.54a364bf9e2395f0b6dc4898c6f61c96.png

 

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 by emailx45

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×