DatabaseConnection_SetConnectionStringForSession for a Mobile application

Is there a way for a MOBILE application to change the connection string for the External SQL Database connection at runtime?


I have multi-tenant WEB and MOBILE applications which interface with an external SQL database defined in ServiceCenter/Administration/Database Connections.  Each tenant has a different external SQL database with the same schema.

My WEB application calls DatabaseConnection_SetConnectionStringForSession from the PlatformRuntime_API to switch the connection after login. This is working well and my WEB app can successfully read/write data from their own external SQL database.

Now I am trying to extend the same functionality to my MOBILE application. But ... the value set from DatabaseConnection_SetConnectionStringForSession is being ignored, and all data is read from the same default tenant. 

This means that I haven't been able to deploy my multi-tenant mobile application to any other tenants?

Can anyone help me with this?

Thanks,
Alan

Solution

Here is the reply we got from João Barata in OutSystems support.
Note - the key seems to be using "Fetch data from other sources option" inside the mobile application. 
And even better - it appears to remember the connection string for subsequent requests. We didn't have to set the connection before every request, but just once after login. (caveat - we are still testing the solution, but looks good so far)


Thank you for reaching out to us.


The "DatabaseConnection_SetConnectionStringForSession" action does seems not to work in Mobile. However what is happening is that in Mobile eSpaces the session is not persisted between server requests, and therefore the change is not persisted.


Mobile eSpaces don't have session variables visible. But this does not mean the session variables have been completely removed from Mobile eSpaces in the platform, they still exist in references and extensions. This enables the possibility to have WebResponsive Espaces that have session variables and expose actions that are referenced from Mobile eSpaces. In these scenarios, the session variables exist can be assigned and read, but are not persisted between server requests.


Please let us present a possible solution. If you change the Connection in each request you should be able to get the proper outcome. Please feel free to explore more in the example below: 


The idea is that instead of using an aggregate to fetch the server database, you can use the "Fetch data from other sources option" and execute the database DatabaseConnection_SetConnectionStringForSession before calling the Aggregate or Advance Query to fetch the records. This needs to be done for each server request that you need to switch the database connection string.

We've created also a product defect to fix the documentation since it's not so clear in how to achieve this step.

Please let us know if this communication is clear and don't hesitate to ask any question that remains.

Best Regards,
João Barata | Customer Success

OutSystems


Solution

Hi Alan,

We are tryinng to connect to another MS SQL server database at runtime using DatabaseConnection_SetConnectionStringForSession but its not working.

In our scenario we have 2 tables in 2 different databases with same name and attributes in MS SQL server

after using this action we are unable to connect to the other database, its still fetching data from default database.


Thanks,

Sachin.


Hi Sachin.

I can confirm that the suggestion from OutSystems works. We are able to set the connection string at runtime.

Note - it doesn't seem to last permanently for a user. For safety sake we are setting the connection string each time before we read from our external SQL server. 

Note also that in the documentation it states that you must SET THE CONNECTION STRING before accessing the database in this request. 

i.e. you cannot connect to different databases in a single request. You must set which database at the START of the request.

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

Note the conditions specified above:

- 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.

In our case, we decided to set the connection string a the START of the current request.

Regards,
Alan

Hello Alan,

In my web appl, I am trying to switch to the the external database using DatabaseConnection_SetConnectionStringForSession from PlatformRuntime API. My database is Oracle.

I have set the connectionName, connectionString, databaseIdentifier. Even if the setting contents are correct/incorrect,  it can not be understood,  as wrong connection names will not throw any error (here).

I will try to describe what i am doing:

First the user will be in one screen. This screen uses preparation  to display table's data. Within the same screen, there is a button named SwitchDatabaseConnection. 

When "SwitchDatabaseConnection" button is pressed, the connection should be changed to external database  and the table data will be displayed from the external database, but the switching is not working and data is displayed from same database as before.

Can you please tell me how to set the connection string and if possible can you give the sample oml file?

Regards,
Alam