Calling stored procedures from Mirth


Mirth JDBC reader/writer are well suited for simplest cases: only one SELECT and (optionally) a UPDATE for each row, but normally you need to use JDBC reader/writer with JS enabled.

Mirth JDBC API when using JS is very basic and does not allow easily deal with stored procedures: there are only executeCachedQuery() and executeUpdate(). If you’re lucky you can use executeUpdate() as long as your SP doesn’t use IN/OUT parameters or returns any resultset. Otherwise you should call the SPs in a java library and then invoke it from Mirth JS. Some time ago I documented how to call custom java classes from Mirth and this may be used to extend its database API.

In worst cases I need 3 classes:

  • DAO
  • TO (in/out)
  • TO (resultset)

Of course some classes may be not useful because this is the worst scenario with a SP full of bizarre SP-features. DAO manages the connection and uses a TO with the IN, OUT parameters, then returns a TO with the resultset(s).

Once packed into a jar and droped into lib/custom (and restart!), you can use it from a Mirth JS destination/transformer:

...
var myDAO = new Packages.com.adsf.myDAO(user,pwd,url,driver);
var parTO = new Packages.com.asdf.parTO('Y',10);
var resultTO = myDAO.exec("{call the_sp()}");
myDAO.close();
logger.error( resultTO.getDate() + "-" + resultTO().getName() );
logger.error( parTO.getMode() ); // IN-OUT parameter
...

This way we can code the SP-call methods in java with a finer control about inputs and outputs arround the procedure. Mirth connector and Java code can share the info using POJOs (the TOs). There are other situations where invoking java code is advantageous:

  • very specific scenarios or where Mirth API is non existent (LDAP?)
  • fine Exception handling inside Java code
  • use of java.lang.String is a lot powerful than javascript string

HTH

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: