Use site property in a sql query

Use site property in a sql query

  

Hello everyone.

I am trying to find a way to use a Site Property in a SQL query. Basically, there is a value which I use in many aggregates and SQL queries which I would like to change on a global level, without going into each query and updating it manually. Is there a way to do this?

Your help is much appreciated.

Kind regards,

Val

Hi Val,

You can use parameters in your SQL Queries and pass to those parameters the value of your site property, like this:


Hello Val Bard,

Yes, it is possible. You just have to pass that site property to your SQL query via input parameter.

Best Regards

Hello all.

Thank you for your prompt response.

I would like to pass a site property in the format of "{tablename}.[columnname]" and use it in a where statement, i.e.

where @thesiteproperty = 'false'.


But Outsystems does not seem to like it?  Would you be able to help.


Thank you.

Kind regards,

Val

Site properties can only be of Basic Type or Identifiers.

I'm not sure if I understood what you really want to do, but, If you want to do a SQL query based on an Entity attribute, why don't you use this attribute itself inside the query?

Best Regards

Thank you Paulo for this quick response.

I have to tailor the application for different clients and I need to easily switch between views by just changing a Site Variable. I have large number of queries and would ideally not want to go and change each one of them. 

A little background on the problem: I have a main entity where I have all the data for a number of records. Not all records are going to be included in the summaries presented to the clients so this is why I have one entity attribute (Client1, client2, client3) for each client set as Boolean to tick the records to be shown. Hence, why I need to pass the entity attribute (Client1, client2, client3) as the one to use. I hope that makes sense.


Kind regards,

Val


Allright, so I'm assuming that you want to show data based on the Client that is logged.

Seems to me that a session variable would be a clear solution instead of the site property.

Upon login, the session variable would be assigned to some kind of identifier of the Client that is logged... then, you could use this session variable in your SQL queries.

Is that close to what you want?

EDIT : I'm assuming that the Client you are mentioning is not a single user. Otherwise you can just fetch the data by the UserId.

Best Regards

Hi Val,

From your description, I think you could solve it by using "Expand Inline" parameters do dynamically create the WHERE clauses. So your setup would look something like this:



Paulo Zacarias wrote:

Allright, so I'm assuming that you want to show data based on the Client that is logged.

Seems to me that a session variable would be a clear solution instead of the site property.

Upon login, the session variable would be assigned to some kind of identifier of the Client that is logged... then, you could use this session variable in your SQL queries.

Is that close to what you want?

EDIT : I'm assuming that the Client you are mentioning is not a single user. Otherwise you can just fetch the data by the UserId.

Best Regards


Thanks Paulo. 


This is an option but I will have to have different data screens tailored for each client. I am working on a demo and I would like to tailor what to show on the screen depending on the client. Basically, I just want to use an entity attribute as a filter to select certain records.


Thank you.


Kind regards,

Val

Aurelio Santos wrote:

Hi Val,

From your description, I think you could solve it by using "Expand Inline" parameters do dynamically create the WHERE clauses. So your setup would look something like this:




Hi Aurelio.

This is exactly what I need but I get 'incorrect syntax near 'where''. in the sql query, I have 'where @demo'  and in the inline expansion : Site.Demo + "like '%a%'". It doesn't seem to work.

Val Bard wrote:

Aurelio Santos wrote:

Hi Val,

From your description, I think you could solve it by using "Expand Inline" parameters do dynamically create the WHERE clauses. So your setup would look something like this:




Hi Aurelio.

This is exactly what I need but I get 'incorrect syntax near 'where''. in the sql query, I have 'where @demo'  and in the inline expansion : Site.Demo + "like '%a%'". It doesn't seem to work.

You forgot to add a space before the "LIKE". It has to be:

Site.Demo + " like '%a%'"

Aurelio Santos wrote:

Val Bard wrote:

Aurelio Santos wrote:

Hi Val,

From your description, I think you could solve it by using "Expand Inline" parameters do dynamically create the WHERE clauses. So your setup would look something like this:




Hi Aurelio.

This is exactly what I need but I get 'incorrect syntax near 'where''. in the sql query, I have 'where @demo'  and in the inline expansion : Site.Demo + "like '%a%'". It doesn't seem to work.

You forgot to add a space before the "LIKE". It has to be:

Site.Demo + " like '%a%'"

Thanks, Aurelio but it still says incorrect syntax near where.


What's the value of your site property?

Aurelio Santos wrote:

What's the value of your site property?




Val Bard wrote:

Aurelio Santos wrote:

What's the value of your site property?




I can't see your image.


Assuming that your entity's name is "AudioFileTable" and that it has an attribute named "Demo1", everything seems ok.

Can you post screenshots of your full query and of the parameter expression?

Hello Val,

I noticed that your input parameter has an orange box around it, in the query. 

It is just the warning to encode the sql or is something else?

Also, could you do a simple test?

Replace the @WhereClause in the query by the value your are passing to see of it works, to define if the problem is in the method or in the value itself.

And, of course, if your query is looking for a {Test} entity, like in your previous image, the where clause using the value {audiofiletable} will fail, as it is not in the query.

Cheers

Eduardo Jauch