indirect addressing of table name has a bug

Yes - I know that you should avoid expand inline whenever possible.

This is a somewhat weird specific case but it looks like found a bug in advanced sql and want to see if anyone has experienced this and found a work around. I have started a ticket with OutSystems support and will see what they say.

We have a test server and a production server - they are pretty much exact copies of each other and weekly the test server gets migrated to the production server. We have one "Core" espace that houses 99% of our data tables/entities.

On the test server, we made a connection to our production server database and then made an extension to which we added all of the entities in our production database.

In the "Core" espace on the test server, we referenced the extension. When the reference was made, the system automatically added a 2 to the end of all of the table names. So if in the test server "Core" espace I had an entity called "Order_Info", in the extension reference that table would be named "Order_Info2".

So, if I make an aggregate with "Order_Info", I see the data from the test server database. If I make an aggregate with "Order_Info2" on the test server Core espace, I see the order info from the production server. Everything is going great.

But, we need to do advanced sql. So I make an advanced sql query in the Core espace on the test server and put in something like select top 5 * from {Order_Info}. When I do a test on this, I see the top 5 results from my test server database. Now I rewrite the sql to be select top 5 * from {Order_Info2}. Run the test and I see the top 5 data results from my production server. Everything is still going great!

Now, I want to get fancy and because of the project we are working on - this has to work. So I add an input var to the advanced sql called TableName, type text, expand inline set to yes. I rewrite the sql to be select top 5 * from @TableName and I set the input test value to be "{Order_Info}". I test it and I get the top 5 from Order info from my test server. That is what I expected. Now I change the test value for TableName to be  "{Order_Info2}" annnnnd - I still get the top 5 results from my test server database NOT the top 5 results from my production server data. I believe this has something to do with the fact that the actual table name definition (something like [OutSystems].[dbo].[OSUSR_ABC_ORDER_INFO]) is the same between the two tables. However, the advanced sql DOES work if I directly type in the name of the table referencing back to my prod database table so I think its a bug in the way indirect addressing inputs are handled.

Anyone seen this and found a way around it? Thank you in advance!

Hi Jason,

You need to retrieve the physical table name from the metadata and use that directly:



Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.