Adding data from tables to mapping destinations and insert them in database

[tGitm]

Hey, again me 👋
In 1 destination transformer I am reading data from HL7 and adding it to channelMap and then inserting to database. Another transformer I would like to read the whole HL7 message and insert it in db field (how to do that?).

But the last one is reading from another SQlite database (code below):
`var sqlDBName = 'jdbc:sqlite:D:/documents/MESI/mesi-integration/mesi-integration.db';
var sqlDBDriver = 'org.sqlite.JDBC';
var sqlDBUser = '';
var sqlDBPass = '';

try {
var dbConn = DatabaseConnectionFactory.createDatabaseConnection(sqlDBDriver, sqlDBName, sqlDBUser, sqlDBPass);

/*var QueryDB = 
	'SELECT '  +
	'integrationserviceid,'+ 
	'source_id,' + 
	'serial_number,' + 
	'tablet_group_id,' +
	'application_id' +
	'FROM '+
	'measurements' + ';'; */

var QueryDB = 'SELECT '  +
	'IntegrationServiceId,'+ 
	'SourceId,' + 
	'SerialNumber,' + 
	'TabletGroupId,' +
	'ApplicationId' +
	'FROM '+
	'Measurements' + ';';	
	
var result = dbConn.executeCachedQuery(QueryDB);
logger.info("DB Query: " + result);
var integration_id;
var source_id;
var serial_number;
var tablet_group_id;
var application_id;

while (result.next()) {
	integration_id = result.getString('IntegrationServiceId');
	source_id = result.getString('SourceId');
	serial_number = result.getString('SerialNumber');
	tablet_group_id = result.getString('TabletGroupId');
	application_id = result.getString('ApplicationId');
	channelMap.put('integration_id', integration_id);
	channelMap.put('id', source_id);
	channelMap.put('serial_number', serial_number);
	channelMap.put('tablet_group_id', tablet_group_id);
	channelMap.put('application_id', application_id);
}

dbConn.close();

} catch(e) {
logger.info("DB Exception: " + e.message);
}`

In destination I'm inserting to local SQLite tabel (code below):

`var dbConn;
try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.sqlite.JDBC','jdbc:sqlite:D:/documents/MESI/mesi-integration/mesi-integration.db','','');

var result = dbConn.executeUpdate("INSERT INTO Tasks (Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, Status, PatientId, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, ApplicationId, DoctorFirstName, DoctorLastName, AdditionalFields, TabletAcceptedBy, RequestContent, RequestDate, DeletedAt, AddressCity) VALUES (" 
		+ $('id') + "','"
		+ $('visit_num') + "','"
		+ '80a1c95c-6480-47e7-ab44-c35cf8328b3e' + "','"
		+ $('integration_id') + "','"
		+ $('patient_id') + "','"
		+ $('given_name') + "','"
		+ $('family_name') + "','"
		+ $('dateBirth') + "','"
		+ $('gender') + "','"
		+ $('race_code') + "','"
		+ $('email') + "','"
		+ $('tel_number') + "','"
		+ $('street_address') + "','"
		+ 2 + "','" 
		+ $('postal_code') + "','"
		+ $('country') + "','"
		+ $('attending_doc_given_name') + "','"
		+ $('attending_doc_family_name') + "','"
		+ $('additionalObject') + "','"
		+ $('OrderMessage') + "','"
		+ $('request_date') + "','"
		+ 2 + "','"
		+ $('serial_number') + "','"
		+ $('tablet_group_id') + "','"
		+ 0 + "','" 
		+ $('city')
);

} finally {
if (dbConn) {
dbConn.close();
}
}`

