ER7 message from DataBase to SOAP sender

[fernando.ramirez]

I have my ER7 HL7 messages in a database with id,status and message fields.
I want to send these messages via SOAP sender in a XML HL7 format.

I have the ER7 message like a CLOB oracle field.
How do I read this field? I try indications from another posts but I don't how the class of msg['message'] field.
This is my transformer.

<com.webreach.mirth.model.Transformer>
<steps>
<com.webreach.mirth.model.Step>
<sequenceNumber>0</sequenceNumber>
<name>CLOB</name>
<script>var strOut = new Packages.java.lang.StringBuffer();&#x0D;
var aux = new Packages.java.lang.String();&#x0D;&#x0D;&#x0D;

var br = new Packages.java.io.BufferedReader(msg[&apos;mensaje&apos;].getCharacterStream() &#x0D;
while ((aux=br.readLine())!=null) strOut.append(aux);
localMap.put(&apos;msgDB&apos;,strOut.toString()); </script>
<type>JavaScript</type>
<data class="map">
<entry>
<string>Script</string>
<string>var strOut = new Packages.java.lang.StringBuffer();&#x0D;
var aux = new Packages.java.lang.String();&#x0D;&#x0D;&#x0D;

var br = new Packages.java.io.BufferedReader(msg[&apos;mensaje&apos;].getCharacterStream() &#x0D;
while ((aux=br.readLine())!=null) strOut.append(aux);
localMap.put(&apos;msgDB&apos;,strOut.toString()); </string>
</entry>
</data>
</com.webreach.mirth.model.Step>
<com.webreach.mirth.model.Step>
<sequenceNumber>1</sequenceNumber>
<name>tmp[&apos;MSG&apos;]</name>
<script>tmp[&apos;MSG&apos;] = localMap.get(&apos;msgDB&apos;</script>
<type>HL7 Message Builder</type>
<data class="map">
<entry>
<string>Mapping</string>
<string>localMap.get(&apos;msgDB&apos</string>
</entry>
<entry>
<string>Variable</string>
<string>tmp[&apos;MSG&apos;]</string>
</entry>
</data>
</com.webreach.mirth.model.Step>
</steps>
<template></template>
</com.webreach.mirth.model.Transformer>


Any idea or example document,

[chrisl]

Re: ER7 message from DataBase to SOAP sender

What does your SQL statement look like?

[fernando.ramirez]

Re: ER7 message from DataBase to SOAP sender

SELECT ID,MENSAJE FROM SIGLO.HL7 WHERE ESTADO=&#039;P&#039; AND SENDER=&#039;MYSENDER&#039;

[fernando.ramirez]

Re: ER7 message from DataBase to SOAP sender

I am unable to read ORACLE CBLOB field.

In my raw Data appears a value of kind

<?xml version="1.0" encoding="UTF-8"?>
<result><mensaje>[email protected]</mensaje><id>1</id></result>

and I don&#039;t know the type of CLOB field "mensaje". I tried cast to oracle.sql.CLOB but doesn`t work.

In another topic I read something that the following (access in transformer to the data with a database connection).

var dbConn = DatabaseConnectionFactory.createDatabaseConnection (&#039;oracle.jdbc.driver.OracleDriver&#039;, &#039;jdbcracle:thin:@//XXX.XXX.XXX.XXX:1521/XE&#039;, &#039;USER&#039;, &#039;password&#039;
var result = (Packages.java.sql.ResultSet) (dbConn.executeCachedQuery(&#039;SELECT MENSAJE FROM SIGLO.HL7 WHERE ID=1&#039);
result.next();
var cl = result.getCLOB(1);

.......

dbConn.close();


but I get the following error


org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid precision value. Cannot be less than zero (d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b#4)
at org.mozilla.javascript.Context.throwAsScriptRuntim eEx(Context.java:1693)
at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:157)
at org.mozilla.javascript.NativeJavaMethod.call(Nativ eJavaMethod.java:201)
at org.mozilla.javascript.optimizer.OptRuntime.call1( OptRuntime.java:64)
at org.mozilla.javascript.gen.c107._c2(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b:4)
at org.mozilla.javascript.gen.c107.call(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b)
at org.mozilla.javascript.optimizer.OptRuntime.callNa me0(OptRuntime.java:106)
at org.mozilla.javascript.gen.c107._c0(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b:22)
at org.mozilla.javascript.gen.c107.call(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b)
at org.mozilla.javascript.ContextFactory.doTopCall(Co ntextFactory.java:337)
at org.mozilla.javascript.ScriptRuntime.doTopCall(Scr iptRuntime.java:2755)
at org.mozilla.javascript.gen.c107.call(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b)
at org.mozilla.javascript.gen.c107.exec(d9ca7f52-b69c-4d6b-b412-f9456f4d1c0b)
at com.webreach.mirth.server.mule.transformers.JavaSc riptTransformer.evaluateOutboundTransformerScript( JavaScriptTransformer.java:307)
at com.webreach.mirth.server.mule.transformers.JavaSc riptTransformer.doTransform(JavaScriptTransformer. java:174)
at org.mule.transformers.AbstractTransformer.transfor m(AbstractTransformer.java:197)
at org.mule.impl.MuleEvent.getTransformedMessage(Mule Event.java:251)
at org.mule.providers.soap.axis.AxisMessageDispatcher .doDispatch(AxisMessageDispatcher.java:194)
at org.mule.providers.AbstractMessageDispatcher$Worke r.run(AbstractMessageDispatcher.java:257)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.runTask(ThreadPoolExecutor. java:650)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :675)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Invalid precision value. Cannot be less than zero
at javax.sql.rowset.RowSetMetaDataImpl.setPrecision(U nknown Source)
at com.sun.rowset.CachedRowSetImpl.initMetaData(Unkno wn Source)
at com.sun.rowset.CachedRowSetImpl.populate(Unknown Source)
at com.webreach.mirth.server.util.DatabaseConnection. executeCachedQuery(DatabaseConnection.java:114)
at sun.reflect.GeneratedMethodAccessor60.invoke(Unkno wn Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:142)
... 21 more

It seems a problem with ORACLE driver.

Any ideas?

[fernando.ramirez]

Re: ER7 message from DataBase to SOAP sender

With ORACLE, it works with this transformer.
But I would like it works without reading another time the database. It is more efficent.


===============================

var ds= new Packages.oracle.jdbc.pool.OracleDataSource();
ds.setURL(&#039;jdbcracle:thin:@//172.30.250.119:1521/XE&#039;
var conn = ds.getConnection(&#039;HORUS&#039;,&#039;criminal& #039;
var stm = conn.prepareStatement(&#039;SELECT MENSAJE FROM SIGLO.HL7 WHERE ID=&#039;+msg[&#039;id&#039;].toString());
var rs = stm.executeQuery();
rs.next();
var cl = rs.getClob(1);

var strOut = new java.lang.StringBuffer();
var aux = new java.lang.String();


var br = new java.io.BufferedReader(cl.getCharacterStream());

while ((aux = br.readLine())!= null) strOut.append(aux+&#039;\r\n&#039;

globalMap.put(&#039;msgDB&#039;, strOut.toString());
logger.info(&#039;msgDB: &#039; + globalMap.get(&#039;msgDB&#039);

conn.close();

=================