web stats
How to query database using parameter from hl7 v2.x message? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 12-03-2019, 07:39 AM
imrrp imrrp is offline
What's HL7?
 
Join Date: Dec 2019
Posts: 3
imrrp is on a distinguished road
Default How to query database using parameter from hl7 v2.x message?

Hey guys,

I am new to mirth (using mirth connect 3.8.1) and struggling to understand certain things. I am experiencing a lot of pressure in my workplace of late to deliver some functionality quickly but I am stuck. Please help.

Problem Statement:
To send QBP^Q21 message and get records of patients from database.

Configuration:
- I have a dummy HIS database setup in PostgreSQL, say a patient database table with columns -- SSN, name, address, insurance_id.
- Mirth Channels:
Channel 1: Source (TCP Listener), Dest (Channel Writer)
Channel 2: Source (Database Reader), Dest (TCP Sender)

What am I doing:
1. I am generating an HL7 v2.x message like this:

Code:
MSH|^~\&|my_sender|my_app|Dummy_HIS|Dummy_Hospital|20191203155517||QBP^Q21^QBP_Q21|123420191203155517|P|2.8.1
QPD|123420191203155517^Patient Query|Q001|100000001|Smith^John||19890419|M
RCP|I|999
and sending the HL7 message to the mirth Channel 1 and then trying to pass it to Channel 2 through Channel Writer.

2. In the Database Reader, I am trying to read the table "patient" for patients with ID present in QPD-3.1 (here 100000001)


How do I do that? I tried globalmap, channelmap. Both did not work.
(I added globalmap in Channel 1 destination, and used globalmap.get() to a variable in Channel 2 source like below)

Code:
var dbConn;

try {
  	var hl7msg = globalMap.get('hl7msg');
        var ssn = hl7msg['QPD']['QPD.3']['QPD.3.1']
	dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://localhost:5432/his','postgres','postgres');

	var result = dbConn.executeCachedQuery("SELECT patient.ssn AS patient_ssn, patient.name AS patient_name, patient.address AS patient_address, patient.insuranceid AS patient_insuranceid FROM patient WHERE patient.ssn LIKE '%"+ssn+"%'");


	// You may access this result below with $('column_name')
	return result;
} finally {
	if (dbConn) { 
		dbConn.close();
	}
}
It would be really helpful if anyone can suggest me where I am wrong.

Last edited by imrrp; 12-03-2019 at 07:43 AM.
Reply With Quote
  #2  
Old 12-03-2019, 08:33 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 246
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

imrrp, honestly, you have several mistakes just in that little bit of code. I think you're trying to jump into the deep end without understanding the fundamentals of Mirth. Please consider working your way up, but no matter what, do NOT store the entire HL7 as a globalMap. That's a HIPAA violation waiting to happen.

You are welcome to keep posting here, but if you want a quick tutorial on making it work, I would be happen to do a free training session then you can post your new code here for the rest of the users. https://calendly.com/jackhaines
__________________

-= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines
Reply With Quote
  #3  
Old 12-03-2019, 02:26 PM
peterl peterl is offline
Mirth Employee
 
Join Date: Mar 2013
Posts: 10
peterl is on a distinguished road
Default

A Database Reader is used to poll a database at scheduled intervals. In your case, you want to read from the database when you receive an HL7 message, so a Database Reader isn't really appropriate. A better approach would be to only have one channel that has a TCP Listener and a TCP Sender. In the Source Transformer, use a Javascript step to read from your database. The Javascript code would be something like:
Code:
var dbConn;

try {
    var ssn = msg['QPD']['QPD.3']['QPD.3.1'].toString();
	dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://localhost:5432/his','postgres','postgres');

	var params = Lists.list(ssn);
	var result = dbConn.executeCachedQuery("SELECT patient.ssn AS patient_ssn, patient.name AS patient_name, patient.address AS patient_address, patient.insuranceid AS patient_insuranceid FROM patient WHERE patient.ssn LIKE ?", params);

	// TODO: do something with the result
	// result.next();
	// channelMap.put('patientAddress', result.getString('patient_address'));
} finally {
	if (dbConn) { 
		dbConn.close();
	}
}
You'll need to do something with the results from the database query, such as storing variables in the channelMap or modifying the message, so that the TCP Sender destination will have the information to send an appropriate message.
Reply With Quote
  #4  
Old 12-04-2019, 11:42 AM
imrrp imrrp is offline
What's HL7?
 
Join Date: Dec 2019
Posts: 3
imrrp is on a distinguished road
Smile

Quote:
Originally Posted by jackwhaines View Post
imrrp, honestly, you have several mistakes just in that little bit of code. I think you're trying to jump into the deep end without understanding the fundamentals of Mirth. Please consider working your way up, but no matter what, do NOT store the entire HL7 as a globalMap. That's a HIPAA violation waiting to happen.

You are welcome to keep posting here, but if you want a quick tutorial on making it work, I would be happen to do a free training session then you can post your new code here for the rest of the users. https://calendly.com/jackhaines
Jack,

Thank you for the session today! I cannot thank you enough for solving my problem and for the amount of knowledge you passed. Glad that I posted the question here.

Wish you a good recovery soon and have a nice day.
Reply With Quote
  #5  
Old 12-04-2019, 11:46 AM
imrrp imrrp is offline
What's HL7?
 
Join Date: Dec 2019
Posts: 3
imrrp is on a distinguished road
Default

Quote:
Originally Posted by peterl View Post
A Database Reader is used to poll a database at scheduled intervals. In your case, you want to read from the database when you receive an HL7 message, so a Database Reader isn't really appropriate. A better approach would be to only have one channel that has a TCP Listener and a TCP Sender. In the Source Transformer, use a Javascript step to read from your database. The Javascript code would be something like:
Code:
var dbConn;

try {
    var ssn = msg['QPD']['QPD.3']['QPD.3.1'].toString();
	dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://localhost:5432/his','postgres','postgres');

	var params = Lists.list(ssn);
	var result = dbConn.executeCachedQuery("SELECT patient.ssn AS patient_ssn, patient.name AS patient_name, patient.address AS patient_address, patient.insuranceid AS patient_insuranceid FROM patient WHERE patient.ssn LIKE ?", params);

	// TODO: do something with the result
	// result.next();
	// channelMap.put('patientAddress', result.getString('patient_address'));
} finally {
	if (dbConn) { 
		dbConn.close();
	}
}
You'll need to do something with the results from the database query, such as storing variables in the channelMap or modifying the message, so that the TCP Sender destination will have the information to send an appropriate message.
Thank you so much @peterl. This really helps and I am able to solve my initial hurdle.
Reply With Quote
Reply

Tags
channel, database reader, hl7 v2, mirthconnect, query parameter

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 12:10 AM.


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