Announcement

Collapse
No announcement yet.

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