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

executeCachedQuery - question

  • Filter
  • Time
  • Show
Clear All
new posts

  • executeCachedQuery - question

    Hi Everyone!!

    I am querying a SQL table to return an integer value if the query returns any results.
    This is the SQL:
    ALTER PROCEDURE [dbo].[usp_searchkeyword2]
    @variable varchar(50),
    @keywdID int
    SET @keywdID = (Select keywordID from keywords where keyword LIKE @variable)
     Return @keywdID
    This is the filter I am using in Mirth to discard any messages not containing any keywords returned by my query

    var searchTerm = $('PV_AdmitReason');
    //var query = "Declare @return_value int exec @return_value = usp_searchkeyword2 @variable = '" +searchTerm+ "', @keywdID = NULL Select @return_value";
    var query = "exec usp_searchkeyword2 @variable = '" +search+ "', @keywdID = NULL";
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:«»sqlserver://sqlserver:1433/database',
     'user', 'password');
    var result = 0;
    result = dbConn.executeCachedQuery(query);
    if (result >= 1){
    return true;
    else {return false;}
    The error I get is :
    DETAILS: Wrapped java.sql.SQLException: The executeQuery method must return a result set.

    Question is: What is it that I am doing wrong and where can I find the definition of the the method executeCachedQuery. Also if there are any more methods which deal with SQL queries in Mirth but are not present in the references, any info on them would be appreciated!!!


  • #2
    Re:executeCachedQuery - question


    I think that the reason is because executecachedquery needs to return some resultset, if no data is returned system claims for it.

    At this point I think that you can use a strategy like:

    Select count(coalesce(keywordID,0)) from keywords where keyword LIKE @variable

    With this sentence the SELECT statement ever returns a resultset with one number, sometimes a keyword id or, in the worst scenary, a 0 triggered by a null result.


    Ricard Bernat


    • #3
      Re:executeCachedQuery - question

      Thanks for your help!!!

      I used a similar query within my SP and looped through the result set in the filter.
      var returnValue = result.getInt(1);
      This actually did it and I am able to filter all the messages based on keywords obtained from a DB.

      Post edited by: malkon10t, at: 11/26/2008 09:13