FireDac Array DML Update query - omitting certain fields

[Mark Williams 14]

I'd like to use Array DML to update a table, however, I don't want to update all fields every time. In some instances I would like to leave the existing database value untouched for a particular record.

 

In a normal update query I would do something like "UPDATE persons set name=name". I can't see how it's possible to do that for Array DML.

 

      SQL.Text :='UPDATE persons SET name=:NAME, email=:EMAIL WHERE id=:ID';

      Params[0].DataType:=ftString;
      Params[1].DataType:=ftString;
      Params[2].DataType:=ftLargeInt;


      Params.ArraySize:=2;

      Params[0].AsStrings[0]:='Delphi';
      Params[1].AsStrings[0]:='praxis';
      Params[2].AsLargeInts[0]:=794;

      Params[0].AsStrings[1]:='Mark';      
      Params[2].AsLargeInts[1]:=795;

      Execute(Params.ArraySize);

The first set of values is intended to overwrite name and email fields.

 

The second set of value is intended to overwrite just the name field and leave the email field as is in the database. I thought (but not with much optimism) that not seeing the parameter for the email field may leave it as is. In fact it sets the value to an empty string.

 

I can't see any method or property of TFDParam that seems to fit the bill. Was hoping there might be an "AsIs" property, but sadly not.

 

I'm sure it can't be the case that I would need to submit multiple array DML's depending on which values I want to change and which ones I want to leave untouched.

 

[weirdo12 21]

Why not just supply the current value where you don't want a value to change?

[Brian Evans 109]

Null parameters and COALESCE () in the UDPATE statement would be one way.

SQL.Text :='UPDATE persons SET name=COALESCE(:NAME,name), email=COALESCE(:EMAIL,email) WHERE id=:ID'; 

 

[Mark Williams 14]

22 hours ago, weirdo12 said:

Why not just supply the current value where you don't want a value to change?

Possible, but I'm posting data to a server which then submits the query and it could be an awful lot of unnecessary data to post.

[Mark Williams 14]

13 hours ago, Brian Evans said:

Null parameters and COALESCE () in the UDPATE statement would be one way.

SQL.Text :='UPDATE persons SET name=COALESCE(:NAME,name), email=COALESCE(:EMAIL,email) WHERE id=:ID'; 

 

You're a genius. I'm an idiot. Why didn't I think of COALESCE. Thanks

[weirdo12 21]

18 hours ago, Mark Williams said:

You're a genius. I'm an idiot. Why didn't I think of COALESCE. Thanks

😄 So just supply the current value when the value doesn't change. I do like the statement that uses COALESCE because it makes it very obvious that that is what is happening.

 

Are you targeting a specific database server?

Edited February 14 by weirdo12

[Mark Williams 14]

22 hours ago, weirdo12 said:

😄 So just supply the current value when the value doesn't change. I do like the statement that uses COALESCE because it makes it very obvious that that is what is happening.

Yes. As I explained that would work, but I would have to submit a mass of additional data to my server that is unnecessary if you use COALESCE.

 

22 hours ago, weirdo12 said:

Are you targeting a specific database server?

No. Trying to write it as flexible as poss

[weirdo12 21]

If you want to be as flexible/portable as possible you could consider using a FireDAC macro like this:

 

email = { fn IFNULL(:EMAIL, email) }

Edited February 15 by weirdo12