Announcement

Collapse
No announcement yet.

Problems with SQL Query

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

  • 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: [email protected] in the exist vaiable. What am I missing here, I know it's probably something dumb.

  • #2
    I am not a SQL expert, but I believe that you can use something like this:
    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
    }

    Comment


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

      Comment


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

        Comment

        Working...
        X