Announcement

Collapse
No announcement yet.

Database Reader is missing rows

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

  • Database Reader is missing rows

    Running Mirth Connect 3.2.1.7650.

    I have a Database Reader polling a SQL Server database ("Database A") on the default interval of 5000 ms. A JavaScript filter step checks a different database ("Database B") (on the same server as the first db) and filters based on what it finds. A single Transformer step maps a channel variable, and a Database Writer destination connector writes to Database B. I also have a small utility channel that accepts (as raw text) a primary key value which is processed through a transformer step to run a query on Database A that is nearly identical to the one run on the main channel, then routes the XML result to the main channel.

    This all works fine for the most part, but as my channel is seeing more frequent use I'm finding that some data is not getting into Database B. When I look at the message history, I'm seeing that there are no messages for the rows whose data is missing from Database B.

    In one or two cases this has been due to my query of Database A not catching the rows due to peculiarities in A's design, but in most cases I have been able to run my utility channel on specific keys in order to process those database records that were missed by the polling.

    In Google searching I found someone else had posted a similar issue, but they never got any replies: http://www.mirthproject.org/communit...d.php?t=215376. Relevant code below. Apologies for the variable name abstraction, a vendor database is involved and my knowledge of its schema is under NDA.

    Database Reader SQL:
    Code:
    SELECT
    	t.PK
    	f.ColA
    	s.ColB
    	t.ColC, //datetime
    	t.ColD,
    	t.ColE,
    	t.ColF,
    	t.ColG
    FROM dbo.MainTable t JOIN dbo.FirstJoinTable f ON t.FirstJoinPK = f.PK
    JOIN dbo.SecondJoinTable s ON f.SecondJoinPK = s.PK
    WHERE t.ColC > ${LastColCValue} AND t.ColD IN (1,2,3,4,5)
    ORDER BY t.Time //NOT ColC
    Utility Channel Source Transformer (JS Step):
    Code:
    var dbConn;
    var result;
    var selectMainTable = 
    	"SELECT t.PK, f.ColA, s.ColB, \
    	t.ColC, t.ColD, t.ColE, \
    	t.ColF, t.ColG \
    	FROM dbo.MainTable t \
    	JOIN dbo.FirstJoinTable f ON t.FirstJoinPK = f.PK \
    	JOIN dbo.SecondJoinTable s ON f.SecondJoinPK = s.PK \
    	WHERE t.PK = ? AND t.ColD IN (1,2,5,6,7,33) \
    	ORDER BY t.Time";
    	
    var paramList = getNewArrayList(); //custom utility function in Code Templates
    paramList.add($('MainTablePK')); //mapped in preceding Mapper step
    
    try {
    	dbConn = getConnection(true); //custom utility function in Code Templates
    	result = dbConn.executeCachedQuery(selectMainTable, paramList);
    	if(result.size() == 0) {
    		logger.info("MainTable record not found" + " (MainTablePK: " + $('MainTablePK') + ")");
    		return;
    	}
    
    	while (result.next()) {
    		tmp['PK'] = result.getString("PK");
    		tmp['ColA'] = result.getString("ColA");
    		tmp['ColB'] = result.getString("ColB");
    		tmp['ColC'] = result.getString("ColC");
    		tmp['ColD'] = result.getInt("ColD");
    		tmp['ColE'] = result.getString("ColE");
    		tmp['ColF'] = result.getInt("ColF");
    		tmp['ColG'] = result.getString("ColG");
    	}
    } finally {
    	if (dbConn) {
    		dbConn.close();
    	}
    }
    Last edited by cmpnit; 03-14-2018, 09:30 AM. Reason: moved comment to actual line it should be on

  • #2
    Some questions...

    Is t.Time when messages were received?

    Is t.colD always increasing as for each message received or can they be out of order?

    How is ${LastColDValue} determined? Is it a globalChannelMap variable? How is it initialized after the server restarts?

    I thought t.colD was a datetime. What is the purpose of "AND t.ColD IN (1,2,3,4,5)" in the WHERE clause? t.colD is maybe a typo in this section?
    Last edited by agermano; 03-12-2018, 12:58 PM.

    Comment


    • #3
      My apologies, ColC is the datetime, I have edited the code in my OP.

      ColD is not an incrementing value, it's a coded value, and in fact the actual numbers in the actual code are not a gapless sequence. It's something more like (1,2,5,6,20) (but not that). This field has a semantic meaning in the vendor database -- it can be other values, but I only care about the specific values in my query.

      ${LastColCValue} (not D) is a globalChannelMap variable. When I restart the server I just set it in the Deploy script as the current time. ...Wait. It's actually a globalMap variable, but it really shouldn't be. I think this may actually solve the problem. I have another channel that also uses LastColCValue, the fact that it's in the global map vs. the global channel maps is probably causing unintended side effects. I'll make that change and see what happens.

      Comment


      • #4
        I think this fixed it! Only two patients today which is a regrettably small sample size, but given how often it was happening I would have expected at least one lost record, and there were none.

        Thanks for forcing me to look at my own bad code, agermano.

        Comment


        • #5
          Glad you found the issue!

          Comment

          Working...
          X