Announcement

Collapse
No announcement yet.

Filter results based on SQL result

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

  • Filter results based on SQL result

    I am reading a txt file and converting it to HL7 message. The file name is the patient ID. Once i process that file i want to create database entry with patientID and Testtime and if i receive the same file i want to filter that message.

    The file i receive have data like:

    Code:
    0,20703,6,17,18,18,30,0,0,1,167,0,0,0,0,0,0,0,0,0,0,0,0,0,0
    My code is as below:

    Code:
    var Year = msg['row']['column5'].toString();
    var Month = msg['row']['column3'].toString();
    var Day = msg['row']['column4'].toString();
    var Hour = msg['row']['column6'].toString();
    var Min = msg['row']['column7'].toString();
    var FullTestTime = Year + Month + Day + Hour + Min;
    var FileName = sourceMap.get('originalFilename');
    FileName = FileName.replace(".txt", "");
    
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
    	'net.sourceforge.jtds.jdbc.Driver',
    	'jdbc:jtds:sqlserver://localhost:1433/Mirth',
    	'Mirth',
    	'123'
    );
    // Check if the result was already processed if SQL return value meaning that it was processed  
    var sql = "select PatientID from ReceivedResult where TestTime = '" + FullTestTime+"'";
    var results = dbConn.executeCachedQuery(sql);
    var Fresult = results.next();
    logger.info('SQL Result :' + Fresult);
    if (Fresult == 'true' ) 
    {
    return false;
    }
    else 
    {
    var InsertADT = "INSERT IGNORE INTO ReceivedResult (PatientID,TestTime) VALUES ('"+ FileName +"', '"+FullTestTime+"')";
    dbConn.executeUpdate(InsertADT);
    }
    dbConn.close();
    The issue i am facing is it always filters the result and never let it throw can someone point what is wrong in my code.

  • #2
    If you have some code in filter, maybe the default return is false, so you need to return true somewhere.

    Comment


    • #3
      a) This is not the right boolean check.
      Code:
      if (Fresult == 'true' ) 
      {
      return false;
      }
      b) if you want to iterate over the result set which is results variable in your code, you do
      Code:
      while(results.next()) {
      //Your code here
      }
      EDIT- deleted wrong info
      Last edited by siddharth; 07-13-2018, 08:42 PM. Reason: wrong info
      HL7v2.7 Certified Control Specialist!

      Comment


      • #4
        You should parameterize your query:

        Code:
        var sql = "select PatientID from ReceivedResult where TestTime = ?";
        var results = dbConn.executeCachedQuery(sql, [FullTestTime]);
        You were actually correct in that results.next() does return a boolean (indicating whether it advanced to the next row or there were no rows remaining.) You can't compare a boolean to a string, though.
        Changing if (Fresult == 'true' ) to if (Fresult == true) or, preferably, if (Fresult) would probably fix your issue.

        Since results is a CachedRowSet, you can also do:
        Code:
        var results = dbConn.executeCachedQuery(sql);
        if (results.size() > 0 ) 
        {
          return false;
        }
        Lastly, you should wrap all of your db code in a try block, and your dbConn.close() should be in the finally block to make sure the connection gets closed even if an exception is thrown.

        Comment


        • #5
          Thank you all i was able to resolve the issue with Agermano's suggestion. and my code now looks like.
          Code:
          var sql = "select * from ReceivedResult where TestTime = '" + FullTestTime+"' and patientID='"+FileName+"'";
          var results = dbConn.executeCachedQuery(sql);
          var Fresult = results.next();
          var finalresult=true;
          if (Fresult==true) 
          {
          finalresult=false;
          }
          else 
          {
          
          var InsertADT = "INSERT IGNORE INTO ReceivedResult (PatientID,TestTime) VALUES ('"+ FileName +"', '"+FullTestTime+"')";
          dbConn.executeUpdate(InsertADT);
          }
          dbConn.close();
          return finalresult;

          Comment

          Working...
          X