[ServiceCenter] Database connection - programmatically switch database connections?

When you import a database via integration studio, and publish your extension, you will need to login to "ServiceCenter" and select the logical database connection for the database you would like to work with.

Problem: usecase 
1) When developer uses his sandbox/test API credentials and consumes an expose web service, all database CRUD actions and SQL query is to be made to logical database connection "A"
2) When developer uses his production API credentials and consumes an expose web service, all database CRUD actions and SQL query is to be made to logical database connection "B"

Both database "A" and "B" are identical, A is used to store test data, B is used to store production data.

Why not just run two servers, one for sandbox and one for production? (higher cost to maintain additional software and hardware; and unnecessary server configurations etc) the only difference is the data!

Solution:
What we need is to be able to switch between test and production logical database connection programmatically within the code - but how do we do this with outsystems when the database connection configuration is fixed in service center?

Check out runtime api i believe. Or database api. There is the simple method SetDatabaseConnectionForSession... In a train, so don't know the exact details...

thanks J, ... it sounds like that could work!!?

it sure does.
It's PlatformRuntime_API, DatabaseConnection_SetConnectionStringForSession

We are using it for Proxy-users atm, different schema's etc.
And I have done it to switch database alltogether.

hello J, for some reason I can not find the method mentioned. which outsystems platform are you running?

Hi, it should be 9.0 I think there is something similar in 8, but perhaps our guru Joao Rosado can help out for 8 if you still using that 
https://www.outsystems.com/help/SErviceStudio/9.0/APIs/PlatformRuntime_API/PlatformRuntime_API.htm

lol, pretty sure that Robert is not on Platform 8  :P

Anyway just for the record, the method was the same there, just the extension name was different (RuntimePublic extension if I remember correctly).

Regards,
João Rosado

J and Joao

My bad, for some reason at 3 am in the morning, I did not see it! I woke up 5 hours later and checked again, it's right there! (Same place where J told me to look) :/




 

J, Joao

"DatabaseConnection_SetConnectionStringForSession" does not seem to do anythiing? It did not switch database. No error either? assigning different databases doesn't do anything.

MySQL Server
DatabaseConnection_SetConnectionStringForSession
connectionName: <same name as configured in service center>
connectionString: <Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;>
databaseIdentifier: 


Both databases is on the same mysql server! I only change the connection string to switch between databses, it does not work, in fact it doesnt do anything.

if you read the help, it's only effective at the first "new" transaction.

so if you do
- SQL
- switch connection
- SQL

it won't work, since it will be in the same transaction!

J

For testing purposes
I switched the connection followed by a SQL query and it does not perform the query via the new database connection.

 

strange.

what if you change the connectionstring to garbage.
it should throw an error.
if not, guess it's not implemented indeed for mysql...

J. wrote: I tested it again and I configured the connection string to some garable connection string, I even gave the connection name the wrong name - nothing happen, no error, no nothing!
 
DatabaseConnection_SetConnectionStringForSession just does nothing in MySQL! no matter how its configured.
 its not implemented in MysSQL?

According to the "DatabaseConnection_SetConnectionStringForSession"description it seems it has been implemented to support MySQL?

==================

Hi Robert,

Wrong connection names will not throw any error.
An invalid connection string will throw an error when a query using it is called.

What connection name are you using? It needs to match the name of the connection as defined in Service Center (case sensitive).

Can you also make sure that the first condition is valid in your test?

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

Like J said, if in your test you are doing:

SomeQuery to that database
SetConnectionString
SomeOtherQuery to that database

Both queries will use the initial connection string, as documented, regardless of what you did in the SetConnectionString.


Edit: just double checked and it is implemented for MySql

Regards,
João Rosado

Hi again,

Another thing you need to check is that your entities in integration studio CANNOT be prefixed with the database name.

(Not sure how they are imported by default on MySQL, but here is an example using MSSQL)

By default a table will be inported as:
   MyCatalogOne.dbo.MyTableName

If I want to switch the connection string to another in runtime I'll have to remove the "MyCatalogOne." part at least. Or it will either fail due to user permissions, or just work if the user has permissions for both catalogs.


Regards,
João Rosado

Joao

It does not work! I attempted to enter the wrong connection string, no exception is raised.

MySQL Server:
MySQL Database Name: platform

Service Center:
MySQL Connection name: PlatformModel

Extension:
MySQL Extension name: PlatformModel
MySQL Extension Logic name: Platform

