web stats
Database Reader is missing rows - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2018, 10:57 AM
cmpnit cmpnit is offline
What's HL7?
 
Join Date: Mar 2018
Posts: 3
cmpnit is on a distinguished road
Default 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 at 09:30 AM. Reason: moved comment to actual line it should be on
Reply With Quote
  #2  
Old 03-12-2018, 12:55 PM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 898
agermano is on a distinguished road
Default

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 at 12:58 PM.
Reply With Quote
  #3  
Old 03-14-2018, 09:40 AM
cmpnit cmpnit is offline
What's HL7?
 
Join Date: Mar 2018
Posts: 3
cmpnit is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 03-15-2018, 12:08 PM
cmpnit cmpnit is offline
What's HL7?
 
Join Date: Mar 2018
Posts: 3
cmpnit is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 03-15-2018, 02:05 PM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 898
agermano is on a distinguished road
Default

Glad you found the issue!
Reply With Quote
Reply

Tags
3.2.1, database reader

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 03:57 AM.


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