Transformer Javascript step for Date Conversion

[RobinP]

Hi,

I've just started with Mirth 1.6. and it looks promising for our UK project.

I have set up a channel with a FileReader source going into a SQL server 2000 database table. I'm having problems reading dates date/times from HL7 messages and these are crucial for the application.

I realise I need to use a Javascript step in the Transformer as Mirth doesn't seem to handle dates as we would expect.

I'm not clear on how to use a Javascript step. I have written this to convert the Admit date:-

var datechange = DateUtil.getDate("yyyyMMddHHmmss", msg['PV1']['PV1.44']['PV1.44.1']);
localMap.put('AdmitDateConvert', datechange);
return;

But I get the following error
"Can't find method com.webreach.mirth.server.util.DateUtil.formatDate string,function)"
which is surprising as it's an internal function.
How should I be converting a date to go into a datetime filed in a SQL Server table?
And how then do I apply the result for this step in the SQL clause
INSERT IGNORE INTO ....... VALUES (...... in the destination.

Appreciate this may be a basic question but any guidance would be appreciated.

Cheers
Robin

[nshaik]

Re:Transformer Javascript step for Date Conversion

http://www.mirthproject.org/index.ph...1&catid=2#2101

[RobinP]

Re:Transformer Javascript step for Date Conversion

Hi, thanks for the pointer to other discussions around date conversion.

I had read those previously but could not see clearly how to simply get date and time into a SQL Server table.
In fact I simply want to transfer the value of the date. I don't really want to convert it.
For example the date is in the format 20061214033000 in the HL7 message and I just want to pass this into a datetime field in SQL.
However even making the receiving field in SQL a string, it still errors.
So it is in Mirth's handling of datatypes that seems to be the problem.

It only seems to be able to handle strings. I've found it wont even pass in integers.
Does Mirth only handle strings directly?

I would have thought that handling datetime fields would have been a fundamental requirement in a transformer.

But do excuse the fact I am new to Mirth.

Thanks in advance
Robin

[nshaik]

Re:Transformer Javascript step for Date Conversion

In fact I simply want to transfer the value of the date. I don't really want to convert it.

E.g., SQL Table Appointment,
TempID [int] IDENTITY (1, 1) NOT NULL
DateInCharFormat Varchar(20)

Set TempID as Primary Key

Check if the code below would work:

var apptStart;
// Get appt. start datetime with no conversion
if (msg['SCH']['SCH.11']['SCH.11.4'] != null) {   
  apptStart = msg['SCH']['SCH.11']['SCH.11.4'].toString();                         
  if (apptStart.length > 0) {
     apptStart =  "'" + apptStart + "'";
  }
  else {
     apptStart = "NULL";
  }             
}

channelMap.put('patientApptStart', apptStart);

Pass $('patientApptStart') as the parameter to the SQL INSERT statement

Sample: INSERT IGNORE INTO Appointment(DateInCharFormat) VALUES ($('patientApptStart'))

Hope this helps!

[RobinP]

Re:Transformer Javascript step for Date Conversion

Hi nShaik,

Fantastic, that worked. As a quick test I wrote this without all the checks.


var datechange = "'" + msg['PV1']['PV1.44']['PV1.44.1'].toString() + "'"
channelMap.put('AdmitDateConvert', datechange);

Thanks, it was also the channelMap.Put function I didn't know the syntax for. That's really useful to know for a Javascript STEP.

Funny enough after making the datefield a nvarchar in SQL, the previous Mapped step DatOfArrival worked, even though I'm sure I tested this before.
It seems that I have to have all varchar fields in my SQL Table. This is not a big deal as I can then process input with Triggers and handle validation in SQL.

Many thanks for your patience.

Regards Robin

[brenthopkins]

With Mirth Connect 2.2.3 I am trying to do something similar, but getting the following error:
[2013-09-18 11:15:22,926] ERROR (org.mule.impl.DefaultComponentExceptionStrategy:9 5): Caught exception in Exception Strategy for: 87933b5b-7efc-4b89-b7ba-443d5f8fbf9d: java.sql.SQLException: FUNCTION patient.$ does not exist Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Message, Address, Create_Date)
VALUES (?, ?, $('nullDob'), ?, ?, ?)
Parameters: [Garner E Madeline, ID363632, A02, Carter Rd, Lumberton, NC 28358, 2013-9-18 11.15.22]
java.sql.SQLException: FUNCTION patient.$ does not exist Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Message, Address, Create_Date)VALUES (?, ?, $('nullDob'), ?, ?, ?) Parameters: [Garner E Madeline, ID363632, A02, Carter Rd, Lumberton, NC 28358, 2013-9-18 11.15.22]
at org.apache.commons.dbutils.AbstractQueryRunner.ret hrow(AbstractQueryRunner.java:320)
at org.apache.commons.dbutils.QueryRunner.update(Quer yRunner.java:489)
at org.apache.commons.dbutils.QueryRunner.update(Quer yRunner.java:402)
at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doDispatch(JdbcMessageDispatcher.java:134)
at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doSend(JdbcMessageDispatcher.java:162)
at org.mule.providers.AbstractMessageDispatcher.send( AbstractMessageDispatcher.java:164)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:191)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:130)
at org.mule.routing.outbound.AbstractOutboundRouter.s end(AbstractOutboundRouter.java:85)
at org.mule.routing.outbound.FilteringMulticastingRou ter.route(FilteringMulticastingRouter.java:54)
at org.mule.routing.outbound.OutboundMessageRouter$1. doInTransaction(OutboundMessageRouter.java:78)
at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:48)
at org.mule.routing.outbound.OutboundMessageRouter.ro ute(OutboundMessageRouter.java:82)
at org.mule.impl.model.DefaultMuleProxy.onCall(Defaul tMuleProxy.java:247)
at org.mule.impl.model.seda.SedaComponent.doSend(Seda Component.java:209)
at org.mule.impl.model.AbstractComponent.sendEvent(Ab stractComponent.java:277)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:201)
at org.mule.routing.inbound.InboundMessageRouter.send (InboundMessageRouter.java:176)
at org.mule.routing.inbound.InboundMessageRouter.rout e(InboundMessageRouter.java:143)
at org.mule.providers.AbstractMessageReceiver$Default InternalMessageListener.onMessage(AbstractMessageR eceiver.java:487)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:266)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:225)
at com.mirth.connect.connectors.vm.VMMessageReceiver. getMessages(VMMessageReceiver.java:222)
at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:108)
at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:97)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor.runWorker(ThreadPoolExecutor.java: 1061)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :575)
at java.lang.Thread.run(Unknown Source)

