web stats
Mirth Community - View Single Post - Mirth Tools: User defined functions
View Single Post
Old 03-16-2018, 04:47 AM
aTom aTom is offline
OBX.3 Kenobi
Join Date: Feb 2016
Location: Lake Geneva Area
Posts: 115
aTom is on a distinguished road
Default Custom DB aggregator

Here's my small contribution. I had to work on this some time ago and I though it was worth to share.

Starting with Mirth 3.5 new DB Reader "Aggregate Results" option, this scripts allows you to debatch/aggregate records according to one column value.

To use it
- go to Source tab, enable Process Batch, enable "Aggregate Results" and set appropriate ORDER BY clause to your query
- go to Summary tab, open Set Data Types window, set Split Batch By to Javascript, paste script in corresponding field and set target column name (in lower case)

As DB Reader generates pretty printed XML, we are able to build results reading one line after the other. This might change in a future release.

EDIT : Actually, while reading it for the first time in weeks, I just figured out there is a much more efficient way to handle this (first version of script is left for info) :

var columnName = "column name in lower case" //<== SET TARGET COLUMN NAME HERE
var line = ''; // holds single lines read from buffered reader		
var result = ''; //string representation of one <result/> element
var results =  <results/> ; // will hold one batch message
var currentAggregation = ''; 
// anything remaining from last call?
var lastResult = $gc('lastReadResult'); // last record read (empty if we start reading) ; WARNING, it's actually a java String, not a js string
if(lastResult != null && (''+lastResult).length>0) {
    // a result was read previously ==> it's part of current "batch" 
    var xmlResult = new XML(lastResult) ;
    currentAggregation = xmlResult[columnName].toString();
// start/continue reading lines
while ((line = reader.readLine()) != null) {
    // ignore first and last <results> elements
    if (line != "<results>" && line != "</results>") {
        line = line.trim();
        result += line;
        // end of a record?
        if (line == '</result>') {
            var xmlResult = new XML(result); // get result as XML
            // same or new batch
            if (currentAggregation != xmlResult[columnName].toString()) {
                if (results == '') {
                    // tricky here, an empty <results/> means that we're in the very 1st iteration : we just have to set currentAggregation for next iterations
                    currentAggregation = xmlResult[columnName].toString();
                } else {
                    // store new result for next batch	                  		
                    $gc('lastReadResult', result);
                    // return this batch
                    return results.toString();
            // same batch : add result
            // reinit for next record
            result = '';	               
// one last batch to return?
return results.toString(); // will return "" if there's nothing remaining
Attached Files
File Type: txt original version.txt (1.8 KB, 3 views)

Last edited by aTom; 03-19-2018 at 12:39 AM.
Reply With Quote