How to load static data in Mirth, avoid many roundtrips to a database

Question

Mirth is a broker to help with healthcare application HL7 message integration.

My question is about saving yourself the trouble of hitting your own datastore everytime you want to do a lookup on some data contained within the HL7.

Scenario: for each message received by the channel, I want to find the facility's mnemonic/code/ID and get the full name of the facility. Unfortunately I cannot ask the sender of the HL7 message to send it along in the message for me. So I have to write my own DB access code to call a stored procedure, pass in the ID, and receive the full name.

Any idea on how to create a cache of data in Mirth so that you can access the lookup from any channel, source, destination, transformer or filter?

Francis De Wet Francis De Wet · Accepted Answer · 2011-01-11T22:12:34

In our case, where we had to load lookup values from a database, we solved this as follows:

Have a channel deploy script that loads lookup values when the channel is deployed into the global map.
Have a global template function that allows you to lookup values from this global map.
Use the global template function in filters and translates.

Note: In our case, each key could have two values associated with it. If we did not use both we just set the second to null in the query from the database.

To load the global map place this in your deploy script of your channel (customize as needed):

// This script executes once when the mule engine is started
// You only have access to the globalMap here to persist data 

var sqlDBDriver   = 'net.sourceforge.jtds.jdbc.Driver';
var sqlDBName     = 'jdbc:jtds:sqlserver://databaseserver/databasename';
var sqlDBDUser    = 'username';
var sqlDBPassword = 'password';

function PopulateLookup ( sqlQuery, globalMapName )
{
    logger.info('Loading lookup table values in the deploy script: ' + globalMapName);

    var dbConn = DatabaseConnectionFactory.createDatabaseConnection( sqlDBDriver, sqlDBName, sqlDBDUser, sqlDBPassword );
    var rs = dbConn.executeCachedQuery( sqlQuery );
    dbConn.close();

    var arr = new Array();
    while(rs.next())
    {
        var obj = new Object();
        obj.LeftValue   = rs.getString('LeftValue');
        obj.RightValue1 = rs.getString('RightValue1');
        obj.RightValue2   = rs.getString('RightValue2');
        arr.push(obj);
    } 
    globalMap.put( globalMapName, arr );
}

PopulateLookup( 'SELECT keyColumn as LeftValue, Value1 as RightValue1, Value2 as RightValue2 FROM tableName', 'GlobalMapName' );

// Repeat above line as needed for each lookup you need

return;

In order to access these values you use the following function as a global template.

function FindLookupValueWithDefault ( LookupGlobalMapName, LeftValue, DefaultValue1, DefaultValue2 ){
    /***********************************************************************************************
    DESCRIPTION: Retrieves lookup table values from the global map and uses the input values to return output values

     PARAMETERS:
       LookupGlobalMapName - name of the lookup table in the Global Map
       LeftValue - The value to look up
       DefaultValue1 - the first default value if a match was not found
       DefaultValue2 - the second default value if a match was not found

    RETURNS:
       An object containing the replacement value and associated OID if applicable

    REMARKS: 
    ************************************************************************************************/
        // Retrieve the previously matched item from the globalmap
        //    We do this to save time and not look through a huge lookup table tons of times
        //    unless we absolutely have to
        var prevItem = globalMap.get(LookupGlobalMapName + '-Previous');

        // This is the same item requested for this globalmap name - just return the previous value
        if ( prevItem != null && prevItem.LeftValue == LeftValue) {
            return prevItem;
        }

        //
        // If we reach this point the previous item either did not exist or did not match 
        //

        // Retrieve the array with lookup objects from the globalmap and search for the matching value
        var arr = globalMap.get(LookupGlobalMapName);
        var obj = new Object();
        obj.LeftValue = LeftValue;
        obj.RightValue1 = DefaultValue1;
        obj.RightValue2   = DefaultValue2; 
        for each ( item in arr )
        {
            var pattern=new RegExp("^" + item.LeftValue + "$");
            var result = pattern.test(LeftValue );

            if ( pattern.test(LeftValue ) )
            {
                obj = item;
                break;
            } 
        }

        // Store the previous value in the globalmap
        globalMap.put( LookupGlobalMapName + '-Previous', obj );

        // Return the object we found or created
        return obj;
    }

A sample of code to access the values:

var myObject = FindLookupValueWithDefault('GlobalMapName', 'LookupValue', 'DefaultValue1', 'DefaultValue2');

if ( myObject != null )
{

      var value1 = myObject.RightValue1;
      var value2 = myObject.RightValue2;
}

Your mileage may vary ... but this has worked for us up to now.

Thanks, Frans de Wet

How to load static data in Mirth, avoid many roundtrips to a database

2 Answers