Am using the following code:
var newDob = msg['PID'][0]['PID.7']['PID.7.1'].toString();

newDob = checkDob(newDob);

channelMap.put('nullDob', newDob);
function checkDob(dob) {
if (dob == "" || dob == " " || dob == null ) {
return "NULL";
} else {
var aDob = DateUtil.convertDate('yyyyMMddHHmm', 'yyyy-M-d H.m', dob);
return aDob;
}
}

And the following SQL for MySQL:
INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Message, Address, Create_Date)
VALUES (${PatientName}, ${PatientId}, $('nullDob'), ${Message}, ${Address}, ${date.get('yyyy-M-d H.m.s')})

I have tried quite a few variations on this, and they all give errors if DOB is not there. Any help would be greatly appreciated.

[narupley]

In SQL mode, you need to use Velocity tokens, not JavaScript. Like all the other columns in your query, it should be ${nullDob}, not $('nullDob').

[brenthopkins]

I tried that but that didn't work either.

[brenthopkins]

Have tried the following:

INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)
VALUES (${PatientName}, ${PatientID}, (CASE WHEN (${DateOfBirth} IS NOT NULL) THEN ${DateOfBirth} ELSE null END), current_timestamp )

Works fine if there is a DOB, but if not I am still getting a MySQL error similar to above:
2013-09-26 14:57:10,617] ERROR (org.mule.impl.DefaultComponentExceptionStrategy:9 5): Caught exception in Exception Strategy for: 062883ba-413e-4c9d-a6dc-699c57f3fe02: java.sql.SQLException: Data truncation: Incorrect datetime value: 'null' for column 'DateOfBirth' at row 1 Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)
VALUES (?, ?, (CASE WHEN (? IS NOT NULL) THEN ? ELSE null END), current_timestamp )
Parameters: [Madeline Garner, ID363632, null, null]
java.sql.SQLException: Data truncation: Incorrect datetime value: 'null' for column 'DateOfBirth' at row 1 Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)VALUES (?, ?, (CASE WHEN (? IS NOT NULL) THEN ? ELSE null END), current_timestamp ) Parameters: [Madeline Garner, ID363632, null, null]

