Passing Database Results to Outbound Messages in Mirth Connect

Recently I had an interoperability project that required reading from a database at regular intervals, and then feeding the information out through an ORU message. Because this was the first time in a while where I had to read FROM the database, it took me a minute before I was reminded that parsing the resultant data doesn't work quite the same as your typical 2.X/3.X/XML message.

 On the plus side, Mirth Connect makes it very easy to handle database results. For a very simple example, start by creating a channel, set the source type to "Database Reader", and set the response to "Destination 1". In the SQL textarea put your query, and set up the rest of the source connector according to your system requirements.
Here is a screenshot of my example setup.

Once you have your source connector set-up, you can either choose to handle the message using source transformers, or pass the message to a destination transformer.
If you pass the results to a destination it is important to note that you will need to create a reference to the message that will be passed to the destination.
A simple way of doing that is to add a javascript step into the source transformer and include the following code:

tmp = msg;

This tells Mirth to send the results onward as a feeder message for the destination transformer.

Now comes the best part about handling database results.

You can now call all of your results in the destination using the column name as the reference!

For example, if I wanted to get the value from the "msg_id" column, I can write something like this:
var msg_id = msg["msg_id"].toString();

The basic syntax for getting the values is:
localVariableName = msg["columnName"].toString();

(This syntax will work in both source and destination connectors as long as somewhere in the source connector you add the tmp = msg; command.)

Now I can use that variable to insert a value into the outbound message, affect the logic for further processing, or output to a file.

Simplify Database Connection Updates

To simplify changing database connection strings I've found it's quite useful to create a function in the Code Templates. This way I can just use that for each time I need to put a database call and whenever I need to change the connection string I have one single spot to modify instead of dozens.

function ODBConn(state) {
  if(state=="open"){
    importPackage(java.sql);
    var dbConn = DriverManager.getConnection('URL','Username','Password');
  }
return dbConn;
}

This example is specific to using prepared statements for database interaction, but the principal is the same for any type or method of connection. 

To use the function for connecting, simply use something like this:

var conn = ODBConn('open');
...
var preparedQuery = conn.prepareStatement("some prepared query string");
  preparedQuery.setString(1,firstParameter);
  preparedQuery.setString(2,secondParameter);
  preparedQuery.executeQuery();

One thing to note about this script is that it does not close the connection when finished as shown. Fortunately closing the connection is very simple, it should just be done at once the need for a connection is complete.

Just append this to the end of your script to close :

conn.close();

Voila! You have a reusable database connection function and whenever you need to change a database password or connection url! It will now be in one convenient place instead of having to search through every channel destination and transformer script to update your information.

Parameterized SQL Queries in Mirth

This doesn't seem to be an often discussed topic, but in the interest of preventing SQL injection it should be an item of importance with developers. (At least in the back of your mind)

Using the provided code snippet from Mirth's reference list:


var dbConn;
var result;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('driver', 'address', 'username', 'password');
result = dbConn.executeCachedQuery('expression', paramList);
} finally {
if (dbConn) {
dbConn.close();
}
}

or

var dbConn;
var result;

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('driver', 'address', 'username', 'password');
result = dbConn.executeUpdate('expression', paramList);
} finally {
if (dbConn) {
dbConn.close();
}
}

is somewhat tedious and error prone.
If you want to use much more straightforward and less error prone, a simple way to do that is to use something like:


importPackage(java.sql);
var dbConn = DriverManager.getConnection('dbUrlString','username','password);

var query = dbConn.prepareStatement("Select id from sampleDB where Firstname=? and Lastname=?");

query.setString(1,"John");
query.setString(2,"Doe");

result = query.executeQuery();

If your queries are consistent you may even consider creating a library in the Global Deploy Scripts with a variety of pre-built query strings that can be easily reused.