web stats
Database reader real-time? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2017, 10:48 AM
psanders psanders is offline
OBX.2 Kenobi
 
Join Date: Nov 2011
Posts: 55
psanders is on a distinguished road
Default Database reader real-time?

Hello all, using Mirth Connect Server 3.5.0.8232

I have a table that stores patient survey results all through the day. The volume of data is such that a new record occurs on average every 10 seconds, but can occur as often as every second or even milliseconds. Currently I've built a channel that uses a database reader doing the following command:

SELECT * FROM WEBSURVEY.MEVAL_HL7_SOURCE_2 WHERE UPDATE_DTM > (SELECT UPDATE_DTM FROM WEBSURVEY.MEVAL_MIRTH) ORDER BY UPDATE_DTM

and a post process SQL command:

UPDATE WEBSURVEY.MEVAL_MIRTH SET UPDATE_DTM = SYSDATE

I have some source transformers that take the records and builds an HL7 message to be stored in a different table:

INSERT INTO U0690432.QUEUE_MEVAL_OUT_VARCHAR
(ASSESSMENT_ID, MESSAGE_ID, MESSAGE, ENTRY_DT)
VALUES
(${survey_id},${message.messageId},${message.encod edData},to_date(${sysdate},'YYYYMMDDHH24MISS'))

The question I have is how to not lose data? In other words, I'm pulling any data since the last time it ran by reading the sysdate I stored in the separate table, but if my initial query takes longer than a second or two, another record may have been stored before I can write the sysdate to the separate table to keep track of where I need to read the next time? Hopefully this makes sense. I guess I'm wondering at what sequence of operations does this post process SQL run? I've set it to "After each message".

Is there a better way to build this channel?
Attached Files
File Type: xml mEVAL_1_DWtoHL7Queue_v4.xml (43.8 KB, 6 views)
Reply With Quote
  #2  
Old 08-10-2017, 12:08 PM
pacmano pacmano is offline
OBX.2 Kenobi
 
Join Date: Oct 2009
Location: Texas
Posts: 73
pacmano is on a distinguished road
Default

Presumably you have a "created_at" column in your table of survey answers of type datetime.

If your SQL returns a result set such that you return this max created_at date, you can use that value in your "start from" reference table for the next call. In other words don't use now() as a reference point, use a column in the table.

Or (in postgres for example) always have the initial column be of type bigserial, and record that max number as the next start point, +1 of course.

I hope that makes sense.
__________________
Mirth 3.7.1 / Postgres 10 / Ubuntu 18.04
Diridium Technologies, Inc.
https://diridium.com
Reply With Quote
Reply

Tags
database reader, hl7

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 08:39 PM.


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