Cannot use variable as table name in advance sql select statement (external db)

Cannot use variable as table name in advance sql select statement (external db)

  

I have a drop down box with list of table names of the external db. When selected table name is passed to a variable which is used by an advance sql.

Script follows:

SELECT * FROM @tablevariable

error is table or view does not exist.

but without using the variable. data is displayed.

SELECT * FROM {table_name}

Anyone knows what is the problem?

Hi A A,

Have you selected "expand inline" for the input parameter being used in advanced sql?

Tiago Neves wrote:

Hi A A,

Have you selected "expand inline" for the input parameter being used in advanced sql?

Hi Tiago Neves, 

Sorry I forgot to mention that I have also selected "expand inline" for the input parameter. The problem still exist.

many thanks for the reply.

Anyone has encountered this and was able to resolve?

HI, AA:

I Try and nailed it.

regards,indra

Indra Budiantho wrote:

HI, AA:

I Try and nailed it.

regards,indra

Hi Indra, 

Sorry for the delayed reply.. 

I think the table name you passed on the variable is not  on an external db that is why it worked. Tried using

 "{ }"  outside of the variable and did not forget to set the  "expand inline" to yes.  Problem still exist when calling a table on an external db,  


hi AA,

I tried in my environment,it is entity got from external Oracle database that is integrated using Integration Studio. Then I have tested in the SQL widget test and the records are successfully queried. I do not know your system, could be privilege problem? Have you managed dependency the module (and the entity) from that external table?

see this:

https://success.outsystems.com/Documentation/10/Extensibility_and_Integration/Connect_to_an_External_Database

the last paragraph says:

regards,

indra

Indra Budiantho wrote:

hi AA,

I tried in my environment,it is entity got from external Oracle database that is integrated using Integration Studio. Then I have tested in the SQL widget test and the records are successfully queried. I do not know your system, could be privilege problem? Have you managed dependency the module (and the entity) from that external table?

see this:

https://success.outsystems.com/Documentation/10/Extensibility_and_Integration/Connect_to_an_External_Database

the last paragraph says:

regards,

indra

Hi Indra,

I am also using Oracle DB. The external Oracle database that is integrated using Integration Studio. 

"could be privilege problem" This part I missed out, so I tried giving my user full control but unfortunately it did not solve my problem. Here are some screenshot that might help.

Fig1: External DB (Done in Manage Dependency)

Fig2: Left side when run, successfully returns a value.

         Right Side when run returns error table or view does not exist.

Fig3: When tried using "{ }" outside of variable the ff error occured.

Attached scr2 in case unreadable.

Thank you.

Regards,

A. A.

hi AA,

This is the same with mine and it works, test value do not need {}, but select * need it:

I think your output structure is incorrect, just use the same entity: Prod_arb_acc.., NOT this one:

change it to Prod_arb_acc entity.

regards,

indra


HI AA,

Please pass columns name in advance sql. Below is the example.


Above Query is tested.

Regards,
Jitendra