web stats
Database writer keeps writing - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-20-2017, 07:13 PM
xiw022 xiw022 is offline
What's HL7?
 
Join Date: Aug 2017
Posts: 3
xiw022 is on a distinguished road
Default Database writer keeps writing

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

The problem is, I set the Interval in source to 5 milliseconds. And when I deploy the channel, it keeps inserting new data to the destination database, even if the data has been inserted before.

For example, if there is only one row in the source database, the channel inserting the row into the destination database every 5 millisecond, resulting in infinite rows in the destination database

Is there anyway to prevent this from happening, and only insert the data that are not in the destination database?
Attached Files
File Type: xml testTwoRow2.xml (23.3 KB, 3 views)
Reply With Quote
  #2  
Old 08-20-2017, 07:42 PM
xiw022 xiw022 is offline
What's HL7?
 
Join Date: Aug 2017
Posts: 3
xiw022 is on a distinguished road
Default Database writer keeps writing

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

Since I set Interval in source as 5 milliseconds, the channel insert new data into the destination database every 5 milliseconds, even if the data has been polled from the source database and be inserted before. This results in infinite insertion into the destination database infinitely.

For example, if I only have one row in the source database, the channel will insert the one row infinitely times into the destination database.

Is there any way to prevent from insertion of duplicate values(insert only if source has been updated and the value has not been inserted into the destination before).
Attached Files
File Type: xml testTwoRow2.xml (23.3 KB, 10 views)
Reply With Quote
  #3  
Old 08-21-2017, 12:15 PM
jridderhoff jridderhoff is offline
OBX.1 Kenobi
 
Join Date: Jan 2015
Posts: 37
jridderhoff is on a distinguished road
Default

Two problems: first, you're not filtering your query against anything in the source connector to only check for processed rows; second, you're not setting anything in your update query to indicate that you've processed the row.

What you need to do is have something in your source data that can be used to indicate whether the row has been processed by the Database Reader or not. For example, a boolean column `processed` could be defaulted to FALSE. Your source query could say something like "WHERE processed IS FALSE". Then, in your update query, you'd say something like "UPDATE table SET processed = TRUE WHERE id = ?".

Hope that helps!
Reply With Quote
  #4  
Old 08-21-2017, 05:23 PM
xiw022 xiw022 is offline
What's HL7?
 
Join Date: Aug 2017
Posts: 3
xiw022 is on a distinguished road
Default

Quote:
Originally Posted by jridderhoff View Post
Two problems: first, you're not filtering your query against anything in the source connector to only check for processed rows; second, you're not setting anything in your update query to indicate that you've processed the row.

What you need to do is have something in your source data that can be used to indicate whether the row has been processed by the Database Reader or not. For example, a boolean column `processed` could be defaulted to FALSE. Your source query could say something like "WHERE processed IS FALSE". Then, in your update query, you'd say something like "UPDATE table SET processed = TRUE WHERE id = ?".

Hope that helps!
Thank you for your reply! That helps a lot. But what if I don't have the boolean column in the source database. Since I'm trying to poll database from the hospital system? Is there any other way to achieve that? I assume Mirth should be able to somehow by default. Thank you!
Reply With Quote
  #5  
Old 08-22-2017, 03:32 AM
appsbyaaron's Avatar
appsbyaaron appsbyaaron is offline
Mirth Guru
 
Join Date: Nov 2011
Location: Florida, USA
Posts: 377
appsbyaaron is on a distinguished road
Default

Quote:
Originally Posted by xiw022 View Post
Thank you for your reply! That helps a lot. But what if I don't have the boolean column in the source database. Since I'm trying to poll database from the hospital system? Is there any other way to achieve that? I assume Mirth should be able to somehow by default. Thank you!
Because Mirth Connect it so versatile it allows you to code for your specific needs. There is no way it can anticipate every scenario. How would it know if a record has already been processed?? You're asking Mirth to think for you. There are far too many databases to code for every possibility.

You could create a separate database to house the record ids that you've already processed. Then when you need to process a record from the main database you can run the id by the new database and see if it's been run before.

There are several ways to get around this.
Reply With Quote
  #6  
Old 08-22-2017, 03:45 AM
appsbyaaron's Avatar
appsbyaaron appsbyaaron is offline
Mirth Guru
 
Join Date: Nov 2011
Location: Florida, USA
Posts: 377
appsbyaaron is on a distinguished road
Default

"This results in infinite insertion into the destination database infinitely"

Is this actually inserting rows or just hanging?

I would logger.info(expression); to see what the actual insert statement looks like. Place is just before the executeUpdate in the destination.

Something tells me this is formatted incorrectly. Might need to be something closer to the following.

var expression = "INSERT INTO Table_15 (id, name) VALUES (\'" +$('a_id')+ "\',\'" +$('a_name')+ "\')";
Reply With Quote
  #7  
Old 08-22-2017, 08:02 AM
jridderhoff jridderhoff is offline
OBX.1 Kenobi
 
Join Date: Jan 2015
Posts: 37
jridderhoff is on a distinguished road
Default

Quote:
Originally Posted by xiw022 View Post
...But what if I don't have the boolean column in the source database. Since I'm trying to poll database from the hospital system? Is there any other way to achieve that? I assume Mirth should be able to somehow by default. Thank you!
Quote:
Originally Posted by appsbyaaron View Post
...You could create a separate database to house the record ids that you've already processed. Then when you need to process a record from the main database you can run the id by the new database and see if it's been run before...
So yeah, as appsbyaaron alluded to, Mirth Connect itself is pretty open to users implementation, so it really is up to the implementer of the individual channels to ensure that records get flagged. Just too many different scenarios there to handle robustly enough.

If you don't have access to the database you're polling from to add a column (and if you don't have access to add columns, you may not even have access to update a column if one existed), I was going to suggest the same idea that appsbyaaron did: create a table in a database you can control (and that's accessible from the same Mirth Connect server that you're running this channel on) that can keep a log of record IDs that have been processed. You may want to give some consideration to database performance when you design that, because you probably don't want Mirth polling directly from the full record set every poll and having to cross-check every record ID to see if it has already been processed or not; the performance of that will probably be pretty sluggish.

If there are time stamps in the hospital's database associated with the records that can reliably be used for detecting new rows (i.e. a "date created" field), that might be a good way to start.

Might even be a good idea to do two channels: one whose job is exclusively to look for new records in the hospital's database and store their ID and a processed flag, and then another channel that can poll from that table and mark rows as processed or not.
Reply With Quote
  #8  
Old 08-22-2017, 08:57 AM
appsbyaaron's Avatar
appsbyaaron appsbyaaron is offline
Mirth Guru
 
Join Date: Nov 2011
Location: Florida, USA
Posts: 377
appsbyaaron is on a distinguished road
Default

Quote:
Originally Posted by jridderhoff View Post
...You may want to give some consideration to database performance when you design that...
Especially if you're polling the hospital database every 5 milliseconds. I mean I know the query fires off 5 milliseconds after the channel processes everything from the last query. It's a non stop query...of the whole hospital database for a name and id. At the hospital I currently work at that could be looking through several MILLION records non-stop all day long.

The timestamp filter is a great place to start IMHO. select id, name from table where timestamp is within the last 5 seconds. Or even create a channel map to hold the timestamp from the last run and use that as part of the query.

select id, name from table where timestamp < channelMap.lastTimestamp;
channelMap.put('lastTimestamp',getCurrentTimestamp );
Reply With Quote
Reply

Tags
database reader, database writer

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 02:23 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation