I currently have a large number of input components (around 150) spread out across several pages. I need to write the data into a Postgres database where the data is split into about 15 tables and linked together with a unique reference number.
My current solution is to have an update / add new query for each table as well as a delete query for each table. The issue is that if one query throws an error, the rest of that data will still get written causing there to be incomplete data in the DB. Is there a better way to handle this?
Hi @robinyoublind!
I'll gladly assist you with this!
Hm, I'd need a bit more context for your setup! As a first step, would chaining the queries on success of the others via on success event handlers on those queries help? Please let me know!
Facing the same issue where i have to update multiple tables at once. Seems the only method is to create a query per table. Require a better solution. It would be usefull to be able to use addtionalScope to pass the table name in the resource query as this isn't supported
However, we generally discourage this because disabling prepared statements is a security concern. By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup (see screenshot below), but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
