web stats
How to update database if patientID exists - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2018, 07:00 PM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 56
Mits87 is on a distinguished road
Default 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 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();
Reply With Quote
  #2  
Old 03-08-2018, 12:58 PM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 806
agermano is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 03-09-2018, 01:58 AM
AlexNeiva AlexNeiva is offline
Mirth Guru
 
Join Date: Oct 2013
Location: Portugal
Posts: 275
AlexNeiva is on a distinguished road
Default

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 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
Reply With Quote
  #4  
Old 03-09-2018, 07:44 AM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 56
Mits87 is on a distinguished road
Default

Thanks dear this will help for my future
Reply With Quote
  #5  
Old 03-09-2018, 07:50 AM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 56
Mits87 is on a distinguished road
Default

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 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();
Reply With Quote
  #6  
Old 03-10-2018, 10:21 AM
pacmano pacmano is offline
OBX.2 Kenobi
 
Join Date: Oct 2009
Location: Texas
Posts: 73
pacmano is on a distinguished road
Default

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.
__________________
Mirth 3.7.1 / Postgres 10 / Ubuntu 18.04
Diridium Technologies, Inc.
https://diridium.com

Last edited by pacmano; 03-10-2018 at 12:01 PM.
Reply With Quote
Reply

Tags
adt, channel error, mirth, patient id, sql server

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


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