Good Day Community,
It has only been a while since I started using outsystem, 10.0.906. One of my dilemma is how to create a dynamic output entity or structure for the advance sql.
Why you ask and the output should be fixed? It's because the selected attribute will be based on the user. One user might only want two columns of the entity while the next user would need three.
The image below is the normal output, so if i want a dynamic select query I still need to follow the total column count of the Account entity even if only two columns is needed.
The image below is one solution and that is to create a structure with two attributes but if I pass a selected fields with more that 2 attributes then only two will be returned.
This is why I'm asking if you may have an idea on how to approach this dilemma. Thanks
hi Rama,
# One idea is to concatenate all the columns queried, and then just put in one field structure, then you need to parse back the result.
# The structure could be as simple as csv or more complex one, using a No SQL structure - json.
# Here is the link for https://blogs.oracle.com/jsondb/generating-json-data for Oracle json (see
Thanks for the suggestion Box.
I hope the performance of parsing it back is not very slow since the query will be returning alot of data :)
Edit:
Sad. We are using 11g, that feature is only for version 12 oracle.
RaMa,
I expected a similar functionality as described here.
In fact, I created an idea today which mentions same thing. Check the idea at: https://www.outsystems.com/ideas/5677/aggregates-and-advanced-sql-query-with-resultset-as-unstructured-data-dictionary
Thanks Chetan. Will check this out.
Hi RaMa,
You can try using the for JSON or for XML clause and get the data in json or xml format and then convert it into structure. Make sure you test this for performance.
For example
1. Get the table record with FOR XML clause like below
SELECT root.[Id] as [column], root.[Value1] as [column], root.[Value2] as [column], root.[FriendlyName] as [column] from {Table} root FOR XML RAW ('ROW'), ELEMENTS;
2. Output structure is defined as genericXML with one text field.
3. Define a variable of type list of generic record.
4. Use the XmlToRecordList plugin to convert it into structure
Below is the configuration.
Action & Structure:
XML to Record List:
Prasad Rao wrote:
One minor problem. Rama is using Oracle. The For XMl is in SQL server.
In Oracle, there is some different function for getting data in XML
So I tried your approach.
1. Oracle query
SELECT XMLElement("NAME", A."NAME") FROM "OSADMIN"."OSSYS_USER_T20" A
2. Followed your structures but xml to recordlist is not returning any data
3. Sample xml string
"<GenericRecord><NAME>Administrator</NAME></GenericRecord>"
EDIT: Never mind. I got it working. It's my xml formal that was wrong.
Hi Prasad Rao,
I have a JSON structure how can insert those attributes dynamically into a structure?
Thanks,
Ronan T.
Thanks for the great suggestion Prasad but as Chetan explained I'm currently using Oracle.
Rama, in Oracle the way of getting the data in XML is bit different. Check some examples at the link: https://stackoverflow.com/questions/15226574/how-can-i-convert-dynamic-row-data-to-xml-in-oracle
You have to use functions like XmlElement, XmlAttribute etc
Thank Chetan.
Your xml is not matching the structure, if I am not wrong you code should be throwing exception.
In my example I had xml as <GenericRecord><ROW><COLUMN>Administrator</COLUMN><COLUMN>admin@test.com</COLUMN ></ROW></GenericRecord>
So the idea is to get the xml as record -> 1:M Row -> 1:M Column. It took me some time to figure out the right syntax to generate the XML in SQL server, you will have to find out the syntax for Oracle if query can return xml or json in similar fashion.
Hi,
I do not think we can change the Outsystems structure in runtime, all the Outsystems structure has to be defined at development time, that was the reason we used a generic structure (above) with list of rows and rows being a list of column.
Prasad Rao