Send Variables to Database Reader

[JJWest12]

I have a channel that you manually input 3 values and it sends as delimited text down to a 2nd database reader channel like such:

input1,input2,input3

The issue I am having is that I want to user input3 in my database reader query on the 2nd channel in a way where

SELECT * FROM tableName WHERE x = input3

I am struggling with how to make this work with sending downstream to a database reader channel and having the query run based on user input

[pacmano1]

If you post here and the mirth forums, please cross reference them with links.

[rjkroll]

What you could do, instead of using an actual Database Reader Source in your 2nd channel, that you are passing the delimited string to, just make the Source a Channel Reaeder.

Then, in that 2nd channel, do this:

• In your Source transformer, parse the delimited text string and assign the 3 segments to variables
• In your Source transformer, use Javascript to call a JDBC database connection, as well as your Select statement, where you would use of one of the variables in your WHERE clause.
• Then, you get your ResultSet and set them to variables, where you can then do whatever you want with them, pass them to Destination, etc.

[JJWest12]

I got everything you said except the 3rd bullet. So here is what I have (I got rid of all of the real information and replaced it with just letters and such for the purpose of not putting the information public online). How exactly do I get the "ResultSet"? The query will end up getting about 20 results, how would I pass each one individually to the destination?

var dbConn;
var result;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('a', 'b', 'c', 'd');

	var sql = "SELECT xyz from dbo.tableName where XYZ = '" + variableName + "'";
	result = dbConn.executeCachedQuery(sql);

}
finally {
if (dbConn) {
dbConn.close();
}
}

[rjkroll]

I didnt actually test the following, but something like this should work. Once you get your "xyz" value from the database and assign it to a variable, it should show up as usable in the Destination via drag-and-drop. If not, you can just assign it to a ChannleMap variable for use wherever in the channel.

var dbConn;
var result;

try 
{
dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://servername:port/dbname','user','password');

    var query = "SELECT xyz from dbo.tableName where XYZ = '" + variableName + "'";
    result = dbConn.executeCachedQuery(query);

    while(result.next())
    {
        var xyz_variable = result.getString("xyz");

    /*
     * channelMap.put('xyz',xyz_variable);
    */
    }


} 
finally 
{
    if (dbConn) 
    {
        dbConn.close();
    }
}

[JJWest12]

I guess I am just confused on the fact that what is coming in to this second channel is delimited text from the first channel. The type of variable 'result' is just an object. When tying to do what you suggest I get an "Invalid column name" error because there isn't an actual column in the result object named "XYZ". "result" looks like it is formatted as XML however I cannot find a path using click and drag on something of type "object". I think I need to find a new to convert it to XML first