I am trying to pass a query in a parameter but I get this error:

and this table is from an external database!
and when I try to execute the exact same query either in an original SQL Editor or even in the Advanced Query itself without the paramter, it works fine!

So, I don't know why Outsystems don't recognize the table while I am passing it through this parameter!


and that's the query I am trying to pass:


Anyone faced this issue before? or knows what causes it?

Hi Mohamed Emam,

There are 3 points to take into account:
1 - the tables must be already referenced by the espace

2 - the input "query" must have the "expande inline" as "true".

3 - The output structure must match the output of the query (number of elements, order and type)

Hope this helps solve your problem.

Best Regards
Rodrigo Henriques

Rodrigo Henriques wrote:

Hi Mohamed Emam,

There are 3 points to take into account:
1 - the tables must be already referenced by the espace

2 - the input "query" must have the "expande inline" as "true".

3 - The output structure must match the output of the query (number of elements, order and type)

Hope this helps solve your problem.

Best Regards
Rodrigo Henriques

You mean tables referenced by the espace, to have them already available by managin the dependencies or you mean to add something before the table name in the query?


and for the other two points, I alredy did that!

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Hi Mohamed Emam,

There are 3 points to take into account:
1 - the tables must be already referenced by the espace

2 - the input "query" must have the "expande inline" as "true".

3 - The output structure must match the output of the query (number of elements, order and type)

Hope this helps solve your problem.

Best Regards
Rodrigo Henriques

You mean tables referenced by the espace, to have them already available by managin the dependencies or you mean to add something before the table name in the query?


and for the other two points, I alredy did that!

Hi Mohamed Emam,

I mean that you can only query a table that already exists in the espace context. If the table you are trying to query is from another espace you must have it referenced in order to work. 

If you just added the reference to the table i suggest you publish the espace before trying to run the query.

Best Regards

Rodrigo Henriques


Rodrigo Henriques wrote:

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Hi Mohamed Emam,

There are 3 points to take into account:
1 - the tables must be already referenced by the espace

2 - the input "query" must have the "expande inline" as "true".

3 - The output structure must match the output of the query (number of elements, order and type)

Hope this helps solve your problem.

Best Regards
Rodrigo Henriques

You mean tables referenced by the espace, to have them already available by managin the dependencies or you mean to add something before the table name in the query?


and for the other two points, I alredy did that!

Hi Mohamed Emam,

I mean that you can only query a table that already exists in the espace context. If the table you are trying to query is from another espace you must have it referenced in order to work. 

If you just added the reference to the table i suggest you publish the espace before trying to run the query.

Best Regards

Rodrigo Henriques



Yes, I referenced it, and if I am trying to query this table normally via the advanced query directly without this parameter, it works fine! 

Hi Mohamed Emam,

Can you show me the structure and table fields?

Best Regards 

Rodrigo Henriques

Rodrigo Henriques wrote:

Hi Mohamed Emam,

Can you show me the structure and table fields?

Best Regards 

Rodrigo Henriques


Hi Mohamed Emam,

The fields type are the same?

I tried a similar example and was able to query a table data into a structure.

Can you provide the espace? Maybe there is something we are missing?

Best Regards

Rodrigo Henriques

Rodrigo Henriques wrote:

Hi Mohamed Emam,

The fields type are the same?

I tried a similar example and was able to query a table data into a structure.

Can you provide the espace? Maybe there is something we are missing?

Best Regards

Rodrigo Henriques


Did you try it with External Database? or just created a table in the same espace? because in this case, it works fine, but in my case it doesn't work if it's external database!


I can't share the espace at the moment, but I can do another sample on another enivronment! 

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Hi Mohamed Emam,

The fields type are the same?

I tried a similar example and was able to query a table data into a structure.

Can you provide the espace? Maybe there is something we are missing?

Best Regards

Rodrigo Henriques


Did you try it with External Database? or just created a table in the same espace? because in this case, it works fine, but in my case it doesn't work if it's external database!


I can't share the espace at the moment, but I can do another sample on another enivronment! 

I used a table from another espace. 

