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

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,

Solution

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>