Best practices for managing DB connections and objects

[ahart]

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...

[hugosoares2]

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.

[kirbykn2]

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?

[hugosoares2]

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.

[jadams4]

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

Thanks!

[hugosoares2]

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:

//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

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

[jadams4]

I was able to get up to this point:

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:

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?

[kirbykn2]

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

[kirbykn2]

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();

[ahart]

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.

[dpoply]

Can the given connection pool be used for SQL Server?

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:

//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

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

Can I use Oracle UCP for SQL Server as well?

[williamxie]

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:

//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

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

only support for oracle database? how to implement the connection pool in other database, ex, MS SQL Server?

[williamxie]

What's your ending up solution for this situation?

[StickyBandit]

Why do we need UCP.jar in mirth connect if the pool is already setup in
oracle and all we need is a connection from the pool?
The URL has
:POOLED
on it. etc..