Announcement

Collapse
No announcement yet.

Aggregator option in 3.5

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

  • Aggregator option in 3.5

    Has anyone tried using the new aggregator option under the DB reader in 3.5 to turn the results into a CSV file?

    Thank you,
    Matt

    Mirth 3.5.0.8232

  • #2
    I am hoping to work on this next week. I have a few channels that I have not yet made it to production and will be a good fit for this feature.
    Best,

    Kirby

    Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

    Appliance Version 3.11.4
    Mirth Connect Version 3.8.0
    Java Version 1.6.0_45-b06
    Java (64 bit) Version 1.6.0_45-b06
    Java 7 (64 bit) Version 1.7.0_151-b15
    Java 8 (64 bit) Version 1.8.0_181-b13
    PostgreSQL Version 9.6.8

    Comment


    • #3
      Have you had the opportunity to look into this?

      Thanks - Matt

      Comment


      • #4
        Here is what I came up with for mapping the database results to a csv file. I set this to increment the column based on 'z' because nobody deserves to have to type them all out - especially when there are 44!

        var length = msg['result'].length();
        var x = 0;
        var z = 3;

        for (var i = 0; i < length; i++)

        {

        tmp['row'][x] = new XML("<row/>");
        tmp['column'][z] = new XML("<column/>");

        tmp['row'][x]['column'][z] = '"'+msg['result'][i]['customfld1'].toString()+'"';
        tmp['row'][x]['column'][z] = '"'+msg['result'][i]['customfld2'].toString()+'"';
        tmp['row'][x]['column'][z] = '"'+msg['result'][i]['customfld3'].toString()+'"';

        x++;

        }

        Comment


        • #5
          I just re-wrote one of my channels to see how I could get the aggregator working. (see attached channel)

          This channel runs a DB query, loops through the results and creates a csv, which it saves to a file as well as sends to an email.

          I probably should have created the CSV by creating the XML directly instead of converting back and forth but I did not realize that until I was done.
          Attached Files

          Comment


          • #6
            Thanks for sharing. I am hoping to get to test this functionality this week.
            Best,

            Kirby

            Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

            Appliance Version 3.11.4
            Mirth Connect Version 3.8.0
            Java Version 1.6.0_45-b06
            Java (64 bit) Version 1.6.0_45-b06
            Java 7 (64 bit) Version 1.7.0_151-b15
            Java 8 (64 bit) Version 1.8.0_181-b13
            PostgreSQL Version 9.6.8

            Comment


            • #7
              I just re-wrote the source transformer to write directly to XML instead of converting from XML->CSV ->XML ->CSV like the channel I posted. In doing so I noticed a bug that is present in both versions that I am not sure how I will get around without specifiying the column names directly in the transformer. I was trying to make this transformer something generic where you could have any SQL query emailed to you via CSV without changing anything except for the SQL script. Unfortunately, it appears that the source connector leaves out columns from the results if the value is null. If you write your SQL query such that it will never return a null result it will never be an issue but if not your columns in the CSV will not line up.

              Here is the code that I used to rewrite it without first convertint to a CSV:

              Code:
              var rownum = 0;
              
              var tmp = new XML('<delimited></delimited>');
              
              for each (result in msg.result){
              
              	var colnum = 0;
              	if (rownum == 0){
              		tmp.row += <row/>;
              		for each (column in result.*){
              			tmp['row'][0].appendChild(<column>{column.name()}</column>);
              			colnum++;
              		}
              		rownum++;
              	}
              	colnum = 0
              	tmp.row += <row/>;
              	for each (column in result.*){		
              		tmp['row'][rownum].appendChild(<column>{column.toString()}</column>);
              		colnum++;
              	}
              	rownum++;
              }
              msg = tmp

              Comment


              • #8
                Apparently I way overthought the source transformer. You can create a CSV from a DB query in just 3 lines (if you don't need the header).

                Code:
                for each (result in msg.result){
                	result.setLocalName("row");
                
                }
                This still has column alignment problems if you have any null values in the DB.

                Comment


                • #9
                  If your creating a CSV, you can put the header row in the outbound message template.
                  Best,

                  Kirby

                  Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

                  Appliance Version 3.11.4
                  Mirth Connect Version 3.8.0
                  Java Version 1.6.0_45-b06
                  Java (64 bit) Version 1.6.0_45-b06
                  Java 7 (64 bit) Version 1.7.0_151-b15
                  Java 8 (64 bit) Version 1.8.0_181-b13
                  PostgreSQL Version 9.6.8

                  Comment


                  • #10
                    were you able to get the issue with NULL rows resolved?
                    Best,

                    Kirby

                    Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

                    Appliance Version 3.11.4
                    Mirth Connect Version 3.8.0
                    Java Version 1.6.0_45-b06
                    Java (64 bit) Version 1.6.0_45-b06
                    Java 7 (64 bit) Version 1.7.0_151-b15
                    Java 8 (64 bit) Version 1.8.0_181-b13
                    PostgreSQL Version 9.6.8

                    Comment

                    Working...
                    X