Link to home
Start Free TrialLog in
Avatar of mwolfe83
mwolfe83

asked on

query mssql Database with mirth

I'm not very good at the programming aspect of my job.  Can someone look at this code and tell me where I'm screwing up?

I think I'm screwing up at tmp[blahblahblah] = result(1);

I know the field i'm grabbing from is a text field..
//DatabaseConnection
//see http://svn.sourceforge.net/viewvc/mirth/trunk/server/src/com/webreach/mirth/server/util/DatabaseConnection.java?view=markup 
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://10.0.0.55:1433/raypax','username','password');


//the query
var AccNum = msg['tag00080050']['@tag'].toString();
var expression = "SELECT REPORT_TEXT FROM PACS_T_REPORT_TEXT WHERE EXAM_KEY = '" + AccNum + "';";  


//CachedRowSetImpl
//see http://developers.sun.com/prodtech/javatools/jscreator/reference/docs/apis/rowset/com/sun/rowset/CachedRowSetImpl.html
var result = dbConn.executeCachedQuery(expression); 

//go to the first result
result.next();
//get the value from the first colum as an integer

tmp['OBX']['OBX.5']['OBX.5.1'] = result(1);


result.close(); 
dbConn.close();

Open in new window

Avatar of sweetfa2
sweetfa2
Flag of Australia image
tmp[blah] =  result.getInt(1);
Avatar of mwolfe83
mwolfe83

ASKER

Here is the error that it returned:
DETAILS:
Wrapped java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 0

DETAILS:	Wrapped java.sql.SQLException: Invalid column index
	at com.mirth.connect.server.mule.transformers.JavaScriptTransformer.evaluateScript(JavaScriptTransformer.java:451)
	at com.mirth.connect.server.mule.transformers.JavaScriptTransformer.transform(JavaScriptTransformer.java:349)
	at org.mule.transformers.AbstractEventAwareTransformer.doTransform(AbstractEventAwareTransformer.java:48)
	at org.mule.transformers.AbstractTransformer.transform(AbstractTransformer.java:197)
	at org.mule.impl.MuleEvent.getTransformedMessage(MuleEvent.java:251)
	at com.mirth.connect.server.controllers.DefaultMessageObjectController.getMessageObjectFromEvent(DefaultMessageObjectController.java:737)
	at com.mirth.connect.connectors.file.FileMessageDispatcher.doDispatch(FileMessageDispatcher.java:52)
	at com.mirth.connect.connectors.file.FileMessageDispatcher.doSend(FileMessageDispatcher.java:129)
	at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:164)
	at org.mule.impl.MuleSession.sendEvent(MuleSession.java:191)
	at org.mule.impl.MuleSession.sendEvent(MuleSession.java:130)
	at org.mule.routing.outbound.AbstractOutboundRouter.send(AbstractOutboundRouter.java:85)
	at org.mule.routing.outbound.FilteringMulticastingRouter.route(FilteringMulticastingRouter.java:61)
	at org.mule.routing.outbound.OutboundMessageRouter$1.doInTransaction(OutboundMessageRouter.java:78)
	at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:48)
	at org.mule.routing.outbound.OutboundMessageRouter.route(OutboundMessageRouter.java:82)
	at org.mule.impl.model.DefaultMuleProxy.onCall(DefaultMuleProxy.java:247)
	at org.mule.impl.model.seda.SedaComponent.doSend(SedaComponent.java:209)
	at org.mule.impl.model.AbstractComponent.sendEvent(AbstractComponent.java:277)
	at org.mule.impl.MuleSession.sendEvent(MuleSession.java:201)
	at org.mule.routing.inbound.InboundMessageRouter.send(InboundMessageRouter.java:176)
	at org.mule.routing.inbound.InboundMessageRouter.route(InboundMessageRouter.java:143)
	at org.mule.providers.AbstractMessageReceiver$DefaultInternalMessageListener.onMessage(AbstractMessageReceiver.java:487)
	at org.mule.providers.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:266)
	at org.mule.providers.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:225)
	at com.mirth.connect.connectors.dimse.DICOMMessageReceiver$MirthStorageSCP.onCStoreRQ(DICOMMessageReceiver.java:193)
	at com.mirth.connect.connectors.dimse.DICOMMessageReceiver$MirthStorageSCP.cstore(DICOMMessageReceiver.java:211)
	at org.dcm4che2.net.DicomServiceRegistry.process(DicomServiceRegistry.java:238)
	at org.dcm4che2.net.NetworkApplicationEntity.perform(NetworkApplicationEntity.java:1158)
	at org.dcm4che2.net.Association.onDimseRQ(Association.java:971)
	at org.dcm4che2.net.PDUDecoder.decodeDIMSE(PDUDecoder.java:530)
	at org.dcm4che2.net.Association.onPDataTF(Association.java:948)
	at org.dcm4che2.net.State$Sta6.receivedPDataTF(State.java:239)
	at org.dcm4che2.net.Association.receivedPDataTF(Association.java:944)
	at org.dcm4che2.net.PDUDecoder.nextPDU(PDUDecoder.java:231)
	at org.dcm4che2.net.Association.run(Association.java:850)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Invalid column index
	at com.sun.rowset.CachedRowSetImpl.checkIndex(Unknown Source)
	at com.sun.rowset.CachedRowSetImpl.getInt(Unknown Source)
	at sun.reflect.GeneratedMethodAccessor297.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)
	at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:66)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340._c_doTransform_13(32060774-24ea-4354-80c5-4a9ac4ceca47:31)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340.call(32060774-24ea-4354-80c5-4a9ac4ceca47)
	at org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:108)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340._c_script_0(32060774-24ea-4354-80c5-4a9ac4ceca47:42)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340.call(32060774-24ea-4354-80c5-4a9ac4ceca47)
	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3157)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340.call(32060774-24ea-4354-80c5-4a9ac4ceca47)
	at org.mozilla.javascript.gen._32060774_24ea_4354_80c5_4a9ac4ceca47_340.exec(32060774-24ea-4354-80c5-4a9ac4ceca47)
	at com.mirth.connect.server.mule.transformers.JavaScriptTransformer.evaluateScript(JavaScriptTransformer.java:395)
	... 36 more

Open in new window

Avatar of mwolfe83
mwolfe83

ASKER

After looking deeper into the archives of this forum, I found a post that seemed to get me a little closer. Now I am running into a LocalMap.put problem. It says the object is not defined. Any Ideas?
var AccNum = msg['tag00080050']['@tag'].toString();


//using stored procedure in javascript

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://10.0.0.55:1433/raypax', 'username', 'password');
var expression = 'select EXAM_KEY,REPORT_TEXT from PACS_T_REPORT_TEXT where EXAM_KEY= 10131' ;//here REPORT_TEXT is blob column
var result = dbConn.executeCachedQuery(expression);

result.next();
var xray = result.getClob(2);
var strOut = new java.lang.StringBuffer();
var aux = new java.lang.String();

// RTF data conversion

var kit = new Packages.javax.swing.text.rtf.RTFEditorKit()
var doc = new Packages.javax.swing.text.DefaultStyledDocument();
kit.read(xray.getAsciiStream(), doc, 0);

var text=doc.getText(0,doc.getLength());
// The data in Text Format

localMap.put('xray',text);

tmp['OBX']['OBX.5']['OBX.5.1'] = xray
0

dbConn.close();

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mwolfe83
mwolfe83
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mwolfe83
mwolfe83

ASKER

I figured it out on my own after living on the mirthcorp forum.