executeCachedQuery - question

[malkon10t]

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
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

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!!!

[ricber]

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

[malkon10t]

Re:executeCachedQuery - question

Thanks for your help!!!

I used a similar query within my SP and looped through the result set in the filter.

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