FireDAC Array DML returning values from inserted records

[Mark Williams 14]

Using a Postgre database.

 

If I run an insert query with the following code:

#

With FDQuery Do
  begin        
    SQL.Text:='INSERT INTO temptab(email, name) '
     +'VALUES (''email1'', ''name1''), '
	 +'(''email2'', ''name2'') '
	 +'returning id';
	Open;
  end;
    

The query returns two records containing the id for the newly inserted records.

 

However, for larger inserts I want to use Array DML, but I also want to be get the returned data. Example code:

 With FDQuery Do
   begin
     SQL.Text:='INSERT INTO temptab(email, name) '
      +'VALUES (:EMAIL, :NAME) returning id';
      Params.BindMode:=pbByNumber; 
      Params[0].DataType:=ftString;
      Params[0].DataType:=ftString;
      Params.ArraySize:=2;

      Params[0].AsStrings[0]:='Email1';
      Params[1].AsStrings[0]:='Name1';
       
      Params[0].AsStrings[1]:='Email2';
      Params[1].AsStrings[1]:='Name2';
        
      Execute(params.ArraySize);
  end;

This returns no records. Not unsurprisingly as I am using the Execute command.

 

If I change the Execute command to Open, it inserts just the first record from the array and returns the id for that new record. 

 

The Open function does not allow you to specify the ArraySize.

 

I have tried to find some other procedure/property that will achieve this, but have drawn a blank.

 

Does anyone know whether it is possible to return data from all inserted records using Array DML? 

 

If it is not possible can anyone suggest an efficient way for returning the inserted data?

 

 There seems to be two alternatives to me, neither of which is particularly attractive:

 

1.  Run a query to return all records that match the email and name and then extract the relevant ids. In large tables this would doubtless result in a significant overhead.
2. Add an integer field to the table eg "temp_id". When inserting the data set this field  to a unique value for all records inserted for the DML Array transaction and then retrieve the newly inserted data using "temp_id". Again, this doesn't appear to me to be an attractive solution.

 

@Dmitry Arefiev If this is not currently possible with Array DML would you please consider including it for later versions?

[J. Robroeks 1]

I was encountering an issue that is related to this topic:

• Inserting a parent record, with a FK to a newly inserted child record. 
• Both the parentid as well as the childid are needed for further processing
• Array DML is needed as the inserts can reach up to 30.000 parent records (single inserts take 1 min. while a DML query takes 1s) 

 

There is no way to retrieve the returning ID's when performaning a array DML with firedac. (If I'm wrong, please let me know). But this can achieved with simple SQL:

 

 

In case the returning ID value is only needed to relate the child record to the parent record then the following does the trick:

 

with rows as (insert into parent DEFAULT VALUES returning parentid)
INSERT INTO child (parentid) 
SELECT
parentid
FROM rows

In case the returning ID value is needed, then you can solve this with a temporary table and select the records you need. You can leverage options that PG offers such as upserts, unique etc. Also make sure to drop the temp table (or use DROP ON COMMIT) and set the cursorkind of the FDQuery to default.

 

with rows as (insert into parent DEFAULT VALUES returning parentid)
INSERT INTO temp_table (parentid) 
SELECT
parentid
FROM rows

 

 

 

Edited April 6, 2021 by J. Robroeks

[Mark Williams 14]

It's a bit frustrating and I have also posted on Stackoverflow, but nothing has come back.

 

I have logged a feature request at Quality Portal. I would give you the link, but for some reason my log in details no longer work. If you could comment on that to lend support that would be great. That's assuming you know how to get in to it.

 

I tried today, but my details no longer work!

[J. Robroeks 1]

Do you have a link to SA? I'll post my answer there as well. That might be useful for others. 

 

I'll check the quality portal. Returning values in DML's would be neat.

[Mark Williams 14]

http://FireDAC Array DML and Returning clauses - Stack Overflow

[mvanrijnen 123]

3 hours ago, Mark Williams said:

http://FireDAC Array DML and Returning clauses - Stack Overflow

this links to this topic instead of SA  🙂

[Mark Williams 14]

Sorry I misunderstood. What is SA?

[mvanrijnen 123]

20 minutes ago, Mark Williams said:

Sorry I misunderstood. What is SA?

SO, i mean your stackoverflow topic

 

[Mark Williams 14]

Sorry, I though I had posted the SO link. It is

 

FireDAC Array DML and Returning clauses - Stack Overflow

[EgonHugeist 3]

@Mark Williams

Might be OT because you refering FireDac. So just for the record: I added that feature to Zeos V8. See: Add ArrayDML returning feature for Postgres

Chears, Michael

Edited April 9, 2021 by EgonHugeist
Typos

[Mark Williams 14]

Good to know thanks, but would be a bit of a major shift for me from FireDac to Zeos