But this code is keep on returning "field id unknown", or pushing just raw variable to database instead of it's data. How should I change my channel.
This i my error now:
Database Writer error ERROR MESSAGE: Error evaluating Database Writer com.mirth.connect.server.MirthJavascriptTransformerException: CHANNEL: HL7 - SQLite CONNECTOR: Destination 1 SOURCE CODE: 99: var dbConn; 100: try { 101: dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.sqlite.JDBC','jdbc:sqlite:D:/documents/MESI/mesi-integration/mesi-integration.db','',''); 102: 103: 104: var result = dbConn.executeUpdate("INSERT INTO Tasks (Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, Status, PatientId, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, ApplicationId, DoctorFirstName, DoctorLastName, AdditionalFields, TabletAcceptedBy, RequestContent, RequestDate, DeletedAt, AddressCity) VALUES (" 105: + $('id') + "','" 106: + $('visit_num') + "','" 107: + '80a1c95c-6480-47e7-ab44-c35cf8328b3e' + "','" 108: + $('integration_id') + "','" LINE NUMBER: 104 DETAILS: Wrapped java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "2916544": syntax error) at e0904d97-23f2-4846-a9ab-32962837472a:104 (doScript) at e0904d97-23f2-4846-a9ab-32962837472a:139 at com.mirth.connect.server.util.javascript.JavaScriptUtil.executeScript(JavaScriptUtil.java:547) at com.mirth.connect.connectors.jdbc.DatabaseDispatcherScript$DatabaseDispatcherTask.doCall(DatabaseDispatcherScript.java:120) at com.mirth.connect.server.util.javascript.JavaScriptTask.call(JavaScriptTask.java:113) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at java.base/java.lang.Thread.run(Thread.java:832)

And this code, should add data to JSON, but in database Iam getting just '${additionalObject}' variable.

`
var additionalFields = {};
var obxList = msg..*::OBX;
logger.info("total number of OBX segments: " + obxList.length());
var counter = 0;

while (counter < obxList) {
	for each (var obx in msg.OBX) {
		var addField = msg['OBX'][counter]['OBX.3']['OBX.3.2'].toString();
		var addValue  = msg['OBX'][counter]['OBX.5']['OBX.5.1'].toString();
		var addUnit = msg['OBX'][counter]['OBX.6']['OBX.6.1'].toString();
		var result = addValue+''+addUnit;

		additionalFields[addField] = result;
	
		var objjson = JSON.stringify(additionalFields);
		var additionalObject = JSON.parse(objjson);
		logger.info(additionalObject);
		channelMap.put('additionalObject', additionalObject);
		counter++;
		
	}
}`

[pacmano1]

Build your SQL statement in a variable then put that variable in the channelMap.

Then you can see the exact SQL you created and can cut and paste it to your SQL tool and execute it to verify your syntax.

Comment out the actual execute query command while you test building the query string.

You can of course logger.info(query). But that gets lost in the console log.

Also this syntax is a bit easier, thanks to @tonygermano for showing me this a long time ago. BE CAREFUL about single quotes in the value of your variables with this.

$c('some_var','test')  // an example from a channelMap var
var some_other_var = 'stuff' // an example of just a var
var query=<sql>
select * 
from table 
where id = '{$c('some_var')}' and x = '{some_other_var}'
</sql>.toString();

$c('SQLGetRows',query)   // you can now see this in the mapping tab in the message - i.e. w/the actual substituon
// the execute your query.

Best practices would dictate paramterized db call, but I'll leave that be for now.

[tGitm]

Thanks for reply! So then I can just call this variable in destination javascript and do other things with it?
Can you answer on the second part of my question too please :)

[pacmano1]

You can then use that $c (that is just a shortcut for channelMap) variable throughout the channel. Again be careful of the value of a field you exact from HL7 has single quotes since they would make the SQL query break which will be obvious when you look at the generated SQL in the channel map.

You need to search the mirth forums - there are examples on how to get the entire hl7 message.

See https://forums.mirthproject.io/forum/mirth-connect/support/13097-insert-hl7-messages-to-mysql-db

[tGitm]

Thanks for your help!

Now I changed my driver to custom one, because I had problems with inserting to database. I added cachejdbc.jar to "server-lib/databases" and to custom-lib and editet my dbdrivers.conf like this:
<drivers> <driver class="com.mysql.cj.jdbc.Driver" name="MySQL" template="jdbc:mysql:https://host:port/dbname" selectLimit="SELECT * FROM ? LIMIT 1" alternativeClasses="com.mysql.jdbc.Driver" /> <driver class="oracle.jdbc.driver.OracleDriver" name="Oracle" template="jdbc:oracle:thin:@host:port:dbname" selectLimit="SELECT * FROM ? WHERE ROWNUM &lt; 2" /> <driver class="org.postgresql.Driver" name="PostgreSQL" template="jdbc:postgresql:https://host:port/dbname" selectLimit="SELECT * FROM ? LIMIT 1" /> <driver class="net.sourceforge.jtds.jdbc.Driver" name="SQL Server/Sybase (jTDS)" template="jdbc:jtds:sqlserver:https://host:port/dbname" selectLimit="SELECT TOP 1 * FROM ?" /> <driver class="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="Microsoft SQL Server" template="jdbc:sqlserver:https://host:port;databaseName=dbname" selectLimit="SELECT TOP 1 * FROM ?" /> <driver class="org.sqlite.JDBC" name="SQLite" template="jdbc:sqlite:dbfile.db" selectLimit="SELECT * FROM ? LIMIT 1" /> <driver class="com.intersys.jdbc.CacheDriver" name="Cache 5"/> </drivers>

