SQL Out Put List is different for different scenario

Arkyadeep Bharadwaj

Solved

SQL Out Put List is different for different scenario

Question

Traditional Web

Application Type

Traditional Web

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?

on 11th Mar

Arkyadeep Bharadwaj

Solution

with help of SQLSandBox component it is resolved.

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

See solution in context

on 12th Mar

1 reply

on 12th Mar

Show thread

Hide thread

Daniël Kuhlmann

mvp_badge

MVP

Hi,

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

Regards,

Daniel

on 12th Mar

Ruben Meireles

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

on 11th Mar

Jeanene Williams

Champion

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.

on 11th Mar

Daniël Kuhlmann

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

on 12th Mar