Did you publish the espace after adding the reference to it? 


Rodrigo Henriques wrote:

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Hi Mohamed Emam,

The fields type are the same?

I tried a similar example and was able to query a table data into a structure.

Can you provide the espace? Maybe there is something we are missing?

Best Regards

Rodrigo Henriques


Did you try it with External Database? or just created a table in the same espace? because in this case, it works fine, but in my case it doesn't work if it's external database!


I can't share the espace at the moment, but I can do another sample on another enivronment! 

I used a table from another espace. 

Did you publish the espace after adding the reference to it? 



Yes, I did

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Mohamed Emam wrote:

Rodrigo Henriques wrote:

Hi Mohamed Emam,

The fields type are the same?

I tried a similar example and was able to query a table data into a structure.

Can you provide the espace? Maybe there is something we are missing?

Best Regards

Rodrigo Henriques


Did you try it with External Database? or just created a table in the same espace? because in this case, it works fine, but in my case it doesn't work if it's external database!


I can't share the espace at the moment, but I can do another sample on another enivronment! 

I used a table from another espace. 

Did you publish the espace after adding the reference to it? 



Yes, I did

At this point i cannot see what might be the problem.

Please send a sample espace so I can give it a look.


I can see from the example that your testquery is: Select * from {person}


This should be: Select {Person}.* from {Person}

Stefano Valente wrote:

I can see from the example that your testquery is: Select * from {person}


This should be: Select {Person}.* from {Person}


I tired it and I get the same error!

Mohamed Emam wrote:

Stefano Valente wrote:

I can see from the example that your testquery is: Select * from {person}


This should be: Select {Person}.* from {Person}


I tired it and I get the same error!

It is only required to indicate the table before the * when you have multiple tables.


In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.

That might be correct in previous/older versions.

What I've learned is that you only need to define the table when you have multiple tables. You have to do that so that if there are columns with the same name, the system can be able to relate the correct values to the output.

Even with multiple tables, if there are no columns with the same name, i believe that the query will work only with "*". 

Query Definition and table:


Query executed and output structure:


Output:


I had to mask the output, but as you can see, i was able to execute it.

The table was from another espace.

Mohamed Emam please send a sample espace as soon as possible, and i will try to understand what is the problem with your query.

Best Regards

Rodrigo Henriques

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.


This scenrio works fine with me, but doesn't work when the table is coming from an external database, and it's in another extensio module managed by integration studio

Mohamed Emam wrote:

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.


This scenrio works fine with me, but doesn't work when the table is coming from an external database, and it's in another extensio module managed by integration studio

Tried your situation (table referenced from a Extension) and in fact got a similar error.

I believe this is a limitation of Outsystems working with External Databases, probably linked with the connection.

Maybe you should report/give feedback to Outsystems explaining the situation. If it is a know issue maybe they can give you a workaround, if not they can try to provide a future solution.

Best Regards 

Rodrigo Henriques


Rodrigo Henriques wrote:

Mohamed Emam wrote:

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.


This scenrio works fine with me, but doesn't work when the table is coming from an external database, and it's in another extensio module managed by integration studio

Tried your situation (table referenced from a Extension) and in fact got a similar error.

I believe this is a limitation of Outsystems working with External Databases, probably linked with the connection.

Maybe you should report/give feedback to Outsystems explaining the situation. If it is a know issue maybe they can give you a workaround, if not they can try to provide a future solution.

Best Regards 

Rodrigo Henriques


I see! Thanks :) 


Rodrigo Henriques wrote:

Mohamed Emam wrote:

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.


This scenrio works fine with me, but doesn't work when the table is coming from an external database, and it's in another extensio module managed by integration studio

Tried your situation (table referenced from a Extension) and in fact got a similar error.

I believe this is a limitation of Outsystems working with External Databases, probably linked with the connection.

Maybe you should report/give feedback to Outsystems explaining the situation. If it is a know issue maybe they can give you a workaround, if not they can try to provide a future solution.

Best Regards 

Rodrigo Henriques



Hi Rodrigo,


Is there a special forum or contact for Outsystems to report this issue?



