Announcement

Collapse

Mirth Connect 4.1.0 Released!

Mirth Connect 4.1.0 is now available as an appliance update and on our GitHub page. Mirth Connect 4.1.0 includes new features such as new event log messages, additional fields to the Welcome to Mirth Connect screen, new information included in alerts as well as many smaller changes, updates, and improvements. This release also contains several improvements to commercial extensions. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

The executeQuery method must return a result set.

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

  • The executeQuery method must return a result set.

    I took the "Workaround-To-Call-StoredProc-From-Mirth" to create my own. Everything is a carbon copy except the db connection, stored procedure name, and some variable names.

    When a transaction comes in, the data is successfully added/updated to the database, however Mirth is throwing an error that the executeQuery method must return a result set.

    Is there something that I need to return at the end of the script?

    Also, where is the logger.info data stored?


    Here is the last part of the code that I am running.

    Code:
    var expression = "EXEC process_adt " + spParams;   
    
    // Print the SQL statement for debugging purpose
    logger.info('SQL: ' + expression);
    
    //var result = dbConn.executeCachedQuery(expression); 
    var result = dbConn.executeQuery(expression); 
    
    
    //go to the first result 
       
    result.next(); 
    
    //get the value from the first column as an integer 
    var iReturnVal = result.getInt(1);  
       
    
    //cleanup 
    result.close(); 
    dbConn.close();
       
    if (iReturnVal > 0) {
      // Stored proc returns +ive no. for success 
          
    logger.info('Processed add or update patient successfully.'); 
       }
    else {
          logger.info('Error: process_adt failed.');   
       }

    Here is the error:
    Code:
    ERROR-300: Transformer error
    ERROR MESSAGE:	Error evaluating transformer
    com.webreach.mirth.server.MirthJavascriptTransformerException: 
    CHANNEL:	GRV3_PATIENT_DB
    CONNECTOR:	Destination 1
    SCRIPT SOURCE:	Transformer
    LINE NUMBER:	188
    DETAILS:	Wrapped java.sql.SQLException: The executeQuery method must return a result set.
    	at com.webreach.mirth.server.mule.transformers.JavaScriptTransformer.evaluateTransformerScript(JavaScriptTransformer.java:414)
    	at com.webreach.mirth.server.mule.transformers.JavaScriptTransformer.transform(JavaScriptTransformer.java:286)
    	at org.mule.transformers.AbstractEventAwareTransformer.doTransform(AbstractEventAwareTransformer.java:48)
    	at org.mule.transformers.AbstractTransformer.transform(AbstractTransformer.java:197)
    	at org.mule.impl.MuleEvent.getTransformedMessage(MuleEvent.java:251)
    	at com.webreach.mirth.server.controllers.MessageObjectController.getMessageObjectFromEvent(MessageObjectController.java:482)
    	at com.webreach.mirth.connectors.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:76)
    	at org.mule.providers.AbstractMessageDispatcher$Worker.run(AbstractMessageDispatcher.java:257)
    	at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
    	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
    	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
    	at java.lang.Thread.run(Unknown Source)
    Thanks in advance!
    Reid Hospital and Healthcare

  • #2
    Re:The executeQuery method must return a result se

    I'll post my stored procedure out here since I haven't received any replies. I am returning "1" when the data is updated/inserted into the database.

    Code:
    ALTER PROC [dbo].[process_adt]
      @mrn AS varchar(10),
      @pt_no as varchar(13),
      @lname as varchar(50),
      @fname as varchar(30),
      @mname as varchar(30),
      @dob as varchar(8),
      @sex as varchar(1),
      @ssn as varchar(12),
      @att_phy_id as varchar(8),
      @att_phy_name as varchar(75),
      @nurs_sta as varchar(6),
      @room_no as varchar(8),
      @discharge_date as varchar(8),
      @address as varchar(150),
      @city as varchar(100),
      @state as varchar(50),
      @zip as varchar(50),
      @phone as varchar(50)
      
      
    AS
    IF @pt_no IN(select distinct pt_no from ADT_Patients)
    BEGIN
      UPDATE ADT_Patients
      SET mrn = @mrn, 
          pt_no = @pt_no, 
          lname = @lname, 
          fname = @fname, 
          mname = @mname, 
          dob = convert(datetime, isnull(left(@dob,2) + '/' + substring(@dob,3,2) + '/' + right(@dob,4), @dob)), 
          sex = @sex, 
          ssn = @ssn, 
          att_phy_id = @att_phy_id, 
          att_phy_name = @att_phy_name, 
          nurs_sta = @nurs_sta,
          room_no = @room_no,
          discharge_date = convert(datetime, isnull(left(@discharge_date,2) + '/' + substring(@discharge_date,3,2) + '/' + right(@discharge_date,4), @discharge_date)) ,
          address = @address,
          city = @city,
          state = @state,
          zip = @zip,
          phone = @phone      
          
      WHERE pt_no = @pt_no
    return '1'
    END
    ELSE
    BEGIN
      INSERT IGNORE INTO ADT_Patients
      VALUES(@mrn, @pt_no, @lname, @fname, @mname, convert(datetime, isnull(left(@dob,2) + '/' + substring(@dob,3,2) + '/' + right(@dob,4), @dob)), @sex, @ssn, @att_phy_id, @att_phy_name, @nurs_sta, @room_no, convert(datetime, isnull(left(@discharge_date,2) + '/' + substring(@discharge_date,3,2) + '/' + right(@discharge_date,4), @discharge_date)), @address, @city, @state, @zip, @phone)
    return '1'
    END
    Reid Hospital and Healthcare

    Comment


    • #3
      Re:The executeQuery method must return a result set.

      In your stored proc, replace
      return '1'
      with
      SELECT 1
      Hope this helps!

      Comment


      • #4
        Re:The executeQuery method must return a result se

        Thanks for the help!!! I received a new error. I ended up commenting out any code after the database connection was closed. It work fine now without throwing any errors.


        Code:
        var expression = "EXEC test_process_adt " + spParams;   
        
        // Print the SQL statement for debugging purpose
        logger.info('SQL: ' + expression);
        
        //var result = dbConn.executeCachedQuery(expression); 
        var result = dbConn.executeQuery(expression); 
        
        //go to the first result 
        //result.next(); 
        
        logger.info(result); 
        
        //get the value from the first column as an integer 
        //var iReturnVal = result.getInt(1);  
         
        //cleanup 
        result.close(); 
        dbConn.close();
           
        //if (iReturnVal > 0) {
          // Stored proc returns +ive no. for success 
        //logger.info('Processed add or update patient successfully.'); 
        //return true;
        //   }
        //else {
        //      logger.info('Error: spHL7AddOrUpdatePatient failed.');   
        //return false;
        //   }
        Reid Hospital and Healthcare

        Comment

        Working...
        X