Hi,

I try to create an advanced SQL query that inserts a record into a specific entity and attribute based on input parameters. 

What i have now is:

  1. 2 dropdowns (entity, attribute) to select which entity and the corresponding attribute
  2. Input field for the value

I passtrough the Entity Name and Attribute Name into the parameters. Below is one of the ways I tried. But it doesn't work at all. 


Does anyone have a working suggestion?


PS; i know there are multiple forum posts about this, but none of these helped me out. 

Hi! 

The "Attribute" parameter and the "Entity" parameter have to be be "Expand inline = Yes"

2.. - you are building a record, that record has only that field?   

I really don't know if the platform let you do what you want that way, but you can try. 

Hope this help a little bit. 

Graça 

Maria da Graça Peixoto wrote:

Hi! 

The "Attribute" parameter and the "Entity" parameter have to be be "Expand inline = Yes"

2.. - you are building a record, that record has only that field?   

I really don't know if the platform let you do what you want that way, but you can try. 

Hope this help a little bit. 

Graça 

For test purposes it has only one field. I already tried the expand inline and didn't work either. 

Solution

Hi Jorick,


I'll just forgoe the whole 'are you sure you want to do this', 'what use case do you have that you can't just use the outsystems built in create action', 'beware of sql injection' and so on, but these are valid questions and I'm sure you and your team will consider them.

So for starters, Maria makes a valid point that your approach of having the name and value of a single attribute as parameters is not really scalable to inserts with multiple attributes.  

So I found that OS is being a bit weird about having to combine special characters like {} for entities or [] for attributes or ' ' around string values with parameters, so what i found works is following

1) either build the full sql statement outside of the widget, pass 1 parameter with expand inline and just have that single parameter as your sql

2) pass individual names and values with expand inline, but with the necessary special characters already around them for example pass in {sample_table} as the entity instead of passing in sample_table and then concatting with {} inside the sql tool

Good luck,

Dorine

Solution

Dorine Boudry wrote:

Hi Jorick,


I'll just forgoe the whole 'are you sure you want to do this', 'what use case do you have that you can't just use the outsystems built in create action', 'beware of sql injection' and so on, but these are valid questions and I'm sure you and your team will consider them.

So for starters, Maria makes a valid point that your approach of having the name and value of a single attribute as parameters is not really scalable to inserts with multiple attributes.  

So I found that OS is being a bit weird about having to combine special characters like {} for entities or [] for attributes or ' ' around string values with parameters, so what i found works is following

1) either build the full sql statement outside of the widget, pass 1 parameter with expand inline and just have that single parameter as your sql

2) pass individual names and values with expand inline, but with the necessary special characters already around them for example pass in {sample_table} as the entity instead of passing in sample_table and then concatting with {} inside the sql tool

Good luck,

Dorine

 Thank you, this works! I used solution 1. :-)