Announcement

Collapse

Mirth Connect 3.12.0 Released!

Mirth Connect 3.12.0 is now available as an appliance update and on our GitHub page. This release includes database performance improvements, improves visual HL7 representation, message pruning, keystore handling, PDF generation, community contributions, and fixes several security vulnerabilities. This release also contains many improvements to commercial extensions. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

Transformer Javascript step for Date Conversion

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

  • Transformer Javascript step for Date Conversion

    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

  • #2
    Re:Transformer Javascript step for Date Conversion

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

    Comment


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

      Comment


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

        Code:
        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!

        Comment


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

          Comment


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

            Comment


            • #7
              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').
              Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

              Nicholas Rupley
              Work: 949-237-6069
              Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


              - How do I foo?
              - You just bar.

              Comment


              • #8
                I tried that but that didn't work either.

                Comment


                • #9
                  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.
                  Last edited by brenthopkins; 09-26-2013, 11:12 AM.

                  Comment


                  • #10
                    What is your DB?

                    Comment


                    • #11
                      MySQL

                      Comment


                      • #12
                        try this instead....


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

                        Comment


                        • #13
                          or


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

                          Comment


                          • #14
                            MySQL

                            Comment


                            • #15
                              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)

                              Comment

                              Working...
                              X