Jump to content
Mark Williams

Returning ids for records inserted with Array DML

Recommended Posts

FireDAC Array DML doesn't seem to be usable in any practical sense with Returning clauses FireDAC Array DML returning values from inserted records.

 

That being so, I am trying to work out whether there is an efficient way I can retrieve data from inserted records using Array DML.

 

The following seems a possibility and is lightening fast, but I am concerned that I may be missing something critical:

 

  1. Insert required no of new records using Array DML within a StartTransaction/Commit section.
  2. Check the number of affected rows to make sure that all required records have been inserted. If not, then rollback.
  3. Obtain the last insert id for the target table for the current session.
  4. Work backwards through the array of inserted data in my app and assign the id by decrementing the last id.

 

My initial testing suggests that this works. But it depends on the following assumptions being correct (which I believe they are):

 

  1. The RowsAffected property of TFDQuery is wholly reliable.
  2. My DB (in this case PostgreSQL) will always reliably return the correct last insert id for my table in my current session.
  3. My DB will insert all my records in the same order as they are added to the TFDQuery params list.
  4. My DB will insert the records in an unbroken sequence of IDs.

 

So my questions

  1. Are my assumptions sound? 
  2. Am I missing something else?

 

 

 

 

Edited by Mark Williams
Typos

Share this post


Link to post
Guest

I dont know nothing about PostGreSQL, I never used it, but if you dont trusth in ArrayDML returning, then, you can do:

 

1) Simple way: use the AfterPost event from FDQuery/anyDataset to feed a Array with the ID inserted in your table.

 

2) more efficient way: create a StoredProcedure to use in your Table-Events (Inserting) to store the IDs and on the end return it all - you can have a SP generic that can used for any Table in your DB.

 

but, I believe that ArrayDML return would be enought for that.

 

hug

Edited by Guest

Share this post


Link to post

Thanks for the suggestions.

 

Just to clarify 

1 hour ago, emailx45 said:

 

but, I believe that ArrayDML return would be enought for that.

By that, do you mean that you consider the method I proposed in my original post would be reliable? If so, that would be the quickest and easiest route to go.

Share this post


Link to post
Guest

hi @Mark Williams 

 

look, the real use for RowsAffected:

Quote

 


After Array DML execution, the property RowsAffected has the number of successful executions, not the total number of affected rows by all executions.
 

 

 

and PostGreSql affected:

PostgreSQL v < 8.1 Emulation aeUpToFirstError  
PostgreSQL v >= 8.1 Native (INSERT /MERGE with multiple VALUES) aeOnErrorUndoAll  

 

http://docwiki.embarcadero.com/RADStudio/Sydney/en/Array_DML_(FireDAC)

 

maybe you dont saw this info, I can think?

see On ErrorTFDAdaptedDataSet.OnExecuteError use, too.

hug

Edited by Guest

Share this post


Link to post
22 minutes ago, emailx45 said:

the number of successful executions, not the total number of affected rows by all executions.
 

But for an insert procedure would not the number of successful executions be the number of new rows inserted?

 

That certainly seems to to be the case from the testing I have carried out so far.

Share this post


Link to post
Guest

this because NONE ERRORS OCCURRED.

see options if any errors occurs... you can "pass it" or "break your executions".

please read your help!!!

 

hur

Edited by Guest

Share this post


Link to post
1 minute ago, emailx45 said:

his because NONE ERRORS OCCURS.

OK. As mentioned in original post if RowsAffected is less than the number of inserted rows I would expect I rollback the transaction (and handle it as an error).

 

It is only if the RowsAffected = the number of inserted rows expected that I would use the approach in my original post.

 

Taking that into account, do you think it is safe to do so?

Share this post


Link to post
Guest

a rolllback "implicit" is done if you "pass any error in onerror event".

a explicit rollback would do "all revertion" ... then there is here a difference you see?

for rest, nothing is really secure, for that exist engines for each actions and reactions.

 

hug

Edited by Guest

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

×