I am told this works in Mirth Connect to a postgresql database, but cannot get it to work in Mirth Connect to a MySQl database. If I simply cut and paste the insert statement into MySQL and put in values, I get no error whether DOB is a date or a blank or null. I should mention again that the Date of Birth field I am trying to insert values into in MySQL is of field type DATETIME, and the value is something like 19361118000000.

Any help would be greatly appreciated.

[cory_cole]

What is your DB?

[brenthopkins]

MySQL

[cory_cole]

try this instead....


INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)
VALUES (${PatientName}, ${PatientID}, (coalesce(${DateOfBirth},''), current_timestamp )

[cory_cole]

or


INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)
VALUES (${PatientName}, ${PatientID}, (CASE ${DateOfBirth} WHEN '' THEN NULL ELSE ${DateOfBirth}), current_timestamp )

[brenthopkins]

MySQL

[brenthopkins]

This gives me the following error:
[2013-09-26 15:34:23,343] ERROR (org.mule.impl.DefaultComponentExceptionStrategy:9 5): Caught exception in Exception Strategy for: 062883ba-413e-4c9d-a6dc-699c57f3fe02: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2 Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)
VALUES (?, ?, (coalesce(?,''), current_timestamp )
Parameters: [Madeline Garner, ID363632, 19361118000000]
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2 Query: INSERT IGNORE INTO patient (PatientName, PatientID, DateOfBirth, Create_Date)VALUES (?, ?, (coalesce(?,''), current_timestamp ) Parameters: [Madeline Garner, ID363632, 19361118000000]
at org.apache.commons.dbutils.AbstractQueryRunner.ret hrow(AbstractQueryRunner.java:320)
at org.apache.commons.dbutils.QueryRunner.update(Quer yRunner.java:489)
at org.apache.commons.dbutils.QueryRunner.update(Quer yRunner.java:402)
at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doDispatch(JdbcMessageDispatcher.java:134)
at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doSend(JdbcMessageDispatcher.java:162)
at org.mule.providers.AbstractMessageDispatcher.send( AbstractMessageDispatcher.java:164)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:191)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:130)
at org.mule.routing.outbound.AbstractOutboundRouter.s end(AbstractOutboundRouter.java:85)
at org.mule.routing.outbound.FilteringMulticastingRou ter.route(FilteringMulticastingRouter.java:54)
at org.mule.routing.outbound.OutboundMessageRouter$1. doInTransaction(OutboundMessageRouter.java:78)
at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:48)
at org.mule.routing.outbound.OutboundMessageRouter.ro ute(OutboundMessageRouter.java:82)
at org.mule.impl.model.DefaultMuleProxy.onCall(Defaul tMuleProxy.java:247)
at org.mule.impl.model.seda.SedaComponent.doSend(Seda Component.java:209)
at org.mule.impl.model.AbstractComponent.sendEvent(Ab stractComponent.java:277)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:201)
at org.mule.routing.inbound.InboundMessageRouter.send (InboundMessageRouter.java:176)
at org.mule.routing.inbound.InboundMessageRouter.rout e(InboundMessageRouter.java:143)
at org.mule.providers.AbstractMessageReceiver$Default InternalMessageListener.onMessage(AbstractMessageR eceiver.java:487)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:266)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:225)
at com.mirth.connect.connectors.vm.VMMessageReceiver. getMessages(VMMessageReceiver.java:222)
at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:108)
at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:97)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor.runWorker(ThreadPoolExecutor.java: 1061)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :575)
at java.lang.Thread.run(Unknown Source)