I need some help with an unusual problem. The thing is that I want to pass a text input to an action and put this input as a SQL query and then execute it. The main problem is that when I test some arguments like: "select {Contrato}. * from {Contrato} where Numero = '50-8693402/21'", or even when I put this directly on the SQL works fine. But when I pass it as a parameter it doesn't work.
I've already searched for someone with the same problem, but I've only found topics from 2019 and their solutions seems to not work anymore, or I'm missing something.
So... I want to pass a full query as Comando, and then execute it. Passing the query as a test input works just fine, and writing it directly on the SQL field works just fine too. The problem is when I pass Comando as a parameter.
What I've already done: Comando is marked as expandable line, I'm using encodeSQL to pass the text to SQL and the database is already working.
Well... As it seems to be more a kind of improvised tool, we've decided to publish the application hardcoding the SQL into the source. This should solve our problems and be easier to work with. Of course we will have to publish every time we want to run different things, but as we're not planning to use it every day there's no problem.
I would not advise to do what you want as it would mean you are exposed to SQL injection vulnerability.
Yeah, I know that. But this is a very restrict application. It's like a last resource to do massive operations on the database as we can't do this directly.
So you want to do a massive delete/update/insert action on many different objects in the same sql?
If you want a select you have the issue with the output entity.
The select is just to log info and test the input without messing with the data itself. The output part is already working.
Then you can only do operations on contrato object?
I still must say that if you want to be able to do deletes etc on 1 entity, that it takes only 30 minutes to create a screen with that functionality. If you want bulk operations, you need a timer and that takes another 30 minutes to build.
I still don't know why you would build something like this.
But i will leave you guys alone. Good luck!
Hey,
you can pass SQL query in text data type format.
If you want to execute that then inside SQL use execute SQL command
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = @sqlquery(input variable for SQL) EXEC (@sqlCommand)
Thanks
Yogesh
In that case, @sqlCommand should be another parameter or are you referring to @Comando?
Hey,sql command is not another variable. It will be newly created variable in select command and you just need to set your query to that variable ti execute.
If I don't put it I just get this error:
Ohhh in this case don't use sql command ,simply pass your input variable to execute
Talking with my team, we've decided to change our approach as this maybe give us more problems than we want. I'm gonna use some hardcoded SQL that should be published every time. As this is not something that we'll use every day there's no problem.Thanks for your help anyway!
If you need to execute different SQL statements over time, why not use the SQL sandbox application available in the Forge?
https://www.outsystems.com/forge/component-overview/5900/sql-sandbox