Announcement

Collapse
No announcement yet.

How to query database using parameter from hl7 v2.x message?

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

  • 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, 06:43 AM.

  • #2
    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
    -= [email protected]
    -= 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

    Comment


    • #3
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment

          Working...
          X