I have been using outsystem to update various databases in my organisation for a while. I would typically use an aggregate/sql query then loop it with a for each then change something to each row according and update the database within the loop. something like thisHowever one issue i encountered is that if there is an error mid way through the loop, the changes that was suppose to be done into the database with the rows before the row with issue will not be committed. is there a better way of updating the DB using loop that will allow it to commit the changes that has been done even if it were to run into an error down the loop?
Hi @Clifford,You can get some ideas by going through this Link.Please let me know if you have any queries.Regards,Anubhav
Hi,
There are several options for dealing with it. Using a single record update and commit is inefficient in terms of CPU utilization. You could try an advanced query.
There are also commit and bulk insert options available.
1. Within your loop, use manual commit to commit each and every record.
2. Use of BulkInsert component. A single call can insert multiple records.
There are also some similar posts on the forum:
https://www.outsystems.com/forums/discussion/72746/how-to-update-multiple-records-on-a-single-click/
https://www.outsystems.com/ideas/554/create-bulk-update-bulk-insert-and-bulk-delete-widgets-for-sql-ops/
Thanks,
Narendra
Hello,
If you are not updating something different every time you can SQL query to update bulk of rows using a specific where condition based on your business scenario as using loop with update statement is not a best practice but if you don't have any other option you can do it.
You can use CommitTransaction from System module to commit your changes explicitly as OutSystems in your implementation commit all changes at the end of action and in case any error happened it will rollback all previous changes that not committed.
You can use commit transaction inside your loop after every update statement which is not recommended or you can do a threshold for example if you updating 1000 rows you threshold can be 100 and create index which you will increment after every update statement and once index equal to threshold you can use commit to commit previous 100 rows then reset index and start increment again.
This way will require to mark updated rows as its updated or save number of rows committed so that in case any error happened you will start from beginning again and this may case data consistency issue so you should start from last committed threshold (for example you successfully did 3 commits and your threshold is 100 so you will start loop in second time from 300)