put the resultset of SQL Tool into a List/Aggregate

My question is: can I put the results of an SQL query executed by the SQL Tool into a List/Aggregate ?

In my use case, the user provides a value for the age of a student and then a stored procedure is called which uses the age value as a parameter and then outputs a list of students as old or older than the specified age.

The SQL Tool outputs the correct results, but only when the correct entity (Student) is used. I can't produce a different Student structure or entity for the output data because then, the SQL tool complains about inconsistency between the data model and the database. Then, where should the data produced by the SQL Tool go?

--

Maurice

Hi Maurice,

You have to define a structure that matches the result comments of your SQL that returns the data.

On the AdvancedSQL you select the structure as output structure.

Extra background information that can be helpful:

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Regards,

Daniel

Daniel,

Of course I tried to create a structure that matches the query result, but it won't get accepted by the SQL Tool as output structure when testing. See picture

Maurice

It cannot find the stored procedure

Check your extension and the connection string.

https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/

Only when I use the original extension (under Entities), the results from the stored proc in Advanced SQL are correct (see pict).

But how can I output this in a List? Should I drag the Student entity to a List? When I do this, the running app always shows all students instead, not only those with the input parameter match.

Could you share screen print of your stored procedure?

see picture

Maurice, 

https://www.outsystems.com/forums/discussion/14867/how-to-stored-procedures/

Contains lot of detailed instructions, also instructions to fix the error message you get.

Regards,

Daniel

Sorry, but that didn't solve my problem, Thanks for your attention.

Got a bit further. Currently, I can reach the SQL List returned by the Advanced SQL by using an assignment. In the Logic, I first create a local variable called StudentsList. Then, in the assignment, I can assign the SQL List to this local variable. But at this time, there is a type mismatch and I can't find the correct DataType (Record List) for the local variable. (see attached picture)

Maurice Lemmens wrote:

Only when I use the original extension (under Entities), the results from the stored proc in Advanced SQL are correct (see pict).

But how can I output this in a List? Should I drag the Student entity to a List? When I do this, the running app always shows all students instead, not only those with the input parameter match.


Hi Maurice,

by the look of it, you have it working if you use Extension/Student as output for your query, so why try to change this ? just stick with that.

Your question is, how can I access the query result in the OS code following the query, I think.

I'm afraid the answer here really is, to refer you to the online learning material again, because this is basic stuff covered in the beginners course.

Results of both Aggregates and SQL nodes, are available in the form of a list, once they are executed, you don't have to do anything extra for that, except refer to that list.  (Even if you do a query on Id and only expect one result back, it will be in the form of a list with 1 item in it)

For example, if you add your SQL node in the Preparation of a Screen, you can put a Table Records Widget on your screen, and point to the list of the SQL node as the source of your Table Records, choose what attributes to show, et voila, your data will be displayed on the screen.

you can also do things like for example go through the list resulting from a SQL node in a For Each Cycle.


For example, if this is the SQL definition :


Then in all logic following the execution, this variable will be available, holding both the list and the count and loads of other properties :



now, go hit the online videos, will you :-)

Dorine


Thanks Dorine, I understand what you are trying to explain. But I am working in mobile mode, and there is no Table Records Widget.

I also checked the appropriate course manuals/videos for this and there is no solution for what I am trying to do.

You say: Results of both Aggregates and SQL nodes, are available in the form of a list

Where is that list?

Solution

Hi Maurice,


That list is available in the scope where the sql is executed, all aggregates and SQL's show up as local variables with a list and a count in their scope, like the example in my previous post.


For mobile, you will either do a FetchDataFromDatabase on a screen if you want to use an aggregate, or in your case if you want to use advanced sql, you will have done a FetchDataFromOtherSources on your screen.  In that case, you will still have to add a list to the output of that action and assign the list coming from your sql to it.


All of that is explained in detail in the online courses.

For mobile, This is a good beginners course

the bit in there about SQL

These courses are far better suited then a forum Q&A at explaining the basics, the people making these courses are far better teachers, and the time of people helping out on the forum is finite.  

You will also find that people helping on the forum already assume a minimum of knowledge about the platform, so you will very often not understand the answer you get and they will very often not understand what you are struggling with, until you put in that minimum effort to get an initial orientation of how the platform works.

Solution

Hi Dorine

Thanks for the answer. I understand I have to do FetchDataFromOtherSources, and assign a List to the output of that action. So far I can assign the SQL1.List to the Out1 variable of the resulting DataAction1 but there is no matching DataType available. I made a video of this, watch

 https://maulem.stackstorage.com/s/RxmzEO75uD5QlAy

I tried to add a List to the Screen but I can not reference it in the Assign.

Almost there, Maurice,

when you create a new Data Action like your 'DataAction1', OS by default creates one output variable of type text.  You will have to change the data type of the output of the DataAction to match the list structure of your SQL.  Then your assign will be ok.

So in your case, change the definition of Out1 into Student List

hi Dorine,

In the mean time, I have managed to assign the SQL1.List to the Out variable. This has to be done in the properties right below. (see picture)

After that, I dropped a List onto the Screen and dropped the Out variable on it.

The app is working now, I type the age in the box and the list changes accordingly.

Thank you very much for your time