SQL Out Put List is different for different scenario
Application Type
Traditional Web

Hi 

I am generating sql query in Advance SQL build on predefined template. And That Template format we have in our entity.

basically i am fetching the template data from Entity and generating the dynamic SQL and download in the excel. But the scenario is user can customize the template. Like user can add more column in the Query Builder part. Like I have one Entity in that we have 10 columns. Template is Build with 5 Columns from that entity. Now user can open the query builder screen and by default it will generate the SQL query with 5 Columns which are predefined. But in left hand side we are displaying all the attributes from that entity so user can add one or two more Column and want to generate the report. When user trying to add the new column then advance sql query is giving error because Advance SQL query output parameter type is mismatched because of new column.


do any one has any idea how to resolve that thing?

with help of SQLSandBox component it is resolved.

https://www.outsystems.com/forge/component-overview/5900/sql-sandbox

mvp_badge
MVP

Hi,

can you elaborate? You used parts of SQL sandbox? Maybe share your solution for others to learn from.

Regards,

Daniel

Hi Arkyadeep,
Not sure if I understood your problem, but it appears that you allow the user to customize the SQL and that will affect the output and you are getting a mismatch error because of that.

The only workaround I know for this is for you the define the output of your SQL as a structure that has more fields that the user will ever need. Having a structure with more attributes than what is returned from the DB is okay. 

Regards,

Rúben

I suggest having the SQL query always output the entire field list and then the user configuration just to display or output the selected fields that way you have more control. Alternatively depending on what you are doing with the output you could try to have your Advanced SQL query return the results as a JSON string instead using something like the below but then you would still need to deal with different JSON structures.


Format Query Results as JSON with FOR JSON - SQL Server | Microsoft Docs 

Note I haven't tried the "For JSON" SQL option in Outsystems yet but in theory it should work.

mvp_badge
MVP

Hi,

You can use the following  extension https://www.outsystems.com/forge/component/1305/advancequery/

This extension allow to get any SQL and return a JSON and even other formats.

Regards,

Daniel

with help of SQLSandBox component it is resolved.

https://www.outsystems.com/forge/component-overview/5900/sql-sandbox

mvp_badge
MVP

Hi,

can you elaborate? You used parts of SQL sandbox? Maybe share your solution for others to learn from.

Regards,

Daniel

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.