Connection with external database and sessions

Rank: #12145

Solved

Connection with external database and sessions

Question

I use an external database for storing and retrieve all the information from all of our applications. Every application has a dedicated database. We now want to use just one Oracle database for all the applications.

Is it possible that the external database knows which end user is accessing the data via a query?

And is there a way in OS to make sure that when a database connection is pulled from the pool and made available to a user session to execute a SQL or stored procedure to initialize prepare the database session?

For example initialize a package in the oracle database to set the context, execute a query (with that context) and then retrieve the data? Has this example always the same database session?

on 2020-11-30

Hans

Rank: #361

Solution

Hi Ties,

a nice challenge. you can indeed call a stored procedure to set the context of the user in the oracle database. You can do it in multiple ways. one way is by using the Oracle Connector (https://www.outsystems.com/forge/component-overview/6636/oracle-connector-p11). another way is by calling a function within an sql select statement like 'select setContext(user=> 1231) from my_dummy_view' (where you have to import the view my_Dummy_view in integration studio), and catch the result (e.g. true / false).

OutSystems uses the same database connection/transaction per client request (where your first server action starts). The transaction is committed at the end of the client flow. There are some special cases:

- a service action creates its own new transaction

- a webservice action creates its own new transaction

- bpt creates its own new transaction.

- timers creates their own new transactions as well.

You have to set the context for each request. In traditional you might use the on-begin-web-request action.

Good luck, Hans

on 2020-12-01

Training

Documentation

Connection with external database and sessions