Basically, I want to run queries dynamically in OutSystems. AI is generating SQL queries, and I need to execute them in OutSystems. For example, while I currently have two entities (Product and Customer), in a real scenario we might have around 50 entities. If AI returns a query related to Product, I need to execute that query, and similarly for Customer. However, since I won't know in advance which entity the query is related to, how can I define the output structure? Is there any way to run a dynamic query and get the output in JSON format? Is there any function available, like ExecuteSQL(), that supports this?
Hi Ramprsath,
The only way to make this happen is to return the result data as a json string in a text attribute of your output structure.
For more info see: Format query results as JSON with FOR JSON - SQL Server | Microsoft Learn
Regards,
Daniel
Hi Daniel, Thanks for the info. I'm passing the entire query as input, which is not a best practice. Is there an alternative approach that can make the application more secure and protect it from SQL injection?
Hi @Ramprasath R ,Please refer the below link,https://www.outsystems.com/forums/discussion/46809/dynamic-advanced-sql-table-name-as-a-parameter/https://success.outsystems.com/documentation/best_practices/development/building_dynamic_sql_statements_the_right_way/Thanks,sahana
Thanks for the info @Sahana K