Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   Database to CSV HOWTO (http://www.mirthcorp.com/community/forums/showthread.php?t=11550)

rdejournett 09-08-2014 08:02 AM

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('jdbc:postgresql://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;');
};

ezinteropsolutions 10-06-2018 10:32 AM

still struggling to get it to work
 
I am a novice when it comes to scripting and mirth.

I am trying to create a channel which will read from a database and will write to a csv file.

So far I have done this

created a channel with source as Database Reader.
Added the below javascript on the source screen


-----------------------------------------------
var dbConn = DriverManager.getConnection(jdbc:mysql://localhost:3306/chinook','root','test');

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


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

dbConn.close();

---------------------------------------------------------------


then I added a step and have added the below script


-----------------------------------------------------------------

tmp = <delimited/>;

var header = <row/>;
var i = 1;
header.appendChild(col('docid',i++));
header.appendChild(col('encid',i++));
header.appendChild(col('filename',i++));
header.appendChild(col('customname',i++));
header.appendChild(col('scandate',i++));
header.appendChild(col('description',i++));
header.appendChild(col('patientid',i++));
header.appendChild(col('scannedby',i++));
header.appendChild(col('reviewedby',i++));
header.appendChild(col('dirpath',i++));
header.appendChild(col('modifieddatetime',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.docid.toString(),i++));
row.appendChild(col(result.encidid.toString(),i++) );
row.appendChild(col(result.filename.toString(),i++ ));
row.appendChild(col(result.customname.toString(),i ++) );
row.appendChild(col(result.scandate.toString(),i++ ));
row.appendChild(col(result.description.toString(), i ++));
row.appendChild(col(result.patientid.toString(),i+ + ));
row.appendChild(col(result.scannedby.toString() ,i++));
row.appendChild(col(result.reviewedby.toString(),i ++) );
row.appendChild(col(result.dirpath.toString(),i++) );
row.appendChild(col(result.modifieddatetime.toStri ng(),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;');
};

------------------------------------------------------------------------

On destination tab, I have set is as filewriterand template is set as

${CSV}


I am getting below error , not sure what is wrong here.


[2018-10-06 11:02:00,039] ERROR (com.mirth.connect.connectors.jdbc.DatabaseReceive rScript:123): An error occurred while polling for messages, retrying after 10000 ms...
com.mirth.connect.connectors.jdbc.DatabaseReceiver Exception: Unrecognized value returned from script in channel "DB2csv", expected ResultSet or List<Map<String, Object>>: org.mozilla.javascript.Undefined@2e5a03a0
at com.mirth.connect.connectors.jdbc.DatabaseReceiver Script.poll(DatabaseReceiverScript.java:119)
at com.mirth.connect.connectors.jdbc.DatabaseReceiver .poll(DatabaseReceiver.java:111)
at com.mirth.connect.donkey.server.channel.PollConnec torJob.execute(PollConnectorJob.java:49)
at org.quartz.core.JobRunShell.run(JobRunShell.java:2 13)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run (SimpleThreadPool.java:557)

agermano 10-08-2018 07:08 AM

The error is because the original solution used a javascript reader, not a database reader. However, that's making things way more complicated than they need to be if you are on mirth version 3.5 or higher that has an option for aggregate results. Depending on what you need to do with the results, there is still probably a better method for versions lower than 3.5.


All times are GMT -8. The time now is 02:36 PM.

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