Steve Webb
Rank: #19811
34Views
11Comments
Dynamic connection string for external databse
Question
External Database

I have a requirement to host an external database per client on Azure. I've successfully built an extension & created a database connection for one client, but how do I make that dynamic?

I think my approach can be:
>Service Center>DatabaseConnections>individual connections for each database

When I build the extension, I want to pass a variable into the extension which then defines which database connection to use. Is this possible?

0
0
on 2020-10-12
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

The PlatformRuntimeAPI has a function called DatabaseConnection_SetConnectionStringForSession which you can use to switch the database connection at runtime and in the current session. Take note that some conditions need to be met in order for the function to work properly:


- If there is a single query that uses the Database Connection with the old database before this action is executed, the switch to the new database will not happen until the end of the current request. Only in a next request the database switch becomes effective.
- The Connection String must connect to a database with the same type (e.g. Oracle, SQL Server, MySQL) as the one configured in Service Center for the Database Connection.
- Your license must include the Platform Extensibility APIs feature.

Hope this helps.

Regards,

Nordin

0
0
on 2020-10-12
Copy link to comment
Steve Webb
Rank: #19811

Thanks Nordin. I had seen this, but haven't yet tested it. Does this apply to a session level? So it doesn't change the actual connection string defined in in service center, but does change it for the current logged in user?


I'm expecting that multiple users will be logged in at once and could be querying different databases concurrently.


Nordin Ahdi wrote:

Hi Steve,

The PlatformRuntimeAPI has a function called DatabaseConnection_SetConnectionStringForSession which you can use to switch the database connection at runtime and in the current session. Take note that some conditions need to be met in order for the function to work properly:


- If there is a single query that uses the Database Connection with the old database before this action is executed, the switch to the new database will not happen until the end of the current request. Only in a next request the database switch becomes effective.
- The Connection String must connect to a database with the same type (e.g. Oracle, SQL Server, MySQL) as the one configured in Service Center for the Database Connection.
- Your license must include the Platform Extensibility APIs feature.

Hope this helps.

Regards,

Nordin

 

 

0
0
on 2020-10-12
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

The function changes the connection string for a specific external database connection, as configured in Service Center for the current session of a user. That is the reason why the function has an input parameter to which you need to pass the name of the database connection, as defined in Service Center. 

This way the platform knows to replace the connection string from the existing external database connection from Service Center, with the connection string of the new database as passed to the DatabaseConnection_SetConnectionStringForSession function - everywhere where that database connection is being used.

Regards,

Nordin

0
0
on 2020-10-12
Copy link to comment
Steve Webb
Rank: #19811

Thanks for your help on this. I've been testing this evening & am struggling to implement the runtime API action. So in a situation where I have the following:

DB connection name as defined in ServiceCenter: "AzureDBConn"

In ServiceCenter the connection has a server name of "DB1.database.windows.net" and database name of "DB1". I've tested this & confirm it works.


After login, I run the DatabaseConnection_SetConnectionStringForSession action:

Where ConnStr is a text variable defined as:

But I can't seem to get it to work. What am I missing here? it still returns the values from the original database.

This is a reactive web app if that has any bearing?

I've tested the full connection string and confirm it is working in SSMS.

0
0
on 2020-10-12
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

You might have the same problem as described in this post.

You need to make sure the entities you have imported in your extension do not include the database name ‘DB1’ as mentioned in this solution.

Regards,

Nordin

0
0
on 2020-10-12
Copy link to comment
Steve Webb
Rank: #19811

Hi Nordin Ahdi. Have just been back through this today. Can confirm I've made the changes correctly & removed [DB1].[dbo]. from the table definitions but still not working as expected.


Is there a particular time I should be calling this at? Right now, I am just doing it on the login button where it grabs a connection string from a table & updates over the DatabaseConnection_SetConnectionStringForSession action. I've also enabled logging & can see the API being called, but it doesn't have a success response to say whether it has actually completed or not.

0
0
on 2020-10-13
Copy link to comment
Steve Webb
Rank: #19811

Thanks. I actually came across that post earlier and made the changes but it had had no effect. Can post screengrab later to confirm.

Is there anyway I can check logs to see if the database connection has been successful?

Really stumped at this now.

0
0
on 2020-10-13
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

I’m not sure. It could have to do with the fact that you are doing this with a Reactive Web Application as those are based on a different technology compared to Traditional Web Applications where this Platform Extensibility API was originally designed for.

You could of course test the functionality within a Traditional Web Application first to see if it works over there.

Furthermore, did you make sure you have met the mentioned conditions from the API documentation?

- If there is a single query that uses the Database Connection with the old database before this action is executed, the switch to the new database will not happen until the end of the current request. Only in a next request the database switch becomes effective.
- The Connection String must connect to a database with the same type (e.g. Oracle, SQL Server, MySQL) as the one configured in Service Center for the Database Connection.
- Your license must include the Platform Extensibility APIs feature.

The first condition is an important one, as you need to make sure no query is executed using the default database connection (DB1) within a certain request, before you make the switch to the DB2 connection. Otherwise the switch would become effective only from the next request onwards.

The second condition I’m sure is met right?

For the third condition, login to Service Center >> Administration >> Licensing and look for ‘Platform Extensibilty API’ to check if its included in your license (for Personal Environments it is included).

Hope this helps.

Regards,

Nordin

1
0
on 2020-10-13
Copy link to comment
Steve Webb
Rank: #19811

Thanks for this. I should have thought about Traditional vs Reactvie earlier. Seems it is for traditional only as have just built a quick Traditional Web App test app and can cycle between connections easily using the same strings.

Any idea how I'd go about replicating similar functionality in Reactive? Or potential forge components which would help (have checked - nothing jumping out at me).


Thanks,


Steve

0
0
on 2020-10-13
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

I'll ask around to see if it is possible to get it working inside a Reactive Web App.

Regards,

Nordin

1
0
on 2020-10-14
Copy link to comment
Nordin Ahdi
mvp_badge
MVP
Rank: #73

Hi Steve,

I was pointed to the following solution which should work the same for Reactive Web.

Hope it helps you achieve what you want.

Regards,

Nordin

0
0
on 2020-10-14
Copy link to comment