Mohamed Emam wrote:

Rodrigo Henriques wrote:

Mohamed Emam wrote:

Stefano Valente wrote:

In Advanced SQL it is always requiredRodrigo, at least: that is what i have learned.


I have tried to recreate your situation and managed to get it working.


This scenrio works fine with me, but doesn't work when the table is coming from an external database, and it's in another extensio module managed by integration studio

Tried your situation (table referenced from a Extension) and in fact got a similar error.

I believe this is a limitation of Outsystems working with External Databases, probably linked with the connection.

Maybe you should report/give feedback to Outsystems explaining the situation. If it is a know issue maybe they can give you a workaround, if not they can try to provide a future solution.

Best Regards 

Rodrigo Henriques



Hi Rodrigo,


Is there a special forum or contact for Outsystems to report this issue?



Hi 

I would suggest you open a Support Case - https://www.outsystems.com/SupportPortal/CaseOpen/

Then, if this is not the right place, someone from Outsystems can point you in the right direction.

Best regards 

Rodrigo Henriques





Solution

Hello,

The problem might be since you do not have any entity in your Advanced Query SQL, OutSystems does not know which database connection to use: Default OS or External Database connection.

In order to let OutSystems know which connection you are going to use, paste the following commented code at the top of your SQL Query:




Just to make it clear, you should replace "Entity1" with a corresponding entity that belongs to the external database connection that is being referenced in this module.


It might even work with just a comment like:    /* {Entity1} */

Test both scenarios.


Let me know if that worked.

Solution

Tiago Pinho wrote:

Hello,

The problem might be since you do not have any entity in your Advanced Query SQL, OutSystems does not know which database connection to use: Default OS or External Database connection.

In order to let OutSystems know which connection you are going to use, paste the following commented code at the top of your SQL Query:




Just to make it clear, you should replace "Entity1" with a corresponding entity that belongs to the external database connection that is being referenced in this module.


It might even work with just a comment like:    /* {Entity1} */

Test both scenarios.


Let me know if that worked.

Thanks, Tiago. it worked with the commented line you mentioned, but it didn't work with just:  /* {Entity1 */


Tiago Pinho wrote:

Hello,

The problem might be since you do not have any entity in your Advanced Query SQL, OutSystems does not know which database connection to use: Default OS or External Database connection.

In order to let OutSystems know which connection you are going to use, paste the following commented code at the top of your SQL Query:




Just to make it clear, you should replace "Entity1" with a corresponding entity that belongs to the external database connection that is being referenced in this module.


It might even work with just a comment like:    /* {Entity1} */

Test both scenarios.


Let me know if that worked.

Is there a way to implement this in run-time outside Advanced Query?

I am passing this query to another action, which will invoke the query, but I am getting the same error, that Outsystems still doesn't recognize which database I am refering to!


Mohamed Emam wrote:

Tiago Pinho wrote:

Hello,

The problem might be since you do not have any entity in your Advanced Query SQL, OutSystems does not know which database connection to use: Default OS or External Database connection.

In order to let OutSystems know which connection you are going to use, paste the following commented code at the top of your SQL Query:




Just to make it clear, you should replace "Entity1" with a corresponding entity that belongs to the external database connection that is being referenced in this module.


It might even work with just a comment like:    /* {Entity1} */

Test both scenarios.


Let me know if that worked.

Is there a way to implement this in run-time outside Advanced Query?

I am passing this query to another action, which will invoke the query, but I am getting the same error, that Outsystems still doesn't recognize which database I am refering to!


Hi,

Just to see if I get it clear:

1. You have Action A that has an Adv Query inside. This Adv Query has a single input, @Query, and also the comment inside.

2. Action A receives an input that you will pass to the Adv Query.

3. Whenever you call Action A from Action B, you get the error you were getting before.


Are Action A and Action B in the same eSpace?

Did you confirm that the Entity1 inside the commented SQL is being referenced in the module where Action A is? It is very easy to eliminate Entity1 reference by mistake when using the module option "Remove unused dependencies"


Best,

Tiago