web stats
Aggregator option in 3.5 - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-05-2017, 09:47 AM
mhaase mhaase is offline
OBX.2 Kenobi
 
Join Date: Dec 2015
Posts: 57
mhaase is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-05-2017, 06:39 PM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

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.
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #3  
Old 05-11-2017, 01:18 PM
mhaase mhaase is offline
OBX.2 Kenobi
 
Join Date: Dec 2015
Posts: 57
mhaase is on a distinguished road
Default

Have you had the opportunity to look into this?

Thanks - Matt
Reply With Quote
  #4  
Old 06-09-2017, 11:39 AM
mhaase mhaase is offline
OBX.2 Kenobi
 
Join Date: Dec 2015
Posts: 57
mhaase is on a distinguished road
Default

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++;

}
Reply With Quote
  #5  
Old 06-13-2017, 06:12 AM
ryadical ryadical is offline
OBX.1 Kenobi
 
Join Date: Aug 2016
Posts: 33
ryadical is on a distinguished road
Default

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
File Type: xml Test Aggregate.xml (23.4 KB, 16 views)
Reply With Quote
  #6  
Old 06-13-2017, 08:34 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

Thanks for sharing. I am hoping to get to test this functionality this week.
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #7  
Old 06-13-2017, 10:22 AM
ryadical ryadical is offline
OBX.1 Kenobi
 
Join Date: Aug 2016
Posts: 33
ryadical is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 06-13-2017, 11:55 AM
ryadical ryadical is offline
OBX.1 Kenobi
 
Join Date: Aug 2016
Posts: 33
ryadical is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 06-13-2017, 01:16 PM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

If your creating a CSV, you can put the header row in the outbound message template.
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #10  
Old 09-07-2017, 02:30 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

were you able to get the issue with NULL rows resolved?
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
Reply

Tags
csv, database polling, xml

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 06:03 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation