Error while dynamically build query and execute it

Hi,

When we dynamically build the query and try to execute query a external tables it gives below error.

(SELECT @SelectClause  from @TableName;): Invalid object name 'Companies'.


Thanks.

Hello Prasad,

Have you set the "Expand Inline" property for your query parameter to YES.

Check the OutSystems documentation 

https://success.outsystems.com/Documentation/10/Reference/OutSystems_Language/Data/Handling_Data/Queries/Query_Parameter

Hope this helps.

Hello Prasad,

You can test your query and check the sql generated. Check if this SQL is what you expected.

Regards,

Marcelo

Hi,

Expand inline property is set to yes and when I hardcode the external table name it works. It even works when table is in OutSystems database.

Thanks.

If you're not passing the schema in the @TableName parameter, you must assure that the table is n the default schema of the user defined in the connection.

Hi,

I have tried with Schema name and without Schema name but both throws errors. I am starting to think that we cannot pass the external table name through parameter.

Table is in the same schema as connection string and name is also same as entity name in OutSystems.

Thanks.

It is possible, and shouldn't be a big deal.

Have you mapped the table with integration studio? If so, can you show us the entity definition tab?

Are you able to query the entity in a regular aggregate?


Hi,

Below is the definition of the table in integration studio is attached.

Aggregates and also SQL works if I hard code the table as {Companies}.


Thanks.

Are you using "{Companies}" as the value of your @TableName parameter? It should work.

Hi,

We cannot use the external table name dynamically. work around is as below. we need to build the select query string with actual physical table name.

/* %LogicalDatabase%=GetLogicalDatabase({Companies}) */
exec ('SELECT * FROM [dbo].[MS_Companies]')

Thanks.

Solution

This below example is working.




Note the entity notation in the input parameter.

Solution

Hi,

Thanks for the solution but it won't work for my use case. My use case requires to have different schema for each tenant and we have to dynamically change the database schema based on the tenant user. In order to achieve this we cannot have the schema name in "table name or view name" in plugin.

Thanks.

If your definition of the entity does not include the schema, you can define multiple DB Connections and change it in runtime using DatabaseConnection_SetConnectionStringForSession. This may avoid using the physical table names.

Hi,

The moment I removed the schema name from plugin it started giving error in the query as object not found, the whole issue started from there.

Thanks.