Dinamic SQL Statement

[pitchfork]

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...

[chrisl]

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

[pitchfork]

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

[chrisl]

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

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'

[albertosaez]

Re: Dinamic SQL Statement

As this is clear in

http://www.mirthproject.org/index.php?option=com_loudmouth&task=topic&id=52&Itemid=63

You shouldn't use variables defined in the filter.

[chrisl]

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