Announcement

Collapse
No announcement yet.

Dinamic SQL Statement

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

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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment

            Working...
            X