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

Data truncation:Out of range value adjusted ...

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

  • Data truncation:Out of range value adjusted ...

    Hello all,

    I have the following problem:

    I have a channel that receives HL7-Messages and stores some data from the messages to a MySQL database. One value of the data is from type "datetime" (for example Date/Time of birth of a patient). If the datetime-value exists in the HL7-Message everything works fine. But if this value is missing, the MySQL-Database throws the following error on insert:

    Code:
    Wrapped com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column 'birthdate' at row 1
    	at com.webreach.mirth.server.mule.transformers.JavaScriptTransformer.evaluateScript(JavaScriptTransformer.java:439)
    	at com.webreach.mirth.server.mule.transformers.JavaScriptTransformer.transform(JavaScriptTransformer.java:346)
    	at org.mule.transformers.AbstractEventAwareTransformer.doTransform(AbstractEventAwareTransformer.java:48)
    	at org.mule.transformers.AbstractTransformer.transform(AbstractTransformer.java:197)
    	at org.mule.impl.MuleEvent.getTransformedMessage(MuleEvent.java:251)
    	at com.webreach.mirth.server.controllers.MessageObjectController.getMessageObjectFromEvent(MessageObjectController.java:507)
    	at com.webreach.mirth.connectors.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:76)
    It seems that the DB interprets the missing value as an empty string. I partially fixed the problem with the following code:

    Code:
    if (birthdate1==""«») birthdate1=null
    But is there a smarter approach? Should I apply this code to every datetime-value on my HL7-Message? (Not only datetime but every value different from string)
    Do you have any idea?

    Thanks for any help!!

    Greets,
    Svetlomir

    Post edited by: Svetlomir Kasabov, at: 07/07/2008 08:42

  • #2
    Reata truncation:Out of range value adjusted ...

    How are you building the SQL query? Are you doing it directly in Javascript or are you using a transformer?
    Jon Bartels

    Zen is hiring!!!!
    http://consultzen.com/careers/
    Talented healthcare IT professionals wanted. Engineers to sales to management.
    Good benefits, great working environment, genuinely interesting work.

    Comment


    • #3
      Reata truncation:Out of range value adjusted ...

      Thanks for the quick reply jbartels,

      I use javascript-transformer. It calls a mysql-stored-procedure and gives the datetime value as an argument to the stored procedure.

      Code:
      dbConn.executeQuery("call insertUpdateA01('" + birthday+"','"...
      That's where the error occurs, because the stored procedure expects a datetime-value and gets a wider-type, string value.

      I tried with deleting the '-characters around the birthday value(in order to avoid giving a string value to the stored procedure) but I got a SQL-Syntax-Error.

      Thanks for your help

      Svetlomir

      Post edited by: Svetlomir Kasabov, at: 07/07/2008 09:22

      Comment


      • #4
        Reata truncation:Out of range value adjusted ...

        Ok so it sounds like the birthday argument is optional for the stored procedure.

        The stored procedure should be able to handle NULL, empty string, string, etc and do the conversion of that field to the MySQL Date type.

        So something like:

        Code:
        SQL:
        
        create stored proc insertUpdateA01
         param birthday
         param ...
        
         if birthday IS NULL
          bday = NULL
         elsif birthday IS STRING
          bday = CONVERT(birthday, DATETIME)
         else
          ...
        I know that is very rough and probably not valid SQL. If you put that logic in the stored procedure it is more portable, easier to maintain, and will likely run faster than the javascript.
        Jon Bartels

        Zen is hiring!!!!
        http://consultzen.com/careers/
        Talented healthcare IT professionals wanted. Engineers to sales to management.
        Good benefits, great working environment, genuinely interesting work.

        Comment

        Working...
        X