web stats
Dinamic SQL Statement - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 09-22-2006, 12:17 AM
pitchfork pitchfork is offline
Mirth Newb
 
Join Date: Sep 2006
Posts: 19
pitchfork
Default Dinamic SQL Statement

Hi everybody,

I'm performing an outbound channel. In the source tab, in the SQL Statement field I want to wtrite a dinamic sql sentence. I mean, I want to use a var. For instance:

SELECT name FROM patients WHERE id>=myvar.

Where can I store my var, so I can write and read it? I cannot create any table in the database I'm connected to. :-S

Thanks...
Reply With Quote
  #2  
Old 09-22-2006, 12:26 AM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: Dinamic SQL Statement

Unfortunantly this is not supported from the source tab.

You COULD create something like this in a transformer or filter script...however this won't help you if do not have a proper source database.

Again, not the ideal architecture - but you could have a "dummy" SQL statement in your source that always returned one result. You could then have your filter or transformer do the "REAL" database call using the variable you defined.

Check the file repository and a few other posts (developer forum) for examples on connecting to the database from the transformer or filter.

-Chris
__________________
Chris Lang
Reply With Quote
  #3  
Old 09-22-2006, 12:34 AM
pitchfork pitchfork is offline
Mirth Newb
 
Join Date: Sep 2006
Posts: 19
pitchfork
Default Re: Dinamic SQL Statement

Thanks, Chris.

I know how to connect a database from the filter. But where do I have to store the result records? Is there any var where I should assign them so Mirth could develop the HL7 messages?

Thanks again
Reply With Quote
  #4  
Old 09-22-2006, 12:40 AM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: Dinamic SQL Statement

When you create the db conn in the filter, you read the results with the CachedRowSet, so you can place the variables in either the the localMap or the globalMap. (I have to verify that the localMap will work across Filter and Transformer, but it SHOULD).

So say you read from the database and have

Code:
var patient_name = results.get(0);
localMap.put('patient_name',patient_name);
Inside your transformer you would have:

HL7 Message Builder
[hr]
HL7 Segment = hl7_xml['PID']['PID.5']['XCN'] (this would be dragged from the HL7 tree which is populated by the template)
Mapping = localMap.get('patient_name');

__________________
Chris Lang
Reply With Quote
  #5  
Old 09-26-2006, 02:52 PM
albertosaez albertosaez is offline
 
Join Date: Sep 2006
Location: Valladolid, Spain
Posts: 842
albertosaez is an unknown quantity at this point
Default Re: Dinamic SQL Statement

As this is clear in

http://www.mirthproject.org/index.ph...d=52&Itemid=63

You shouldn't use variables defined in the filter.
Reply With Quote
  #6  
Old 09-26-2006, 04:10 PM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: Dinamic SQL Statement

Thanks Alberto - this is correct. The localMap is not persisted across the filter to the transformer.

Because it has been brought to our attention, we will look at adding this functionality if desired.

-Chris
__________________
Chris Lang
Reply With Quote
Reply

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Invalid SQL statement svart Support 1 09-16-2008 05:31 AM
Executing SQL Statement into script tab Alexander Support 0 11-19-2007 09:38 PM
Help with prepared statement in javascript josemathews Support 2 09-13-2007 01:24 AM
on-update statement problem carles Support 2 08-28-2007 12:26 AM
SQL statement processing coffde Support 3 01-03-2007 01:16 PM


All times are GMT -8. The time now is 03:19 AM.


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