web stats
Problems with SQL Query - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 06-09-2011, 06:52 AM
kmoss@ochonline.com kmoss@ochonline.com is offline
Mirth Newb
 
Join Date: May 2011
Posts: 10
kmoss@ochonline.com is on a distinguished road
Default Problems with SQL Query

I am trying to setup a database connection to add information from an ADT feed to a POSTGRES database. I am using the patient account number as a key field in the database. This is the database setup:
Code:
CREATE TABLE adtfeed
(
  patientid character varying(10) NOT NULL,
  lastname character varying(64) NOT NULL,
  firstname character varying(64) NOT NULL,
  middlename character varying(64),
  gender character varying(1) NOT NULL,
  dateofbirth timestamp without time zone NOT NULL,
  ssn character varying(11) NOT NULL,
  patienttype character varying(1) NOT NULL,
  patientsubtype character varying(2) NOT NULL,
  patientalphatype character varying(11) NOT NULL,
  dateadded timestamp without time zone NOT NULL DEFAULT now(),
  processed boolean NOT NULL DEFAULT false,
  dateofservice timestamp without time zone NOT NULL,
  mrnumber character varying(11) NOT NULL,
  CONSTRAINT adtfeed_pkey PRIMARY KEY (patientid)
)
Because the ADT feed also contains additional messages when changes, transfers, discharges are made. In my code to do a SELECT using executeUpdate(), what I am expecting back is a number of lines it returns with 0 being no results and > 0 being already in the database. From there if it was zero I would add the record otherwise just update it with the new information.

The result I am getting is always a -1. Please find part of my code attached:
Code:
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://localhost:5432/Patient_Data','mirthtraining','abc12345');

var exist = dbConn.executeUpdate("SELECT patientid FROM adtfeed WHERE patientid = '" + $('account_Number') + "'");
// var exist = dbConn.executeUpdate("SELECT patientid FROM adtfeed WHERE patientid = '445003'");
// var exist = dbConn.executeCachedQuery("SELECT patientid FROM adtfeed WHERE patientid = '445003'");


logger.error("Result " + exist + " Number " + $('account_Number'));
As you can see by the commented lines I have tried a couple of other options. When I executeUpdate the result is always a -1 when I use execute query I get: com.sun.rowset.CachedRowSetImpl@88803c in the exist vaiable. What am I missing here, I know it's probably something dumb.
Reply With Quote
  #2  
Old 06-09-2011, 08:18 AM
lgarciac lgarciac is offline
Mirth Newb
 
Join Date: Sep 2009
Location: Madrid, Spain
Posts: 13
lgarciac is on a distinguished road
Default

I am not a SQL expert, but I believe that you can use something like this:
Quote:
var dbConn = java.sql.DriverManager.getConnection("DB_Address", "Username", "DB_Password");
var sql = "SELECT patientid FROM adtfeed WHERE patientid = ?";
var stmt = dbConn.prepareStatement(sql);
stmt.setString(1, $('account_Number'));
var resultSet = stmt.executeQuery();

if (resultSet.next() != null) {
// You have results }
else {
// You do not have results
}
Reply With Quote
  #3  
Old 06-09-2011, 02:06 PM
edwelch4 edwelch4 is offline
OBX.1 Kenobi
 
Join Date: Oct 2007
Location: Columbia, SC
Posts: 27
edwelch4 is an unknown quantity at this point
Default

Try this:

var pi = (channel mapping for the number you want to use)

var newsql = "SELECT Count(*) FROM tbl_Patient WHERE PatCorpNumber = '" + pi + "'";

var newresult = dbConn.executeCachedQuery(newsql);

// Fetch the result row.
newresult.next();

//If no match, Insert new row into Patients and visits
if (newresult.getInt(1) == 0)
{
//Create the SQL statement to insert a new row into Patient table.
var sql = "INSERT INTO tbl_Patient (PatCorpNumber, PatMedRecNumber, PatFirstName, PatLastName, PatMidName, DOB, Gender, Race, SSN, InsertDate)" +
"VALUES ( '" + pi + "' , '" + mrn + "' , '" + $('PID_5_2_FirstName') + "' , '" + $('PID_5_1_LastName') + "' , '" + $('PID_5_3_MiddleName') + "' , '" + $('PID_7_DOB') + "' , '" + $('PID_8_Gender') + "' , '" + $('PID_10_Race') + "' , '" + $('PID_19_SSN') + "', '" + curdate + "' )";

dbConn.executeUpdate(sql);
//Create the SQL statement to insert a new row into Visit Table.
var sql2 = "INSERT INTO tbl_Visit (AdmitDateTime, DischargeDateTime, PatientType, PatientClass, HospitalService, PatMedRecNumber, AccountNumber, PatCorpNumber, FacilityId, Location, Room, Bed, Publicity, PublicityCode, InsertDate)" +
"VALUES ( '" + $('PV1_44_AdmitDate') + "' , '" + $('PV1_45_DischargeDate') + "' , '" + $('PV1_18_PatientType') + "' , '" + $('PV1_2_PatientClass') + "' , '" + $('PV1_10_HospitalService') + "' , '" + mrn + "' , '" + $('PID_18_AccountNumber') + "' , '" + pi + "' , '" + $('PV1_39_servicingFacility') + "' , '" + $('PV1_3_Location') + "' , '" + $('PV1_3_Room') + "' , '" + $('PV1_3_Bed') + "' , '" + $('PV2_21_1_Publicity') + "' , '" + $('PV2_21_1_PublicityCode') + "' , '" + curdate + "' )";

dbConn.executeUpdate(sql2);

}

else

Do updates in this part..

Hope this helps...
Ed
Reply With Quote
  #4  
Old 06-17-2011, 11:43 AM
kmoss@ochonline.com kmoss@ochonline.com is offline
Mirth Newb
 
Join Date: May 2011
Posts: 10
kmoss@ochonline.com is on a distinguished road
Default What I did

What I finnaly had to do to get this was to use the following query:
Code:
var resultSet = dbConn.executeCachedQuery("SELECT patientid FROM adtfeed WHERE patientid = '" + $('account_Number') + "'");
In order to determine if the record existed I had to make the folowing declaration:
Code:
var exists = resultSet.next()
At that point I could test to see if exists == true then run an SQL UPDATE statement, otherwise I use a CREATE.
Reply With Quote
Reply

Tags
javascript, jdbc connetor, postgres, sql

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:58 AM.


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