Executing Timer using PlatformRuntime_API not functioning as expected

Let me explain what I am building. We have a set of 5 databases which have common schema. So the table names are same across 5 databases and only the data in those tables differ. To read the data and store the data in the entity within a same server action, I am using PlatformRuntime_API DatabaseConnection_SetConnectionStringForSession to navigate to a database and read the data and store the details in a local entity and repeat for all databases. Now I have stored the connection details in a entity and using for each action, i pass the parameters for database connectivity and perform the same logic for all database. When I incorporate this within a Timer, what happens is that, the database connection establishes for the first connection and though the cycle for 2nd database starts, it is using the same connection of 1st database and fetching records from that db tables instead of 2nd DB.

Further checking I got to know Timer is asynchronous process and thereby it takes default values as when timer starts and thus switching DB connection does not happens... When I use 5 Timers, (each timer wakes at the end of first timer action) the functionality is working. But I want to incorporate it in a single timer. 

Is there any way to run the same timer again and again for different db connection? Is there any way to check whether the previous timer execution completed and wake the same timer again with different DB connection? Is there any way to close the DB connection?

How about to put entire Timer into a server action or function, calling that server action(or function) using a callback? callback is to make sure that process is finished and the session is ended. 

Hi Somesh Renganathan,

If you can see the documentation , they have mentioned 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.

I'm suspecting your first query is taking time, by the time first query execution ending 2nd one is starting that is the reason its taking same connection.


Sravan

Sravan Vanteru wrote:

Hi Somesh Renganathan,

If you can see the documentation , they have mentioned 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.

I'm suspecting your first query is taking time, by the time first query execution ending 2nd one is starting that is the reason its taking same connection.


Sravan

Hi Sravan, 

If the above was the case, let me explain the situation here. The connection is established and then a select query executes in the database which fetches around 30k records and then it stores them in a entity (CreateOrUpdate) and another select query runs with the outsystem entities and stores the result in another entity (CreateOrUpdate). So the select query has completed and fetched the results and moreover two operations with respect to OutSystem entities are also performed. Moreover I have also inserted Commit Transaction server action before the switch to next database server action to ensure request is completed. 

So the request has been completed (select query has finished executing  and we also make use of those results with the help of local entities)

I believe the request completed when select query returns the fetched results. 

Hi Somesh Renganathan,

Have look of below URl, it might help you, meanwhile I will check from my end and let you know

https://www.outsystems.com/forums/discussion/33437/change-in-runtime-the-external-database-connection/


Sravan

Sravan Vanteru wrote:

Hi Somesh Renganathan,

Have look of below URl, it might help you, meanwhile I will check from my end and let you know

https://www.outsystems.com/forums/discussion/33437/change-in-runtime-the-external-database-connection/


Sravan

Hi Sravan,

Thanks for the link. I have already referred that and I have used just the table names only ({tablename}.[columnname]) as suggested in that discussion. 

I feel that when a Timer session starts, it won't allow to switch the connection once established via PlatformRuntime_API to next connection, which should not be the case. When I use 5 Timers, it works which I feel can be made with a single Timer. Is there any way to end the transaction and ensure that transaction has been closed? FYI, I am connecting to an Oracle Database.


Solution

Hello Somesh,

As per the documentation (already mentioned):

1. Timer awakes your server action (is a request)

2. You start by setting the connection for the first database (it is working)

3. You do your queries using this connection.

4. You go back to the start and change the connection, but the active connection still is the first.

Why?

Because you did queries using the FIRST connection BEFORE changing the connection...

Repeating Sravan (from the 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.

So, there is NO way you can change connection inside the same timer, and make it work.

But...

You can do something "funny" that I think it will work.

For this, you will need to store in an entity the "active" connection (or the "you should use this connection" information). 

1. Timer awake your action

2. Retrieve the "use this connection" from the entity

3. Set the active connection for the one retrieved

4. Update the "use this connection" to the next one

5. You do your queries using the active connection.

6. At the end of the action check if the stored "use this connection" is not the "first one". If it is not, wake the timer. If it is, just end.

This way, you are using a "single" timer, but the timer will awake in sequence to deal with each connection.

Hope this helps.

Cheers.

Solution

Eduardo Jauch wrote:

6. At the end of the action check if the stored "use this connection" is not the "first one". If it is not, wake the timer. If it is, just end.

This way, you are using a "single" timer, but the timer will awake in sequence to deal with each connection.

Hope this helps.

Cheers.

Hi Eduardo, thanks for your detailed reply. It clarified most of my doubts about the timers. 

Considering your idea to implement, Is it possible to wake the same timer which is still in execution? (Though the WakeTimer action is the last one before End..)


Hi Somesh,

Yes. What the WakeTimer does is to set the timer record that holds the information of WHEN the timer must execute to execute "now".

When the timer finishes and the Scheduler Service pass through the database to see which timers must run, it will start the timer again.

Cheers.

Eduardo Jauch wrote:

Hi Somesh,

Yes. What the WakeTimer does is to set the timer record that holds the information of WHEN the timer must execute to execute "now".

When the timer finishes and the Scheduler Service pass through the database to see which timers must run, it will start the timer again.

Cheers.

Hi Eduardo,

Your funny idea finally worked!! I was able to accommodate the process which required 5 timers in a single timer. I introduced a Flag column and initiated it with False for all the connection. Whenever a connection is used, changed its flag to True and at the final connection, If the aggregate with a filter condition False was not available, then timer ends else wakes up the same timer again..

Thank you very much!!

Hello Somesh, glad this helped! :)

Thanks Eduardo, 

I too learned along with Somesh.


Sravan