web stats
Database to CSV HOWTO - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 09-08-2014, 08: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
  #2  
Old 10-06-2018, 10:32 AM
ezinteropsolutions ezinteropsolutions is offline
What's HL7?
 
Join Date: Oct 2018
Posts: 3
ezinteropsolutions is on a distinguished road
Default 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)
Reply With Quote
  #3  
Old 10-08-2018, 07:08 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 1,006
agermano is on a distinguished road
Default

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.
Reply With Quote
Reply

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 10:49 PM.


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