• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6026
  • Last Modified:

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

0
mwolfe83
Asked:
mwolfe83
  • 4
1 Solution
 
sweetfa2Commented:
tmp[blah] =  result.getInt(1);
0
 
mwolfe83Author Commented:
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

0
 
mwolfe83Author Commented:
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

0
 
mwolfe83Author Commented:
Fixed it.  Man, that was rough.  I guess it would help if I knew ANY programming...


var accession;
var rptBody; //holds report text from PACS_T_REPORT_TEXT.REPORT_TEXT
accession = msg['tag00080050']['@tag'].toString();

//connect to DB
var dbConn = DatabaseConnectionFactory.createDatabaseConnection
('oracle.jdbc.driver.OracleDriver','jdbcracle:th in:@10.0.1.13:1521:RAYPAX1', 'pacs', 'pacs');

//query for report body
var SQL = "select REPORT_TEXT from PACS_T_REPORT_TEXT where EXAM_KEY= '" + accession + "'";
var result = dbConn.executeCachedQuery(SQL);
result.first();
try
{
var rptBody = result.getClob("REPORT_TEXT");
logger.error(rptBody);
var clobLength = rptBody.length();
var rptBodyFull = rptBody.getSubString(1,clobLength)
logger.error(rptBodyFull);


} catch (e) {
rptBody = "Exception retreiving report. No Report Found";
}
channelMap.put('rptBody',rptBody);

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


dbConn.close();

Open in new window

0
 
mwolfe83Author Commented:
I figured it out on my own after living on the mirthcorp forum.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now