Announcement

Collapse
No announcement yet.

How to update database if patientID exists

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

  • How to update database if patientID exists

    I am really new to mirth and i have created a channel which received ADT from source and have applied few transformers to it. I want to save that transformer values to database.below is the code i have and what i am looking is if the patient already exist in database update the values and if not create a new value. But below code doesnt work any helps is really appreciated.

    Code:
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
    	'net.sourceforge.jtds.jdbc.Driver',
    	'jdbc:jtds:sqlserver://localhost:1433/mirthdb',
    	'mirthuser',
    	'mirthpassword'
    );
    
    var ReceivedADT = msg['PID']['PID.3']['PID.3.1'].toString();
    
    var sql = "select PatientID  from PatientADT where PatientID = '" + ReceivedADT+"'";
    var results = dbConn.executeCachedQuery(sql);
    
    if (results == ReceivedADT) 
    {
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
    	'net.sourceforge.jtds.jdbc.Driver',
    	'jdbc:jtds:sqlserver://localhost:1433/mirthdb',
    	'mirthuser',
    	'mirthpassword'
    );
    var updateADT = "Update PatientADT Set FirstName = $('FirstName'), MiddleName =$('MiddleName'), LastName = $('LastName'), Gender = $('Gender'), DOB = $('PatientDOB'), AcccountNo= $('AccountNo'), AdmissionDate = $('AdmissionDate'), DischargeDate = $('DischargeDate') where PatientID = +$('PatientID')"; 
    var updateADTresults = dbConn.executeCachedQuery(updateADT);
    }
    else 
    //if (results != ReceivedADT)
    {
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
    	'net.sourceforge.jtds.jdbc.Driver',
    	'jdbc:jtds:sqlserver://localhost:1433/mirthdb',
    	'mirthuser',
    	'mirthpassword'
    );
    	
    var InsertADT = "INSERT IGNORE INTO PatientADT (FirstName, MiddleName, LastName, Gender, DOB, AcccountNo, AdmissionDate, DischargeDate, PatientID) VALUES ($('FirstName'), $('MiddleName'), $('LastName'), $('Gender}'), $('PatientDOB'), $('AccountNo'), $('AdmissionDate'), $('DischargeDate'), $('PatientID')";
    var InsertADTresults = dbConn.executeCachedQuery(InsertADT);
    }
    dbConn.close();

  • #2
    Not a full answer, but a few things real quick... you can reuse the same dbconn in all of your queries. You don't need to keep creating new ones (and each one should be closed.)

    You can't use executeCachedQuery for an insert or update. Use executeUpdate for those instead.

    Comment


    • #3
      Hello Mits87,

      try this code instead:

      Code:
      var dbConn;
      var results;
      
      try
      {
          var pid_temp="";
      
          dbConn = DatabaseConnectionFactory.createDatabaseConnection(
              'net.sourceforge.jtds.jdbc.Driver',
              'jdbc:jtds:sqlserver://localhost:1433/mirthdb',
              'mirthuser',
              'mirthpassword'
          );
      
          var ReceivedADT = msg['PID']['PID.3']['PID.3.1'].toString();
      
          var sql = "select PatientID  from PatientADT where PatientID = '" + ReceivedADT+"'";
          results = dbConn.executeCachedQuery(sql);
      
          while(results.next())
          {
              pid_temp=results.getString("PatientID");
          }
      
      
          if (pid_temp == ReceivedADT) 
          {
              var updateADT = "Update PatientADT Set FirstName = $('FirstName'), MiddleName =$('MiddleName'), LastName = $('LastName'), Gender = $('Gender'), DOB = $('PatientDOB'), AcccountNo= $('AccountNo'), AdmissionDate = $('AdmissionDate'), DischargeDate = $('DischargeDate') where PatientID = +$('PatientID')"; 
              var updateADTresults = dbConn.executeUpdate(updateADT);
          }
          else 
          {        
              var InsertADT = "INSERT IGNORE INTO PatientADT (FirstName, MiddleName, LastName, Gender, DOB, AcccountNo, AdmissionDate, DischargeDate, PatientID) VALUES ($('FirstName'), $('MiddleName'), $('LastName'), $('Gender}'), $('PatientDOB'), $('AccountNo'), $('AdmissionDate'), $('DischargeDate'), $('PatientID')";
              var InsertADTresults = dbConn.executeCachedQuery(InsertADT);
          }
      }
      finally
      {
          if(dbConn)
              dbConn.close();
      }
      i put on bold the steps you miss, and "update" the code in order to be more "clean"
      Best Regards,
      Alex Neiva

      Comment


      • #4
        Thanks dear this will help for my future

        Comment


        • #5
          You are awesome looks like i need lots of improvement @AlexNevia but really appreciate your help

          I was able to get it work by below code buy yours looks better.
          Code:
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
          	'net.sourceforge.jtds.jdbc.Driver',
          	'jdbc:jtds:sqlserver://localhost:1433/mirthdb',
          	'mirthuser',
          	'mirthpassword'
          );
          
          var ReceivedADT = msg['PID']['PID.3']['PID.3.1'].toString();
          var sql = "select PatientID from PatientADT where PatientID = '" + ReceivedADT+"'";
          var results = dbConn.executeCachedQuery(sql);
          
          if (results.next()) 
          {
          var updateADT = "begin Update PatientADT Set FirstName = '"+$('FirstName')+"', MiddleName ='"+$('MiddleName')+"', LastName = '"+$('LastName')+"', Gender = '"+$('Gender')+"', DOB = '"+$('PatientDOB')+"', AcccountNo= '"+$('AccountNo')+"', AdmissionDate = '"+$('AdmissionDate')+"', DischargeDate = '"+$('DischargeDate')+"' , LastUpdateTime = GETDATE() where PatientID = '"+ ReceivedADT+"' select'1' end"; 
          dbConn.executeCachedQuery(updateADT);
          }
          else 
          {
          var InsertADT = "INSERT IGNORE INTO PatientADT (FirstName, MiddleName, LastName, Gender, DOB, AcccountNo, AdmissionDate, DischargeDate, PatientID, CreatedTime) VALUES ('"+$('FirstName')+"', '"+$('MiddleName')+"', '"+$('LastName')+"', '"+$('Gender')+"', '"+$('PatientDOB')
          +"', '"+$('AccountNo')+"', '"+$('AdmissionDate')+"', '"+$('DischargeDate')+"','"+$('PatientID')+"', GETDATE())";
          dbConn.executeUpdate(InsertADT);
          }
          dbConn.close();

          Comment


          • #6
            I'd be careful with generic update statements like in the previous posts.

            I would also write stored procedures for your upsert (update or insert) commands.

            A segment being empty in a subsequent message may not be a reason to delete it in the database.

            For example given the same patient in difference messages, the absence of the value "TEST" in the second message is not necessarily a request to remove what you have in your database via an update.

            Not real messages of course, we are only considering the presence of the word "TEST" in a segment.
            1. PD1|||||||TEST|
            2. PD1|||||||
            3. PD1||||||""|


            In practice the code is something like this (for PostgreSQL):

            Code:
             set chiefcomplaint = NULLIF(COALESCE(NULLIF(p_chiefcomplaint,''),chiefcomplaint),'""'),
            This update command wipes the value in a column if "" is in the segment (i.e.. |""|) which is a request to "clear out what you have". Otherwise it persists the data that is in the table UNLESS there is a new value for that field explicitly set for the given segment.
            Last edited by pacmano; 03-10-2018, 12:01 PM.
            Mirth 3.8.0 / PostgreSQL 11 / Ubuntu 18.04
            Diridium Technologies, Inc.
            https://diridium.com

            Comment

            Working...
            X