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

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-13-2016, 10: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
  #2  
Old 04-18-2016, 10:39 AM
hugosoares2 hugosoares2 is offline
OBX.2 Kenobi
 
Join Date: May 2011
Posts: 64
hugosoares2 is on a distinguished road
Default

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
  #3  
Old 04-18-2016, 11:50 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 608
kirbykn2 is on a distinguished road
Default

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?
__________________
Best,

Kirby

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
  #4  
Old 04-18-2016, 02:55 PM
hugosoares2 hugosoares2 is offline
OBX.2 Kenobi
 
Join Date: May 2011
Posts: 64
hugosoares2 is on a distinguished road
Default

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
  #5  
Old 04-19-2016, 06:47 AM
jadams4's Avatar
jadams4 jadams4 is offline
OBX.3 Kenobi
 
Join Date: Aug 2015
Posts: 143
jadams4 is on a distinguished road
Default

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

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

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)
http://www.oracle.com/technetwork/da...ex-091264.html

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

mgr.createConnectionPool(pds);
mgr.startConnectionPool("MyPOOL");

//Save it on globall map
globalMap.put("MyPOOL", pds);
On a any javascript context you can now get connections from the pool
Code:
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.
https://docs.oracle.com/database/121....htm#JJUCP8223
Reply With Quote
  #7  
Old 05-11-2016, 10:27 AM
jadams4's Avatar
jadams4 jadams4 is offline
OBX.3 Kenobi
 
Join Date: Aug 2015
Posts: 143
jadams4 is on a distinguished road
Default

I was able to get up to this point:
Code:
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
usually I would, create a connection, execute a query, and log the result like this:
Code:
var sqlCommand = "SELECT exampleData AS example_data FROM testTable";

var dbConn = DatabaseConnectionFactory.createDatabaseConnection($(''),$(''),$(''),$(''));
	var result = dbConn.executeCachedQuery(sqlCommand);
	while(result.next()){
		var logThisData = result.getString('example_data');
        }

logger.info(logThisData);
How can I log something out with the UCP style?

Last edited by jadams4; 05-11-2016 at 12:31 PM.
Reply With Quote
  #8  
Old 05-11-2016, 12:50 PM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 608
kirbykn2 is on a distinguished road
Default

Can you provide an example of how you are able to store the contents of a query into a variable?

For our DB connections that are not using the connection pool we use executeCachedQuery
__________________
Best,

Kirby

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
  #9  
Old 05-12-2016, 05:13 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 608
kirbykn2 is on a distinguished road
Default Got it!

We need to use executeQuery instead of executeCachedQuery

var pds = globalMap.get("CernerPool");
var conn = pds.getConnection(); //java.sql.Connection
var sqlCommand = "SELECT person_id as t_person_id FROM person_alias WHERE person_id = 55555 AND person_alias_id = 666666";

var stmt = conn.createStatement();
var result = stmt.executeQuery(sqlCommand);

while(result.next())
{
logger.info("t_person_id " + result.getString('t_person_id'));
}

conn.close();
__________________
Best,

Kirby

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
  #10  
Old 05-12-2016, 06:24 PM
ahart ahart is offline
OBX.2 Kenobi
 
Join Date: Oct 2014
Posts: 56
ahart is on a distinguished road
Default

Thank you all for the replies. I ended up caching the connection and prepared statements in the globalChannelMap. I think I'd be better off still with a more robust connection pool and will look into this.
Reply With Quote
Reply

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 02:28 AM.


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