I'm developing an app which generates SQL which in turn i want to use to query the retool postgresql db.
So for example, where SELECT * FROM Sales; is generated, i capture that as a variable and want to then pass it into a query and execute.
Issue is, i can't find a way to do that.
I've turned off prepared statements btw, but can't fathom out how to inject the SQL generated into a query using JS.
Thanks for any help!
@Dave_AIMM Welcome to Retool!
We utilize a version of what you are asking for that runs a bit like this (uses a generated SQL statement and then executes it as part of a CRUD operation):
generateCreateStatement.data is a JS query, and we pass the results along to the DB via EXEC. This particular operation goes into a on-prem SQL database.
A more complex delete statement using the same principle:
My colleague did a bit of a write-up on our generic CRUD implementation that fleshes this out a bit more (Do I need separate queries...), however I think you just need these building blocks to get started.
Thank you so much @pyrrho, really appreciate the in depth and fast response.
This makes a lot of sense, I'll give it a bash and let you know how I get on.