Good day.
I have this code in a JS Query:
updateInvoiceLineDetail.trigger(
{
additionalScope: { queryString: queryString }
}
);
I have this updateInvoiceLineDetail query:
UPDATE invoices SET {{ queryString }};
I get this error when the query is triggered:
In the debugger query property, I only see:
UPDATE invoices SET
which I expect since the queryString variable has no value in the global context.
What am I doing wrong? Thanks.
Good day, I debug it, seem the additionalScope is not added to body of SQL query body. I have tag this topic as feature request.
I'm sorry. I don't understand.
Hello, In your case the queryString variable should be resolved to the string you added in additionalScope, but it not, it was resolved to an empty string "".
Part of your issue is with proper use of parameterized queries.
You have to do something like set myfield={{passedParameter}}. You cannot just do set {{passedParameter}} if passedParameter is 'myfield=12'. $1 is the variable for the parameter.
Retool has to be able to tell what is the field and what is the value to properly create the query to send to the database.
Thank you for the guidance. I obviously don't understand how to take the data from a form and do an insert or update to the database. There must be a clean, easy way to do this in Retool that I have not discovered. If anyone has guidance, please reply.
I'm also wondering if there is a way to build a SQL statement in JS and submit it to the database.
Thanks.
There definitely are ways to do this cleanly and there are built-in ways in the Retool query editor that do insert/update/delete/upsert methods without having to worry about parameterised queries or escaping quotes etc (pic below)
I think what would help is understanding why you need to pass an additional parameter to a query string to build a SQL statement when the query appears to be a regular "update" query that you could use the existing Retool query build "in GUI mode" for.
A fair question. Thanks for taking the time to respond to my post.
Three reasons, but these could be a result of my lack of experience with Retool.
The form has fields (e.g.,. a hidden UUID) that are in form.data that cannot or should not be updated. So, I think I need to manipulate the form.data object to remove those fields before submitting the query.
I need to inspect or manipulate the data before the update to do complicated validation, check for potential duplicates in the DB, update other tables, etc.
I also will have the need to compose SQL queries in code and submit them to the DB. I realize that Retool is very different from Wordpress, but WP offers a good example of what I think I need to do. In WP, I can compose a complete SQL query in code and submit it to the DB through WP_Query() and get back a code indicating failure or the result of the query as an object.
Thanks much for your thoughts on this.
These are all valid points and makes perfect sense - getting used to "the Retool way" of working is always going to involve a learning curve from whatever system you are used to but I would say that the approach is much more safety focussed and easier to maintain in the long term.
For 95% of use cases I find I don't write SQL for CRUD operations.
To your points I would suggest you might want to consider a transformer function to "clean" the form data and produce the object you want to use for updates - this would give you an opportunity to build the correct data structure, remove unwanted fields, add default values etc
If you're doing all you validation checks before calling the update/insert query then you can use the output of the transformer as the recordset object too and don't need to pass additionalScope objects around.
form -> submit -> validate -> format data -> run query
Brilliant. Just to be clear I understand:
Write a transformer to mirror the form.data object and then modify as needed.
Submit the modified object to a GUI Mode created Upsert query.
Thank you very much!
