Announcement

Collapse
No announcement yet.

velocity variable replacement in database reader sql template

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

  • velocity variable replacement in database reader sql template

    Hi everybody,

    I am using
    • mirth connect mirthconnect-3.5.1.b194
    • CentOS Linux release 7.3.1611 (Core)
    • java version "1.8.0_131"
      Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
      Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)



    I have declared a Global Map variable like this

    Code:
    $g('max',12356); // note the data type is an integer, not a string
    And in a DataBase Reader (SQL template, not a javascript one) againts a sql server 2012

    Code:
    SELECT * FROM TABLE WHERE ID_TABLE > ${max}
    Field Table.id_table is int type too.

    Whenever the channel runs the query, I get the following error.

    Code:
    ERROR (com.mirth.connect.connectors.jdbc.DatabaseReceiverQuery:207): An error occurred while polling for messages, retrying after 10000 ms...
    
    java.sql.SQLException: Conversion failed when converting the varchar value '12356.0' to data type int.
    Velocity is changing the data type from int to string, and it is also adding a .0 at the end of the integer.

    If I change the select to use a cast(${max} as int) it does not work either because of the ending .0


    A workaround is to store the variable in the global map between ' ' and use the cast function. But I find it "dirty" to store an integer as a string.

    Is there any way to velocity not changing the data type or adding the final .0

    Thank you very much in advance

  • #2
    Here are some tests I ran. Maybe this would help

    Code:
    $g('max',12356);
    logger.info("1 " + typeof($('max'))); //object
    max=$('max');
    logger.info("2 " + typeof(max)); //object
    logger.info(max); //12356.0
    logger.info(''+max); //12356
    logger.info(typeof(''+max)); //string
    HL7v2.7 Certified Control Specialist!

    Comment


    • #3
      You can explicitly put a Java Integer/Long in the map rather than a JavaScript Number (which gets converted to a Java Double):

      Code:
      $g('max', new java.lang.Integer(12356));
      Or you can usually cast the parameter in your SQL script:

      Code:
      SELECT * FROM TABLE WHERE ID_TABLE > ${max}::INTEGER
      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


      • #4
        The ${max}::INTEGER syntax is not working for me. I receive an incorrect syntax near :: error

        But the globalMap.put('max',new java.lang.Integer(123456)); works perfectly.

        Thank you very much.

        Comment

        Working...
        X