web stats
Database to CSV HOWTO - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 09-08-2014, 07:02 AM
rdejournett rdejournett is offline
OBX.2 Kenobi
 
Join Date: Jan 2013
Posts: 99
rdejournett is on a distinguished road
Default Database to CSV HOWTO

This was a bit of detective work so I thought i'd share my solution, which is mostly just cobbled together posts from Naru. I had a task of generating a CSV file from a postgres database table, very simple data flow. We want to email this report to various people. One issue is that I wanted all rows returned instead of just a row at a time, and I wanted to ensure each destination message contained the entire dataset in CSV format.

The channel source is Javascript, and the following code goes there.


var dbConn = DriverManager.getConnection('jdbcostgresql://localhost/db','user','pw');

importPackage(java.sql);
new com.mysql.jdbc.Driver();
var ps = dbConn.prepareStatement("SELECT * from Table");
var rs = ps.executeQuery();
var rsmd = rs.getMetaData();
var msg = <results/>;


while(rs.next()) {
var result = <result/>;
for (var i = 1; i <= rsmd.getColumnCount(); i++)
result[rsmd.getColumnName(i)] = rs.getString(i);
msg.appendChild(result);
}

dbConn.close();

return msg.toXMLString();


Then in the source transform, the following code goes there.

tmp = <delimited/>;

var header = <row/>;
var i = 1;
header.appendChild(col('id',i++));
header.appendChild(col('CreateDTTM',i++));
header.appendChild(col('Patient Name',i++));
header.appendChild(col('MRN',i++));
header.appendChild(col('Practice ID',i++));
header.appendChild(col('Member ID',i++));
header.appendChild(col('Practice Name',i++));
header.appendChild(col('Success',i++));
header.appendChild(col('Message',i++));


tmp.appendChild(header);

for each (results in msg) {

for each (result in results.children()) {
var row = <row/>;
var i = 1;

row.appendChild(col(result.id.toString(),i++));
row.appendChild(col(result.dttm.toString(),i++));
row.appendChild(col(result.patname.toString(),i++) );
row.appendChild(col(result.mrn.toString(),i++));
row.appendChild(col(result.practiceid.toString(),i ++));
row.appendChild(col(result.memberid.toString(),i++ ));
row.appendChild(col(result.practicename.toString() ,i++));
row.appendChild(col(result.success.toString(),i++) );
row.appendChild(col(result.message.toString(),i++) );
tmp.appendChild(row);
}
}

var deserializationProperties = SerializerFactory.getDefaultDeserializationPropert ies('DELIMITED');
var CSV = SerializerFactory.getSerializer('DELIMITED', null, deserializationProperties).fromXML(tmp);

channelMap.put ('CSV', CSV);

function col(str,num) {
return new XML('<column'+num+'>'+escape(str)+'</column'+num+'>');

}


function escape(str){
return str
.replace(/&/g,'&amp;')
.replace(/"/g,'&quot;')
.replace(/'/g,'&apos;')
.replace(/</g,'&lt;')
.replace(/>/g,'&gt;');
};
Reply With Quote
 

Tags
csv, database

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 01:59 PM.


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