web stats
velocity variable replacement in database reader sql template - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 12-20-2017, 06:30 AM
usuariousuario usuariousuario is offline
What's HL7?
 
Join Date: Sep 2016
Posts: 4
usuariousuario is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-21-2017, 12:59 AM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 832
siddharth is on a distinguished road
Default

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!
Reply With Quote
  #3  
Old 12-21-2017, 08:53 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,116
narupley is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 12-22-2017, 02:38 AM
usuariousuario usuariousuario is offline
What's HL7?
 
Join Date: Sep 2016
Posts: 4
usuariousuario is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
database, reader, velocity

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 04:41 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation