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.