Change in runtime the external database connection

Change in runtime the external database connection

  

Hi,

I have a scenario with multiple external Sql Server databases with the same schema (but different names) where I need to connect to fetch data depending on the user that is logged in. Something like:


Ideally, I’d like to have just one extension pointing to a single DB connection that would be updated in runtime.

This way my implementation doesn’t need to have any db specific logic and if I want to add new databases in the future would be only a matter of a small and centralized change.


For what I could understand the PlatformRuntime API DatabaseConnection_SetConnectionStringForSession (https://www.outsystems.com/help/servicestudio/9.0/APIs/PlatformRuntime_API/PlatformRuntime_API.htm) does exactly this – changes the connection string of a given db connection.


The problem is the databases despite having the same structure have a different name.

From my tests my understanding is that all the queries are done with the database name used to create the extension. So when I change the db connection configuration the queries will keep having the old name:

  1. Create DB extension and connection based on the DB1
  2. Querying DB1 -> works
  3. Update DB connection (Server=server;Database=DB2;User Id= DB2User;Password=P)
  4. Querying DB2 -> fails (DB2User is not able to access DB1)


Is there a way of accomplish the behavior given my restrictions? or am I doing some wrong assumptions?


Thank you

Hello Hugo,

I never tried what you wants to achieve, but as far as I know, you are correct in your assumptions.

When you import the entity, The system will have to define its name, in order to be able to execute queries.  

There is no way to update entity names, I think...

Changing the connection will not update any names, beside the database name.

If the only difference is the database name, than it should work, as the database name is defined in the connection. In this case, if it is not working, probably is because you are trying to change the connection and querying (using it) in the same transaction. I think to use it you need to finish the actual transaction.

So, when you say the structure is the same, what is changing? Only database names, or also table names?

Cheers,

Eduardo Jauch

Solution

Hi Hugo, 

Make sure that the entities that you import via the extension don't have the db name in them. So instead of [db1].[dbo].[table], just use [table]. This way the db name shouldn't be a problem. 

Regards, 

Lennart

Solution

Eduardo, all the tables have the same names and structure. The problem was that the database name is by default part of the entity name in the integration studio.


By removing the db name as suggested by Lennart I was able to get this working.

Thanks for your help.

Ah!

Learning something every day ;)
Never thought of including the database name in the integration studio (never payed attention to this).

Nice!

Cheers,
Eduardo Jauch