Suggestions Dynamic Output Entities or Structure

Suggestions Dynamic Output Entities or Structure

  

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

2) A simple JSON array..inside it..hopefully make your day lighter..:)) .


regards, 


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:

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:


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


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.

Prasad Rao wrote:

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:


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 RaMa,

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.