Now I'm getting this error:
java.lang.ClassNotFoundException: java.sql.Driver at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:435) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:589) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522) at java.base/java.lang.ClassLoader.defineClass1(Native Method) at java.base/java.lang.ClassLoader.defineClass(ClassLoader.java:1016) at java.base/java.security.SecureClassLoader.defineClass(SecureClassLoader.java:151) at java.base/java.net.URLClassLoader.defineClass(URLClassLoader.java:514) at java.base/java.net.URLClassLoader$1.run(URLClassLoader.java:422) at java.base/java.net.URLClassLoader$1.run(URLClassLoader.java:416) at java.base/java.security.AccessController.doPrivileged(AccessController.java:691) at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:415) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:589) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522) at com.mirth.connect.connectors.jdbc.CustomDriver.<init>(CustomDriver.java:40) at com.mirth.connect.server.userutil.DatabaseConnectionFactory.initializeDriverAndGetInfo(DatabaseConnectionFactory.java:145) at com.mirth.connect.server.userutil.DatabaseConnectionFactory.getCustomDriverInfo(DatabaseConnectionFactory.java:167) at com.mirth.connect.server.userutil.DatabaseConnectionFactory.createDatabaseConnection(DatabaseConnectionFactory.java:54) at jdk.internal.reflect.GeneratedMethodAccessor105.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138) at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:226) at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1669) at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1009) at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109) at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:412) at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3545) at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:121) at com.mirth.connect.server.util.javascript.JavaScriptTask.executeScript(JavaScriptTask.java:150) at com.mirth.connect.server.util.javascript.JavaScriptUtil.executeScript(JavaScriptUtil.java:542) at com.mirth.connect.connectors.jdbc.DatabaseDispatcherScript$DatabaseDispatcherTask.doCall(DatabaseDispatcherScript.java:120) at com.mirth.connect.server.util.javascript.JavaScriptTask.call(JavaScriptTask.java:113) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at java.base/java.lang.Thread.run(Thread.java:832)

This is my code in destination database writer:
`var dbConn;
var driver;
var result;

try {

driver = new com.mirth.connect.connectors.jdbc.CustomDriver(java.lang.Thread.currentThread().getContextClassLoader(), 'com.intersys.jdbc.CacheDriver');
dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.intersys.jdbc.CacheDriver','jdbc:Cache:D:/documents/MESI/mesi-integration/mesi-integration.db','','');
/*logger.info($('otherDataMeasurements'));
logger.info($('integration_id'));
logger.info($('id'));
logger.info($('serial_number'));
logger.info($('tablet_group_id'));
logger.info($('application_id')); */

result = dbConn.executeUpdate("INSERT INTO Tasks (Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, Status, PatientId, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, ApplicationId, DoctorFirstName, DoctorLastName, AdditionalFields, TabletAcceptedBy, RequestContent, RequestDate, DeletedAt, AddressCity) VALUES (" 
		+ $('id') + "','"
		+ $('visit_num') + "','"
		+ '80a1c95c-6480-47e7-ab44-c35cf8328b3e' + "','"
		+ $('integration_id') + "','"
		+ $('patient_id') + "','"
		+ $('given_name') + "','"
		+ $('family_name') + "','"
		+ $('dateBirth') + "','"
		+ $('gender') + "','"
		+ $('race_code') + "','"
		+ $('email') + "','"
		+ $('tel_number') + "','"
		+ $('street_address') + "','"
		+ 2 + "','" 
		+ $('postal_code') + "','"
		+ $('country') + "','"
		+ $('attending_doc_given_name') + "','"
		+ $('attending_doc_family_name') + "','"
		+ $('testestestest') + "','"
		+ $('OrderMessage') + "','"
		+ $('request_date') + "','"
		+ 2 + "','"
		+ $('serial_number') + "','"
		+ $('tablet_group_id') + "','"
		+ 0 + "','" 
		+ $('city') + ")"
);

return 'Result of channel: ' + result;

} finally {
if (dbConn) {
dbConn.close();
}
}`

[pacmano1]

Please don't switch topics on posts. Mark one of the responses as an answer (if answered).

Start a new post with the new problem.

[tGitm]

Ok I opened new issue.

[pacmano1]

This discussion was answered in the thread of messages, however no particular item was tagged as the exact answer by the orginal poster. Original poster changed thread topics at the end.