Dynamic Advanced SQL - Table name as a Parameter

Rank: #2775

Solved

Dynamic Advanced SQL - Table name as a Parameter

Question

Hi all,

I'm building a fully dynamic SQL query for a very complex application. The query I'm working on returns a JSON string as output, which will then be put into a dynamic JavaScript component. It targets an external database (SQL Server), which has its tables imported into the OutSystems eSpace as an extension; CRUD actions on these tables function normally when called from actions, aggregates, and 'normal' advanced SQL queries.

The SQL query I use is as follows:

SELECT {Table}.*
FROM {Table}
FOR JSON AUTO

It works brilliantly and quickly returns the proper JSON string from the table, until I try to 'parameterize' the table name as follows:

SELECT @var.*
FROM @var
FOR JSON AUTO

@var is an Input Parameter passed to the SQL as a Query Parameter of data type 'text' and has the Expand Inline property set to 'Yes'. I then defined @var as "{Table}" in the Test Inputs tab.

Whenever I put this parameter into the query above and run 'Test' however, it breaks: "Error in advanced query: Invalid object name 'DATABASE.dbo.Table'.

When I switch back to my first, 'hardcoded' query, the query under the 'Executed SQL' tab doesn't change a single character, but I do get my JSON string back without errors. I can't think of any reason why passing a parameter as the table name would give a different result in the database if the executed query stays the same.

Has anyone run into something similar? What did I miss?

Thanks so much in advance!

on 2019-04-04

Nuno Gonçalo Pereira

Rank: #370

Solution

i'm trying to help you testing it, and i have this error:

I think you need to put dbo.@Physical_table_name to access your table.

Such as:

where Physical_Table_Name is something like "OSUSR_OXZ_TABLE2"

I hope it will helps you!

Thanks and Best Regards,

Nuno Pereira

See solution in context

on 2019-04-04

Bernardo Condé

Rank: #252

Hi Monique de Vos,
Your problem is as follows:
As your tables are extensions, external sql tables, and you do

SELECT {Table}. *
FROM {Table}
FOR JSON AUTO

It works normally because OutSystems already knows the database and the tables it should access.
but when you try to enter a parameter in your query and you pass only "{TABLE}" text, it can not interpret.

As you saw the error, he hopes that in the front comes a owner from his bank. What you can do is the following: On your test everything happens ok, run it again, go to the "Executed SQL" tab and see how the full name of your table is being interpreted by OutSystems. Then take that name and test it as an input parameter again and see if it works.

on 2019-04-04

Nuno Gonçalo Pereira

Rank: #370

Solution

i'm trying to help you testing it, and i have this error:

I think you need to put dbo.@Physical_table_name to access your table.

Such as:

where Physical_Table_Name is something like "OSUSR_OXZ_TABLE2"

I hope it will helps you!

Thanks and Best Regards,

Nuno Pereira

on 2019-04-04

Sravan Vanteru

Rank: #401

Hi Monique de Vos,

You can achieve that like below.

Hope this helps

Sravan

on 2019-04-04

Monique de Vos

Rank: #2775

Hi all,

Thanks so much for taking the time to reply!

@Bernardo, OutSystems has been able to parse the {Table} test value just fine; after submitting {Table} as a test value, the correct string ([DATABASE].[dbo].[Table]) appeared in the Executed SQL tab. Putting an OutSystems entity between the brackets - like {Entity} - worked fine, too.

@Nuno, thanks for helping me on the right path! After looking at your post and my stored procedure call (calling [dbo].[StoredProcedure]), I changed my query to [dbo].[@Table]. It still didn't work when I tried to test it with the Test button, but running it 'for real' worked!

@Sravan, I'm sorry I haven't got to testing your solution, after I got it running I had to focus on another issue. Thanks for the help!

TL;DR, using the Test button in the Advanced SQL widget produced errors when trying to call an external database with a variable as the table name, while running the same code/module (in the browser) worked fine.

on 2019-04-05

Karan Bansal

Rank: #12403

Hi Sravan Vanteru,

Your response is really helpful. Thanks.

on 2020-12-09

Training

Documentation

Dynamic Advanced SQL - Table name as a Parameter