Extension as output structure for the Advanced SQL

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

Until now I have been using an Extension with a Student entity to output the results of Advanced SQL to. I created another extension out of the same database table, with a different name, which gets a different entity name, Student3, to distinguish it from the former entity. If I choose this new Extension as output structure for the Advanced SQL, the query fails due to inconsistency with the database. (see video)

Any ideas?

Hi Maurice,

Nicely recorded video explaining the situation :)

The error message "Invalid Data Type" clearly explains what the issue is. As suggested in the Service Studio itself, define a mapping from 'Student3 Record List' to 'Student List' as your SQL (Stored Procedure) output parameter expects 'Student List' data type.

'Student List' was mapped from the earlier entity 'Student'.

Regards,

Swatantra

Why is this mapping needed? I am exchanging the SQL output parameter for another, exactly the same, structure, and delete the old one. That would imply an automatic, new mapping, wouldn't it?

I tried to do a manual mapping but it fails anyway.

When I make a new SQL Advanced block, it doesn't accept the Student3 entity either. Why do the former instances remain active?

Hi Maurice,

The mapping Swatantra is talking about, is the error you see at the bottom of your screen, about your Assign statement after the SQL.  In that, you are trying to assign the value of a Student3 List to the output, which is still a Student List.  If you want to go forward with Student3, then you'll have to change the definition of your Out1 parameter to datatype Student3 List.

Outsystems is strongly typed, and doesn't allow assigning things of different data types to each other, even if  they are in your eyes 'exactly the same'.

But you are also asking why you can't test the query after replacing Student with Student3,  and OS is trying to tell you that, it says that you first have to publish.  You can't test sql or aggregates on entities that have not been published yet.

So you'll have to fix the errors, publish, and then you'll be able to test with Student3 as output.

I can not change the data type of the Out1 parameter to a Student3 List, it's not among the choices.

Solution

Sure you can, you just have to try a bit harder.


I made an example oml, with birds instead of students, added a bird2 entity and replaced the bird entity in the sql output by bird2.

So here are 3 options for changing the data type of Out1 to a Bird2 List




Solution

Yes, it is working now. Strange I couldn't do that myself :-)

Only the SQL Advanced Test keeps complaining, but the app is working as usual.

Maurice Lemmens wrote:

I can not change the data type of the Out1 parameter to a Student3 List, it's not among the choices.


Oh, I see, Dorine already supplied you the example.

In OutSystems, input/output parameters are fixed with the strict entity type. So, removing one expects you to assign a new type. In your example, Student and Student3 were exactly the same, but for OutSystems, these are two different entities, two different lists. It is the way it is.

Nevertheless, it worked :)

Thanks Dorine for supplying the example.