Announcement

Collapse
No announcement yet.

Best practices for managing DB connections and objects

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


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

          Thanks!

          Comment


          • #6
            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

            Comment


            • #7
              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, 11:31 AM.

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Can the given connection pool be used for SQL Server?

                      Originally posted by hugosoares2 View Post
                      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

                      Can I use Oracle UCP for SQL Server as well?

                      Comment


                      • #12
                        Originally posted by hugosoares2 View Post
                        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
                        only support for oracle database? how to implement the connection pool in other database, ex, MS SQL Server?

                        Comment


                        • #13
                          What's your ending up solution for this situation?

                          Comment


                          • #14
                            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..

                            Comment

                            Working...
                            X