Announcement

Collapse
No announcement yet.

Database reader real-time?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 IGNORE 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

  • #2
    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.8.0 / PostgreSQL 11 / Ubuntu 18.04
    Diridium Technologies, Inc.
    https://diridium.com

    Comment

    Working...
    X