Announcement

Collapse
No announcement yet.

executeCachedQuery - question

Collapse
X
 
  • 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:
    Code:
    ALTER PROCEDURE [dbo].[usp_searchkeyword2]
    @variable varchar(50),
    @keywdID int
    AS
    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

    Code:
    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);
    dbConn.close();
    
    
    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!!!


    Thanks!!!

  • #2
    Re:executeCachedQuery - question

    Hi,

    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.

    HTH,

    Ricard Bernat

    Comment


    • #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.
      Code:
      result.next(); 
      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

      Comment

      Working...
      X