search

rbeckman-nextgen / test-mc3

0 stars 0 forks source link

Add Database Connection Pool Resource Type #2151

Open rbeckman-nextgen opened 4 years ago

rbeckman-nextgen commented 4 years ago

When a transformer needs to access to a database, it creates a connection for each message, which is an efficience lost. The need of write in clear the db password could be a problem in some situations. And it's really ugly.

A datasource management system will help with database access management, and will improve efficience of transformers with this kind of access.

Imported Issue. Original Details: Jira Issue Key: MIRTH-2228 Reporter: albertosaez Created: 2012-09-30T16:18:51.000-0700

rbeckman-nextgen commented 4 years ago

AKA. Mirth needs to be allowing the developer to use a connection pool feature for the connections used in a channel. And also allow the user to get a connection from the pool that mirth is currently using internally. Mirth can be installed in the same schema that channels may use. This 'internal' connection pool should be available to the channel programmer.

Imported Comment. Original Details: Author: stickybandit Created: 2014-01-30T08:48:15.000-0800

rbeckman-nextgen commented 4 years ago

The code template "getPoolDbConn" creates a pool of 20 database connections using a very simple call: getPoolDbConn(DatabaseType, DatabaseName, ServerAddress, UserName, Password); where DatabaseType: is one of "mysql", "oracle", "postgres(ql)" or "sqlserver" (case insensitive) DatabaseName: the name of the database you are connecting to, e.g. "mirthresults", "my_staging_db" The rest is self-explanatory

The function returns an object of type java.sql.Connection

Imported Comment. Original Details: Author: aitougan Created: 2015-11-20T09:05:09.000-0800

rbeckman-nextgen commented 4 years ago

The code template "queryPoolDbConn" runs a database query using "getPoolDbConn()". It takes the same arguments as getPoolDbConn plus two: sql_string and args.

sql_string is any valid SQL select statement that may use ? in place of arguments. args is an array of Java objects to be used with the query, most common ones are java.lang.String and java.lang.Long.

Example: var sql = "select * from subject where subject_key = ?"; var args = [new java.lang.Long(12345)]; var db_res = queryPoolDbConn("postgres", "mirthresults", "localhost", "mirthuser", "secretpassword", sql, args);

If no arguments are to be specified pass a null instead of args. The function returns a CachedResultSet that can be used directly (returned) by Database Reader or it can be iterated over in custom code.

Imported Comment. Original Details: Author: aitougan Created: 2015-11-20T09:14:39.000-0800

rbeckman-nextgen commented 4 years ago

The code template "updatePoolDbConn" executes an update or insert SQL statement using "getPoolDbConn()" and returns the number of affected rows.

Its syntax is identical to "queryPoolDbConn()":

var sql = "update staging_table set processed = 1 where key = ?;"; var args = [new java.lang.Long($('staging_key'))]; var row_count = updatePoolDbConn("mysql", "stagingdb", "localhost", "staginguser", "secretpassword", sql, args);

Imported Comment. Original Details: Author: aitougan Created: 2015-11-20T09:20:41.000-0800