[ServiceCenter] Database connection - programmatically switch database connections?

Robert Chanphakeo

Solved

Question

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?

14 Sep 2015

MVP

Solution

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.

See solution in context

14 Sep 2015

MVP

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

14 Sep 2015

Robert Chanphakeo

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

14 Sep 2015

MVP

Solution

14 Sep 2015

Robert Chanphakeo

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

14 Sep 2015

MVP

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

14 Sep 2015

João Rosado

Staff

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

14 Sep 2015

Robert Chanphakeo

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) :/

16 Sep 2015

Robert Chanphakeo

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.

22 Sep 2015

MVP

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!

22 Sep 2015

Robert Chanphakeo

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.

22 Sep 2015

MVP

strange.

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

22 Sep 2015

1 reply

22 Sep 2015

Show thread

Hide thread

Robert Chanphakeo

J. wrote:

strange.

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

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?

22 Sep 2015

Robert Chanphakeo

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

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

Switches a Database Connection from one database to another at runtime and in the current Session.

There are some conditions to which you have to pay attention to when using this action:

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

22 Sep 2015

João Rosado

Staff

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

22 Sep 2015

1 reply

23 Sep 2015

Show thread

Hide thread

Robert Chanphakeo

João Rosado wrote:

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.

Invalid connection name and invalid connection string did not throw an exception error. Nothing happens when using DatabaseConnection_SetConnectionStringForSession

23 Sep 2015

João Rosado

Staff

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

22 Sep 2015

Robert Chanphakeo

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:

23 Sep 2015

Robert Chanphakeo

@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?

28 Sep 2015

Lara Luís

Hey Robert,

I am not able to reproduce your problem but I think I can help you by indicating what you need to do step by step:

Import your table using Integration Studio and ensure that you have something like:

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.

Please try it and let me know the results!

Thank you for your feedback.

28 Sep 2015

1 reply

28 Sep 2015

Show thread

Hide thread

Robert Chanphakeo

Lara Luís wrote:

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

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)

28 Sep 2015

Lara Luís

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

28 Sep 2015

Robert Chanphakeo

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.

28 Sep 2015

Robert Chanphakeo

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

28 Sep 2015

Robert Chanphakeo

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.

28 Sep 2015

Lara Luís

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

29 Sep 2015

Robert Chanphakeo

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

29 Sep 2015

Lara Luís

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

29 Sep 2015

André Siébra

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}) */

13 Oct 2015

João Rosado

Staff

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

14 Oct 2015

Robert Chanphakeo

Joao

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

07 Feb 2016

João Rosado

Staff

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

10 Feb 2016

ravikumar somachar

Hi Rosado,

I am trying to switch between the oracle database, can you please share the screen shot for DatabaseConnection_SetConnectionStringForSession configuration, the format of the connectionstring. I am not getting error and it is not working I used this format for connectionstring property

connectionString: "Uid=<username>; Pwd=<password>; Server=<server>; Database=<database name>;"

Thanks
Ravikumar S

10 Feb 2022

2 replies

Last reply 11 Feb 2022

Show thread

Hide thread

João Rosado

Staff

Hi,

If something is wrong the error should appear on the next query you use after setting the connection.

Ensure that you also use the correct name of the DB Connection in the call. You can try an invalid connection string to try forcing an error.
Also note that as said in the action comment, the action has no effect for queries on the same request if that database was already accessed by any during the current request.

Regards,
João Rosado

11 Feb 2022

ravikumar somachar

Thanks for the quick reply Rosado,

I have used the same database connection name which I have configured in the service center,

below are the screenshots of the connection string used and error I am getting, I tried with logical database name in the integration studio, I was getting always data from one databse, then I removed the logical database name, then I am getting "error executing query" I have copied the error stack,

please let me know what mistake I am doing,

Replying to João Rosado's comment on 11 Feb 2022 11:16:05

Regards
Ravikumar S

11 Feb 2022

João Rosado

Staff

Hi,

The suggestion I made on the other post was to make a invalid connection string, just to see if it has any effect on the query call. Ex: a host that does not exist to see if a connection error happens instead of the table not found.

I can also see from the screenshots that you are not passing anything on the database identifier. That means it only works if the user in the connection string is the schema owner of the table. If your connection string uses a runtime user with lower permissions (that is not the schema owner) then you also need to pass the schema name on the database identifier parameter for it to look for the table in the correct place.

Please also ensure that the user that you are using has permissions to select the table.

Regards,
João Rosado

16 Feb 2022

1 reply

16 Feb 2022

Show thread

Hide thread

ravikumar somachar

Hi João,

As you suggested, I tried with wrong password to connect, I got exception, on the front end saying “connection timed out”. exception logged

After passing the correct info, and setting the database identifier always getting the data from the same database location Dubai

On change of other location getting error. Is this something related to oracle database permission issue.

Here is the connection string passed to the DatabaseConnection_SetConnectionStringForSession action. Let me know is this excepted format of the connection string for oracle.


"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAZURE)));User Id=userid;Password=pwd;" 

I connected directly to OracleSQL and able to select the data from both database with 
same user name and password.

Regards

Ravikumar S

16 Feb 2022

João Rosado

Staff

Hi,

That test proves that the action is behaving as expected, and the connection string used for the query is being changed by the call correctly. The format you said also looks fine.

As for the last error screenshot, searching on google, ORA-01435 means that what you passing on the database identifier (to be used as schema) does not exist. Make sure it is correct and with the right capitalization.

Regards,
João Rosado

17 Feb 2022

1 reply

17 Feb 2022

Show thread

Hide thread

ravikumar somachar

Hi João ,

I passed database identifier has input parameter, and passing different identifier based on the location selection, now I am not getting any error but always getting the records from same database.

in integration studio I have removed the database name as suggested in the forum.

Please suggest..

Regards
Ravikumar S

17 Feb 2022

João Rosado

Staff

Don't see anything wrong from what you explained.

As long as you are calling the SetConnectionStringForSession once per request and before any query it should give you results for the tables that you are pointing to. Forcing the errors before proves that it's taking effect as expect.

I would suggest to check in the database side the queries that are reaching there to understand what is happening behind the scenes.

Regards,
João Rosado

17 Feb 2022

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines