web stats
Mirth Community - View Single Post - Database Reader is missing rows
View Single Post
  #1  
Old 03-07-2018, 11: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 10:30 AM. Reason: moved comment to actual line it should be on
Reply With Quote