web stats
Filter results based on SQL result - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 07-12-2018, 06:23 PM
Mits87 Mits87 is offline
Mirth Newb
 
Join Date: Nov 2016
Posts: 24
Mits87 is on a distinguished road
Default Filter results based on SQL result

I am reading a txt file and converting it to HL7 message. The file name is the patient ID. Once i process that file i want to create database entry with patientID and Testtime and if i receive the same file i want to filter that message.

The file i receive have data like:

Code:
0,20703,6,17,18,18,30,0,0,1,167,0,0,0,0,0,0,0,0,0,0,0,0,0,0
My code is as below:

Code:
var Year = msg['row']['column5'].toString();
var Month = msg['row']['column3'].toString();
var Day = msg['row']['column4'].toString();
var Hour = msg['row']['column6'].toString();
var Min = msg['row']['column7'].toString();
var FullTestTime = Year + Month + Day + Hour + Min;
var FileName = sourceMap.get('originalFilename');
FileName = FileName.replace(".txt", "");

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
	'net.sourceforge.jtds.jdbc.Driver',
	'jdbc:jtds:sqlserver://localhost:1433/Mirth',
	'Mirth',
	'123'
);
// Check if the result was already processed if SQL return value meaning that it was processed  
var sql = "select PatientID from ReceivedResult where TestTime = '" + FullTestTime+"'";
var results = dbConn.executeCachedQuery(sql);
var Fresult = results.next();
logger.info('SQL Result :' + Fresult);
if (Fresult == 'true' ) 
{
return false;
}
else 
{
var InsertADT = "INSERT INTO ReceivedResult (PatientID,TestTime) VALUES ('"+ FileName +"', '"+FullTestTime+"')";
dbConn.executeUpdate(InsertADT);
}
dbConn.close();
The issue i am facing is it always filters the result and never let it throw can someone point what is wrong in my code.
Reply With Quote
  #2  
Old 07-12-2018, 07:10 PM
ligamo ligamo is offline
Mirth Newb
 
Join Date: May 2018
Posts: 10
ligamo is on a distinguished road
Default

If you have some code in filter, maybe the default return is false, so you need to return true somewhere.
Reply With Quote
  #3  
Old 07-12-2018, 09:53 PM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 761
siddharth is on a distinguished road
Default

a) This is not the right boolean check.
Code:
if (Fresult == 'true' ) 
{
return false;
}
b) if you want to iterate over the result set which is results variable in your code, you do
Code:
while(results.next()) {
//Your code here
}
EDIT- deleted wrong info
__________________
HL7v2.7 Certified Control Specialist!

Last edited by siddharth; 07-13-2018 at 08:42 PM. Reason: wrong info
Reply With Quote
  #4  
Old 07-13-2018, 08:43 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 330
agermano is on a distinguished road
Default

You should parameterize your query:

Code:
var sql = "select PatientID from ReceivedResult where TestTime = ?";
var results = dbConn.executeCachedQuery(sql, [FullTestTime]);
You were actually correct in that results.next() does return a boolean (indicating whether it advanced to the next row or there were no rows remaining.) You can't compare a boolean to a string, though.
Changing if (Fresult == 'true' ) to if (Fresult == true) or, preferably, if (Fresult) would probably fix your issue.

Since results is a CachedRowSet, you can also do:
Code:
var results = dbConn.executeCachedQuery(sql);
if (results.size() > 0 ) 
{
  return false;
}
Lastly, you should wrap all of your db code in a try block, and your dbConn.close() should be in the finally block to make sure the connection gets closed even if an exception is thrown.
Reply With Quote
  #5  
Old 07-16-2018, 12:13 AM
Mits87 Mits87 is offline
Mirth Newb
 
Join Date: Nov 2016
Posts: 24
Mits87 is on a distinguished road
Default

Thank you all i was able to resolve the issue with Agermano's suggestion. and my code now looks like.
Code:
var sql = "select * from ReceivedResult where TestTime = '" + FullTestTime+"' and patientID='"+FileName+"'";
var results = dbConn.executeCachedQuery(sql);
var Fresult = results.next();
var finalresult=true;
if (Fresult==true) 
{
finalresult=false;
}
else 
{

var InsertADT = "INSERT INTO ReceivedResult (PatientID,TestTime) VALUES ('"+ FileName +"', '"+FullTestTime+"')";
dbConn.executeUpdate(InsertADT);
}
dbConn.close();
return finalresult;
Reply With Quote
Reply

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 05:54 AM.


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