Mirth : database reader, how to deal with [multiple rows] returned from sql query?

Question

I am new to Mirth connect and I need some help

I am working on a demo like the following:

the source is Database Reader the destination is a Document writer

The SQL select query in the source returns multiple rows "and this is what I need" I am trying to generate a pdf document [ the document writer ] which contains the values of all returned columns but Actually , what is written in the file is the last returned row ONLY

this is the HTML template I wrote

<html>
<head></head>

<body>
<div>
${Target_path}\\${fileName}
</div>

</body>
</html>

and in the destination , I have transformers of type Mapping which maps the values of the returned columns to string

The SQL statement selects two columns from my database , both are strings

the first column represents a path , and the second column represents a file name So I have many file names returned from the sql statement and I need to write all of them to this document

Any hints about how can I deal with every row returned from the query?

Best Regards,

You have to loop the template content... Just check the this link you will get an idea mirthified.wordpress.com/category/mirth-tool .. when you get the data from the DB you will loop content too. leakymllp.com/2010/08/17/MirthHL7ToPDF.html — Vibin Guevara
I got it , but the difference here is that I am not converting from HL7msg but from xml , database selection .. so I am doing the same but I have problem with this line : var file_name = msg['file_name'].toString(); , it gives me this error TypeError: Cannot call method "toString" of undefined .. Which works first? the javascript transformer or the sql query from the database reader source? any hints on how to overcome this ? — Heba A.

Austin Yi Austin Yi · Accepted Answer · 2016-11-04T22:32:34

I'm using JavaScript instead to pull my data but you can format your entries beforehand and insert them into an ArrayList. From there, map the list to a channel map variable.

var dbConn;
var result;
var entryList = java.util.ArrayList();

try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('DRIVER', 'ADDRESS', 'USERNAME', 'PASSWORD');
    result = dbConn.executeCachedQuery('YOUR QUERY');
    while (result.next()) {
        var entry = result.getString(1) + "//" + result.getString(2);
        list.add(entry);
    }
} finally {
    if (dbConn) {
        dbConn.close();
    }
}

channelMap.put('entryList', entryList);

In your template, you can use Velocity in your html template to dynamically create your PDF like so.

<html>
    <head/>
    <body>
        #foreach ($entry in ${entryList})
        <div>
            $entry
        </div>
        #end
    </body>
</html>

Mirth : database reader, how to deal with [multiple rows] returned from sql query?

1 Answers