Outsystems automatically renaming tables from 2 different databases

Hi 

I am trying to switch between external databases at runtime using DatabaseConnection_SetConnectionStringForSession action where the UI will be same and the user will switch between the databases once he logs in based on the particular session.

When I try this, I have 2 tables with same structure and same names in 2 different external databases. When I import one table, Outsystems automatically renames it as <TableName2> and when I use it in the Advanced SQL Query as just <TableName> for retrieving the data, it always retrieves the data from the DB 1 as the table from DB 2 is renamed as <TableName2>. 

Can someone let me know if there is any other way to overcome this so that when I use the tablename in any aggregate or SQL Query, the table used changes to particular table belonging to that database based on the session set using the connection string?


Thanks is Advance!

You are defining the database connection extension twice. You have to change the data base connection in the connection extension and use only one connection dependence. 

Maria da Graça Peixoto wrote:

You are defining the database connection extension twice. You have to change the data base connection in the connection extension and use only one connection dependence. 

 Hi Maria,


Thanks for the response.

I changed the database connection extension to use a single extension which contains tables from both the external databases. But still when I use it in the advanced SQL, the table name is considered as [db1].[dbo].[tablename1] since the tablename for that entity is automatically taken as  [db1].[dbo].[tablename1] for table1 and [db2].[dbo].[tablename2] for table2 . I am not able to connect to table2 in Advanced SQL. 


Can you share any example on how to use it?



I do not have an example to show you. 

But we can think together:

- You need both databases info at the same time? 

To take advantage from the DatabaseConnection_SetConnectionStringForSession you must be using only one database at each moment, this action changes the database that is used by  "connection" your extension uses. 

If so you don't need to access the " table2", "table1" will have the data from the database you are using at the moment.   

Tell me something. 


Maria da Graça Peixoto wrote:

I do not have an example to show you. 

But we can think together:

- You need both databases info at the same time? 

To take advantage from the DatabaseConnection_SetConnectionStringForSession you must be using only one database at each moment, this action changes the database that is used by  "connection" your extension uses. 

If so you don't need to access the " table2", "table1" will have the data from the database you are using at the moment.   

Tell me something. 


 Yes, you are correct, I need one database at each moment. 

I have table TestDB from DB1 and TestDB from DB2. When I add both of these tables in a single extension, below are the fully qualified tablesnames in Integration Studio.

[DB1].[dbo].[TestDB] with Integration studio table name as TestDB

[DB2]. [dbo].[TestDB] with Integration studio table name as TestDB1 (1 is automatically appended by Outsystems).

When I use below SQL, When the connection string is set to DB2, I can still see the data from DB1 instead of DB2.

Is there a way where the data from DB2 would be displayed with the same SQL Query when the connection is set to DB2?

 

Can you check your integration studio , Just to make sure you have not hard coded the Entity and Table.


More in below Q&A.

https://www.outsystems.com/forums/discussion/63074/change-in-runtime-the-external-database-connection/


Regards,

Rama

Ramakrushnarao Seera wrote:

Can you check your integration studio , Just to make sure you have not hard coded the Entity and Table.


More in below Q&A.

https://www.outsystems.com/forums/discussion/63074/change-in-runtime-the-external-database-connection/


Regards,

Rama

Hi Ramakrushnarao,

I see that the details in Integration studio are hard coded as below.

Please help me on how to change this hard coding, because when I remove the database and schema names, and give only table name, it is giving me invalid object error in sql query.

 

And read also 

https://success.outsystems.com/Documentation/10/Reference/OutSystems_APIs/PlatformRuntime_API#DatabaseConnection_SetConnectionStringForSession


Then test it, forget the connection  you have done for "DB2" and test the change-in-runtime-the-external-database-connection, see what are the results from your SQL when using one or the other data bases. 

Champion

I totally agree with Maria da Graça Peixoto.

You don't need to create two extensions. You only need to create one extension and let DatabaseConnection_SetConnectionStringForSession  do the trick.

Also please refer to DatabaseConnection_SetConnectionStringForSession  document bellow.

The document has some points you should be careful when using DatabaseConnection_SetConnectionStringForSession  

https://success.outsystems.com/Documentation/11/Reference/OutSystems_APIs/PlatformRuntime_API


Champion

Hi Ravali Perni 

I tried to remove the database and schema name. It's worked for me.

Can you also provide an example of the connection string?

Regards

Tom Zhao wrote:

Hi Ravali Perni 

I tried to remove the database and schema name. It's worked for me.

Can you also provide an example of the connection string?

Regards

 

 Hi Tom,
Below is the example of connection string I am using.

 ConnectionString: "Server = ServerName; Database = DB2; User Id = abc; Password = pwrd;"


Also can you confirm if we need to mention any logical database for that table in integration studio?

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