web stats
configurationMap in "database reader settings" SQL box - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 10-15-2019, 05:37 AM
prakashjoy prakashjoy is offline
Mirth Newb
 
Join Date: Sep 2016
Posts: 18
prakashjoy is on a distinguished road
Default configurationMap in "database reader settings" SQL box

Mirth version is 3.4.2

configurationMap configured for below key's

PrimaryDbURL
PrimaryDbUser
PrimaryDbPassword
PrimaryDbSchema

Channel Source Connector type is "Databse reader"
Database Reader Settings

Driver -> Postgressql
URL -> ${PrimaryDbURL}
Username > ${PrimaryDbUser}
Password -> {PrimaryDbPassword}

SQL -> query is " select item_id from uat.hl7_control_items where status='N' ";

I am getting error for below query.
select item_id from ${PrimaryDbSchema}.hl7_control_items where status='N'

for configurationMap.get('PrimaryDbSchema'); and ${PrimaryDbSchema} mirth is not identifying the schema name.

Please help me to solve this problem
Reply With Quote
  #2  
Old 10-15-2019, 06:01 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 255
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

Could you post a screenshot of your configuration map and an export of your channel?
__________________

-= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines
Reply With Quote
  #3  
Old 10-15-2019, 06:48 AM
prakashjoy prakashjoy is offline
Mirth Newb
 
Join Date: Sep 2016
Posts: 18
prakashjoy is on a distinguished road
Default ScreenShot

Hi Jack

Thanks for considering the query.


I am attaching the screenshot
Attached Images
File Type: jpg screenshot.jpg (175.9 KB, 6 views)
Reply With Quote
  #4  
Old 10-15-2019, 06:55 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 255
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

Send the screenshot of the configurationmap too please?
__________________

-= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines
Reply With Quote
  #5  
Old 10-15-2019, 07:01 AM
prakashjoy prakashjoy is offline
Mirth Newb
 
Join Date: Sep 2016
Posts: 18
prakashjoy is on a distinguished road
Default ScreenShot config

configuration map screen shot attached
Attached Images
File Type: jpg screenshot1.jpg (102.2 KB, 4 views)
Reply With Quote
  #6  
Old 10-15-2019, 07:23 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 255
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

The quick and easy way is to use JavaScript (instead of just the SQL query) and use:

Code:
var query = "select item_id from "+$('PrimaryDbSchema')+".hl7_control_items where status='N' ";
Also note the $(' not ${'
__________________

-= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines
Reply With Quote
  #7  
Old 10-15-2019, 07:40 AM
prakashjoy prakashjoy is offline
Mirth Newb
 
Join Date: Sep 2016
Posts: 18
prakashjoy is on a distinguished road
Default Default configurationMap in "database reader settings" SQL box

Hi jack,

In case of JavaScript, how to access the result in source transformation. Channlemap.put is not working in source JavaScript connector type.
Reply With Quote
  #8  
Old 10-15-2019, 07:43 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 255
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

You are correct, you can't use channelMap.put in the JS source, but the query is used to get the row from the table... It will return the row as XML that you can then manipulate to your liking. From that point forward, you would use transformer like normal.
__________________

-= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines
Reply With Quote
  #9  
Old 10-15-2019, 08:14 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 1,176
agermano is on a distinguished road
Default

Quote:
Originally Posted by jackwhaines View Post
The quick and easy way is to use JavaScript (instead of just the SQL query)
This is true.

SQL mode doesn't use velocity replacement like many of the other connectors do. SQL mode produces a prepared statement and converts the replacement tokens to parameters.

According to this link, you can't pass identifiers (such as the schema) as a parameter to a prepared statement.

The jdbc driver does have a property you can set for currentSchema that might work, and the url does use normal Velocity replacement. If you set the schema in your url, I think you can still use SQL mode and change your query to:

Code:
select item_id from hl7_control_items where status='N'
Reply With Quote
  #10  
Old 10-15-2019, 09:08 PM
prakashjoy prakashjoy is offline
Mirth Newb
 
Join Date: Sep 2016
Posts: 18
prakashjoy is on a distinguished road
Default configuration map

Thank you jack and Thank you agermano for the valuable responses.

I resolved the issue by adding "?currentSchema=uat" in the db url in configuration map
Reply With Quote
Reply

Tags
configurationmap

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 06:47 AM.


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