web stats
Best practices for managing DB connections and objects - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Thread Tools Display Modes
Old 04-13-2016, 09:06 AM
ahart ahart is offline
OBX.2 Kenobi
Join Date: Oct 2014
Posts: 56
ahart is on a distinguished road
Question Best practices for managing DB connections and objects

So, I have a Mirth application where most all of the logic is in JavaScript.
I have code template functions where I have been consolidating all the common logic, much of which has to do with db operations, inserting rows, updating rows, searching for information.

Since many of these code template functions are called from within loops, I want to minimize the costly db operations, like getting db connections, preparing statements.

So, the way I'm currently doing it is that the calling script (the source transformer, a javascript writer destination, etc.) makes the db connection and prepares the statements, and so my code template functions expect global variables to exist for the db connection, the PreparedStatement objects they might need.

I could make many of the code template functions self contained, i.e., they make their own db connection, prepare a statement, and then execute it, but this would not accomplish that goal.

So, I'd like to figure out the best way to centralize this code. How do most people approach this? DB connections and PreparedStatement objects in the global map? Global channel map? Maybe a function template with loose declarations outside of a function?

What are the best practices? Specifically, for developing db related code template functions...
Reply With Quote
Old 04-18-2016, 09:39 AM
hugosoares2 hugosoares2 is offline
OBX.2 Kenobi
Join Date: May 2011
Posts: 64
hugosoares2 is on a distinguished road

For managing DB connections my approch is to use a connection pool implmentation. In my case we UCP but you can use c3po or hikary (like mirth does). Opennign a db connection every time you need one is not advisable since it is a costly operation.

The approch is to mantain a global map with a reference to the Connection pool manager . This global map is initiated by some global deploy script that simply loads the connection pool if it is not already initiated.

Then I have a simple function (code template) that allows me to get a database connection whenever I need one. sysGetDbConnection(POOL_NAME). This function uses the global map to get a db connection from the pool.

We use ORACLE UNIVERSAL CONNECTION POOL has the connection pool implmentation. It is free and works well with ojdbc drivers.
Reply With Quote
Old 04-18-2016, 10:50 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
Join Date: Sep 2014
Location: Michigan
Posts: 615
kirbykn2 is on a distinguished road

This is great! We are in the process of implementing DB connections in our test environment and are trying to come up with a organized fashion for these implementations.

In our legacy engine, each connection was made independently from each interface. In Mirth we will now look at using ORACLE UNIVERSAL CONNECTION POOL.

We are a Cerner shop, and I am curious if you are also connecting to Cerner using Mirth?


Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.4
Mirth Connect Version 3.8.0
Java Version 1.6.0_45-b06
Java (64 bit) Version 1.6.0_45-b06
Java 7 (64 bit) Version 1.7.0_151-b15
Java 8 (64 bit) Version 1.8.0_181-b13
PostgreSQL Version 9.6.8
Reply With Quote
Old 04-18-2016, 01:55 PM
hugosoares2 hugosoares2 is offline
OBX.2 Kenobi
Join Date: May 2011
Posts: 64
hugosoares2 is on a distinguished road

No, I am not connecting to cerner, but this applies to any interface that demands an high throughput to dB destinations. In our case we have a medium rate of 20 messages per second an peaks of 150mps. UCP allows me to get connections very quickly and to overcome issues related to broken connections and Oracle rac specific events.
Reply With Quote
Old 04-19-2016, 05:47 AM
jadams4's Avatar
jadams4 jadams4 is offline
OBX.3 Kenobi
Join Date: Aug 2015
Posts: 143
jadams4 is on a distinguished road

Any chance you can provide an example channel of your strategy?

Reply With Quote
Old 04-20-2016, 04:28 AM
hugosoares2 hugosoares2 is offline
OBX.2 Kenobi
Join Date: May 2011
Posts: 64
hugosoares2 is on a distinguished road

Unfortunatly I cant share the code but here a re some pointers.

First add Oracle UCP jar to Mirht Resources. (be sure to download the correct jar for your jdbc driver version)

Then on the Global script:
//Load ucp pool
var mgr = Packages.oracle.ucp.admin.UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager();
var pds = Packages.oracle.ucp.jdbc.PoolDataSourceFactory.getPoolDataSource();
//...set any other properties on the pool


//Save it on globall map
globalMap.put("MyPOOL", pds);
On a any javascript context you can now get connections from the pool
var pds = globalMap.get("MyPOOL");
var conn = pds.getConnection(); //java.sql.Connection

//Use the connection and then close it to return it to the pool
To manage you UCP Pool you can use the UCP manager and to get some stats on the pool you can use the pool data source object.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

All times are GMT -8. The time now is 01:41 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Mirth Corporation