https://goo.gl/eK7KDM

DatabaseConnection_SetConnectionStringForSession
connectionName: PlatformModel
connectionString: Server=localhost;Database=platform;Uid=myUsername;Pwd=myPassword;
databaseIdentifier: 

João Rosado wrote: Invalid connection name and invalid connection string did not throw an exception error. Nothing happens when using DatabaseConnection_SetConnectionStringForSession
 

@Joao

I am still unable to resolve this issue, into DatabaseConnection_SetConnectionStringForSession method for MySQL server is non-functional in BALI BETA 2.
 
Is it because my name is called "Platform"? or MySQL code was not implemented into BALI BETA 2?

Hey Robert,

Name: country

Table or View Name: `country` (remove the name of the database that is before the table name!)

Logical Database: platform

• Publish the extension that you have just created
• Go to Service Studio, in your module, add the 'DatabaseConnection_SetConnectionStringForSession' action call to the flow before any other data query with the following arguments:

connectionName: "PlatformModel" (the connection name of the database where the table is initially)
connectionString: "Uid=<username>; Pwd=<password>; Server=<server>; Database=<database name>;" (NOTE: the connection string information is about the database to where to want to switch)
database identifier: 

• After the action call you can add the queries and publish.

Lara Luís wrote:  I believe this is the reason why it did not work! the database name is in the table name, therefore its always going to communicate with the database that was used to import the tables.

However, I am still puzzled as to why an invalid database connection string, will not throw an exception error?


Also is there a way to import the database tables with the name of the database removed? (it's alot of work to remove the names for 200+ tables)

Yes, it should be throwing an exception, unfortunately it doesn't. I will add it to the backlog.

About the import of database tables, sorry, there's no way to do it.

Regards

Lara

I found two more bugs:
Bug #1 if you remove the database name from the "Table or View Name" field, and attempt to import the database again, you break the sync feature, integration studio will create new tables! 
Bug #2 if you rename the "Table or View Name" back to the original name (after changing it), integration studio will crash and the following error message is returned.

Lara

"DatabaseConnection_SetConnectionStringForSession"

Is it possible to change the "Table or View Name" to the database name specified in the database string? (any work around)?

Example, If your original imported database was "mydatabase" and in your "DatabaseConnection_SetConnectionStringForSession" you specified the database string name "Database=mydatabase2;" 

The table or view name will change 
from `mydatabase`.`profile`
to `mydatabase2`.`profile`

(Without manually renaming each table/view, since renaming breaks the import/refresh/sync feature).

Lara

Overall, I just would like to switch databases, and also be able to re-import tables/views in the future without breaking the sync feature or crashing integration studio.

Hey Robert,

I tried to reproduce your error but the table refresh worked for me without any issues. However, I confirm that, when re-importing the table again, it is duplicated. What is your scenario for re-importing the tables instead of just refreshing them?

I will add this issue to our backlog and we will try to address it whenever it is possible.

Thank you for your feedback,
Regards

Lara
 
"What is your scenario for re-importing the tables instead of just refreshing them?"

When you import many hundreds of tables, you would not be able to easily track the tables you've previously imported (you have too many tables), therefore what you would do is import all tables! however since you renamed the table/view name, integration studio will create new duplicate tables (there are a number of ways this problem could be solved).

Okay, now I understand your scenario. I agree that this behaviour is a bug so, we will not keep the current behaviour. We will add it to the backlog. prioritise and fix accordingly.

Regards

Please, correct me if I'm wrong... Just to complement this discussion, another way (workaround) to achieve this would be use the Advanced Logic Canvas to set the Logical Database Connection:

/* %LogicalDatabase%=GetLogicalDatabase({Entity Name}) */

Hi André,

Those are for a different use case.
The DatabaseConnection_SetConnectionStringForSession is to change the connection string of one Database Connection.
The "%LogicalDatabase%=" is to be used when the advanced query does not contain any {entity}, so we cannot determine what is the correct Database Connection. Actually don't know what is the expected behavior when there are actually {entity} inside the query as well.

Regards,
João Rosado

Joao

What happens with the query cached when the database is switched from one database to the other?
(Does it clear?)

Hi Robert,

The caches in queries/actions do not depend on the active User and/or Session (apart from the TenantId), so they are also not affected by this action.
If you want to cache the information from those queries my recommendation is to put it inside an action with an input that represents the active database (and all other inputs necessary for the query) and set the cache on that action instead.

Regards,
João Rosado