How to reconnect to the Database if the connection fails in between running the DB Queries?

Help Activities
, , , ,
1

I created a Flowchart and in my 1st Sequence I am connecting to the Database and running a select and update queries, then it moves to the 2nd Sequence and run some other queries.

My question is when it moves to the 2nd Sequence and try to run some queries but the Database connection drops then it would fail the test case, how to overcome this issue?

I want to make sure the database connection is connected and if in between the connection drops then it should again retry to connect to the Database.

What is the best way to handle this scenario? any suggestions??

2

most likely when you run a query which return many rows, it may cause connection timeout

In case you can separate these query via adding how many rows to get, maybe 10000 rows, and then use another query to get another 10000rows

Please also consider to drop some join table if not required.

3

Hi @achawla

if you are updating large number of records in the 1st sequence, use delay for few seconds before moving to 2nd sequence.

Also surround the query in 2nd sequence with a retry activity.

Hopefully this might solve the issue

4

I am not trying to update large number of records its just 1 account i am updating a value.
But I want to cover a scenario where in case if the DB Connection drops in between i want to use an activity to handle that.
How can I use a Retry Scope? I mean I should just add the connect Db under Activity and under condition what should I write? because I didn’t get any error yet. Also in condition the expression should return a Boolean values which I am not sure how to give with Database scenario?

Below is the screenshot where you can see in the 1st Sequence i have added the Db connection but in 2nd Sequence (Validate Batch ProcessStatusTypeId) I am just calling the same existing connection. is there a way i can add a Retry Scope here?

I have attached the file below for your reference.

CAM-Batch Processing(Autosaved).xaml (131.2 KB)

5

the update query i am using just updates a single column for a specific account. Its just as a precaution i would like to use something for DB connection incase it drops in between while running the 2nd or even 1st Sequence. Is there something i can add it to the workflow itself for Db Connection?

6

here Activities - Retry Scope you will find how to use retry scope.

My suggestion is not to do this. :point_up_2:
Disconnect the existing connection after the query execution in 1st sequence and reconnect for query in 2nd connection, then disconnect after query execution in 2nd sequence. Following this will reduce the probability of getting timed connection out error.

Hope this is helpful

7

I didnt try this one before therefore dont know does it work or not.

Could you try connect the DB everytime before you execute the query? And dont need to care did it connected or not.