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
