web stats
Mirth Community - View Single Post - Database to CSV HOWTO
View Single Post
  #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