web stats
hash map in DB Reader - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2012, 07:16 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default hash map in DB Reader

Hello,

Does someone have an example on using a hash list / map in a database reader and then how to access this in a transformer. I have multiple rows that I want to return.

Here is my attempt so far..

// DATABASE READER
// OMITTING DATABASE QUERY / CONNECTION

var sql = "exec pb_tms_theatre_cancellationsvh '1 Feb 2012','8 Feb 2012',1,1,1";

var result = dbConn.executeCachedQuery(sql);

dbConn.close();

var resultMap = new Array();
while (result.next()) {
row = { "patient" : result.getString(1).toString(),
"ur_number" : result.getString(2).toString()
}

resultMap.push(row);

}

logger.error("first patient is: " + resultMap[0]["patient"].toString());
// this logs the patient name correctly

return resultMap;


Questions...

Is the above approach correct?
Can I generate separate messages per row using this method?
How do I map in the transformer section?

Thanks very much!

Glenn
Reply With Quote
  #2  
Old 04-11-2012, 04:31 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,117
narupley is on a distinguished road
Default

When you use a Database Reader, even in JavaScript mode, the source connector will require a CachedRowSet to be returned. The only exception is when you route or manually process a message through the channel; in that case the message goes directly to the global preprocessor, so it requires a string.

Anyway, if you want to return multiple rows as a single message, it'll be much easier to do so in a JavaScript Reader. Here's an example:

Code:
importPackage(java.sql);
new com.mysql.jdbc.Driver();
var dbConn = DriverManager.getConnection('jdbc:mysql://localhost:3306','user,'pass');
var ps = dbConn.prepareStatement("SELECT TEXT FROM TEST.TEXTTEST");
var rs = ps.executeQuery();
var rsmd = rs.getMetaData();
var list = new java.util.ArrayList();
var mod = 3, ret = mod, msg = <results/>;
while(rs.next()) {
	if (ret == 0) {
		list.add(msg.toXMLString());
		msg = <results/>;
	}
	var result = <result/>;
	for (var i = 1; i <= rsmd.getColumnCount(); i++)
		result[rsmd.getColumnName(i)] = rs.getString(i);
	msg.appendChild(result);
	ret = (ret+1)%mod;
}
list.add(msg.toXMLString());
dbConn.close();
return list;
In that example, I just group together every mod rows into a single message and add it to the List object. Obviously I'm not checking for null values, column types other than strings, etc., so you will want to modify that as you see fit.

Hope that helps!
Reply With Quote
  #3  
Old 04-13-2012, 03:04 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default

thanks so much.. it looks like JdbcMessageAdapter does not like the message in XMLString format? I get this error..

java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Map
at com.mirth.connect.connectors.jdbc.JdbcMessageAdapt er.<init>(JdbcMessageAdapter.java:25)
Reply With Quote
  #4  
Old 04-13-2012, 03:48 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,117
narupley is on a distinguished road
Default

What code are you using?
Reply With Quote
  #5  
Old 04-13-2012, 04:28 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default

Here is what I am using..

Code:
var connUrl =  'jdbc:jtds:sybase://myserver:5100/mydb';
var connUser = 'myuser';
var connPass = 'mypass';
var connDriver = 'net.sourceforge.jtds.jdbc.Driver';

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(connDriver,connUrl,connUser,connPass);

var sql = "exec pb_tms_theatre_cancellationsvh '1 Feb 2012','8 Feb 2012',1,1,1";

var rs = dbConn.executeCachedQuery(sql);
var rsmd = rs.getMetaData();
var list = new java.util.ArrayList();
var mod = 3, ret = mod, msg = <results/>;
while(rs.next()) {
	if (ret == 0) 
	{
		list.add(msg.toXMLString());
		msg = <results/>;
	}
	var result = <result/>;
	for (var i = 1; i <= rsmd.getColumnCount(); i++) 
	{
		result[rsmd.getColumnName(i)] = rs.getString(i);
	}
	msg.appendChild(result);
	ret = (ret+1)%mod;
}

list.add(msg.toXMLString());
logger.error("sending msg: " + list.toString());
dbConn.close();
return list;
Reply With Quote
  #6  
Old 04-13-2012, 04:29 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default

mirth v 2.1.1.5490
Reply With Quote
  #7  
Old 04-13-2012, 04:33 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,117
narupley is on a distinguished road
Default

Do you have a more complete stacktrace? Can you do some echo checking to see exactly where the code fails?
Reply With Quote
  #8  
Old 04-13-2012, 04:38 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default

Here is the full error message. The msg count on the channel is still sitting on zero so I assume it never gets into mirth as a valid msg.

Code:
[2012-04-14 10:33:39,208]  ERROR (com.mirth.connect.connectors.jdbc.JdbcMessageReceiver:194): Error in channel: SVH_TMS_QVIEW1_OUT
java.lang.ClassCastException: java.lang.String cannot be cast to java.util.Map
	at com.mirth.connect.connectors.jdbc.JdbcMessageAdapter.<init>(JdbcMessageAdapter.java:25)
	at sun.reflect.GeneratedConstructorAccessor2462.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at org.mule.util.ClassHelper.instanciateClass(ClassHelper.java:271)
	at org.mule.util.ClassHelper.instanciateClass(ClassHelper.java:278)
	at org.mule.providers.service.ConnectorServiceDescriptor.createMessageAdapter(ConnectorServiceDescriptor.java:270)
	at org.mule.providers.AbstractServiceEnabledConnector.getMessageAdapter(AbstractServiceEnabledConnector.java:149)
	at com.mirth.connect.connectors.jdbc.JdbcMessageReceiver.processMessage(JdbcMessageReceiver.java:177)
	at org.mule.providers.TransactedPollingMessageReceiver$1.doInTransaction(TransactedPollingMessageReceiver.java:98)
	at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:72)
	at org.mule.providers.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:104)
	at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:97)
	at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290)
	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
	at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:575)
	at java.lang.Thread.run(Thread.java:662)
