26
Views
11
Comments
Dynamic connection string for external databse

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?

mvp_badge
MVP
Rank: #74

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

mvp_badge
MVP
Rank: #74

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

mvp_badge
MVP
Rank: #74

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

Rank: #11608

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.

mvp_badge
MVP
Rank: #74

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

Rank: #11608

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

mvp_badge
MVP
Rank: #74

Hi Steve,

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

Regards,

Nordin

mvp_badge
MVP
Rank: #74

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