Returning ids for records inserted with Array DML

[Mark Williams 14]

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 March 22, 2021 by Mark Williams
Typos

[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 March 22, 2021 by Guest

[Mark Williams 14]

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.

[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 March 22, 2021 by Guest

[Mark Williams 14]

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.

[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 March 22, 2021 by Guest

[Mark Williams 14]

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?

[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 March 22, 2021 by Guest

[Mark Williams 14]

OK. I'm a little confused, but thanks for the help.