Jump to content
Mark Williams

FireDAC Array DML returning values from inserted records

Recommended Posts

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?

Share this post


Link to post
Posted (edited)

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 by J. Robroeks

Share this post


Link to post

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!

Share this post


Link to post

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.

Share this post


Link to post
20 minutes ago, Mark Williams said:

Sorry I misunderstood. What is SA?

SO, i mean your stackoverflow topic

 

Share this post


Link to post

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

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

×