Mirth connect database to database duplicate

Question

I'm currently developing a channel from Database reader to Database writer. Both the source and destination connectors are running on SQL server.

The problem is that by setting Interval in source to 5 milliseconds, the channel polls and write data every 5 milliseconds, even if the data has been polled or written before.

For example, if there is only one row in the source database, the channel will keep inserting the one row into the destination database every 5 milliseconds infinitely. This results in infinite duplicate rows in the destination database.

Is there any way to avoid such situation and only insert into destination only if the data polled is new?

Answer 1

You didn't post your channels so I don't know what your queries look like. Also you didn't post the db schema. In general what you need to do is implement a way to keep track of what records have been processed. One way to do this by adding a column to the relevant tables that has a default value of 'NEW' when new records are inserted. When you have processed them (e.g. copied from one db to the other), you set the status to 'COPIED' via an update statement. optionally you could also have a column with a timestamp that indicates when the record was copied. Another (probably better) way is to do this in a separate table that keeps track of all mutations in the database. This gives you more flexibility in what events you record.

Answer 2

Two ways I can think of :

1. usual way is to have a flag set in the table of the DB you are reading, Once the data is read from that table by mirth, you will update the table with "Read" or "Processed". Then you will be writing a fetching query based on that flag, so it will insert data only if the flag is not equal to "Read"/"Processed"

2. In case changing DB is not in your hand, You can write query in the to fetch data like this "SELECT * FROM Table ORDER BY ID DESC LIMIT 1". ID being your Primary one.