Reply With Quote
  #9  
Old 04-13-2012, 04:41 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,117
narupley is on a distinguished road
Default

That's helpful, but can you do some echo checking to see exactly where the code fails?
Reply With Quote
  #10  
Old 04-13-2012, 04:46 PM
glenn71 glenn71 is offline
OBX.3 Kenobi
 
Join Date: Nov 2010
Location: Sydney Australia
Posts: 129
glenn71 is on a distinguished road
Default

It fails on "return list" the logger just before return list shows the message contents.. here is an abbreviated & anon version:

Code:
[<results><result><patient>BARRY,MR NOBODY</patient><ur_number>9997757</ur_number><nmname>SMITH</nmname><cancellation_reason>No ITU Bed</cancellation_reason><category>Ward</category><oper_date>01/02/2012</oper_date><specialty>CARDIOTHORACIC SURGERY</specialty><planned_adition_type>TCA</planned_adition_type><tbtheatrebkid>108315</tbtheatrebkid><asallocsessid>83441</asallocsessid><cancellation_date>01/02/2012</cancellation_date><rhpatclassifcd>DOS</rhpatclassifcd><booked_cases>14</booked_cases><day_only_booked_cases>8</day_only_booked_cases><dpc_cancel_tickbox>null</dpc_cancel_tickbox><admiss_date>null</admiss_date><cancelled_by>Hanna Barbara</cancelled_by><amo_code>DHI2</amo_code></result><result><patient>HILDA,MS NOBODY</patient><ur_number>9997961</ur_number><nmname>SMITH</nmname><cancellation_reason>Patient Refused Surgery</cancellation_reason><category>Patient</category><oper_date>01/02/2012</oper_date><specialty>UROLOGY</specialty><planned_adition_type>TCA</planned_adition_type><tbtheatrebkid>108745</tbtheatrebkid><asallocsessid>81332</asallocsessid><cancellation_date>01/02/2012</cancellation_date><rhpatclassifcd>DS</rhpatclassifcd><booked_cases>14</booked_cases><day_only_booked_cases>8</day_only_booked_cases><dpc_cancel_tickbox>null</dpc_cancel_tickbox><admiss_date>null</admiss_date><cancelled_by>Hanna Barbara</cancelled_by><amo_code>KOOR</amo_code></result></results>]
Reply With Quote
Reply

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


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