web stats
Mirth Community - View Single Post - Database to CSV HOWTO
View Single Post
  #2  
Old 10-06-2018, 09: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