Announcement

Collapse

Mirth Connect 4.3.0 Released!

Mirth Connect 4.3.0 is now available as an appliance update and on our GitHub page.

This is a major release containing new features like adding new functionality to the Mirth Connect Setup Wizard, adding the ability for resource and channel-specific classloaders to load child-first or parent-first, and added a default implementation of the getObjectsForSwaggerExamples() method in the ServicePlugin class. This release also contains enhancements for the Mirth Connect Administrator Launcher, the Mirth Connect Docker images, and several bug fixes and security improvements.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

Convert SQL Text data type to string?

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    Convert SQL Text data type to string?

    ============
    Mirth 1.3.1
    Channel - Database Reader to FileWriter
    Outbound ORU message for transcription
    ============

    Backend SQL Server 2000 table has the transcription note stored in Text data type column. Using a SQL Statement to get the
    value.

    How do I get the transcription note in plain text in a JavaScript Transformer step.

    I get this output when I try to print the data:

    =========
    var transInSQLTextType = msg['documenttext'].toString();
    logger.info('Test transInSQLTextType: ' + transInSQLTextType);

    Output:

    outbound-transformation: Test transInSQLTextType: [email protected]
    =========

    Appreciate your help.

    Thanks in advance.
    Tags: None
  • #2
    Re: Convert SQL Text data type to string?

    It looks like documenttext is a CLOB register.

    There are two possible solutions:

    http://www.mirthproject.org/index.php?option=com_loudmouth&task=topic&id=162&Itemid=63

    Comment

    • #3
      Re: Convert SQL Text data type to string?

      I saw that post and did try but the output was blank.

      ========== Here's what I tried earlier

      //using stored procedure in javascript

      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://TestServer:1433/TestDB', 'sa', 'test'
      var expression = 'SELECT DocumentText FROM DocumentList WHERE DocumentID = 1' ;
      var result = dbConn.executeCachedQuery(expression);

      result.next();
      var xray = result.getClob(1);
      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());
      logger.info('Test text is: ' + text);
      logger.info('Test text toString is: ' + text.toString());
      // The data in Text Format
      localMap.put('xray',text);
      ==========

      OUTPUT:

      INFO 2007-01-11 18:07:41,142 [739c7d6b-6e70-4d35-b766-fa217de48b85_destination_1_connector.dispatcher.3] outbound-transformation: Test text is:
      INFO 2007-01-11 18:07:41,142 [739c7d6b-6e70-4d35-b766-fa217de48b85_destination_1_connector.dispatcher.3] outbound-transformation: Test text toString is:

      Comment

      • #4
        Re: Convert SQL Text data type to string?

        I have the same problem with ORACLE.

        When I try the select statement in a transformer, I get a SQLException

        http://www.mirthproject.org/index.php?option=com_loudmouth&task=topic&id=229&Itemid=63

        Comment

        • #5
          Re: Convert SQL Text data type to string?

          I got it to work.

          ===========
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://TestServer:1433/DocumentDB', 'sa', 'test'

          var expression = 'SELECT DocumentText FROM DocumentList WHERE DocumentID = 1' ;
          // DocumentText column is if type "Text" in SQL Server 2000

          var result = dbConn.executeCachedQuery(expression);

          result.next();
          var cl = result.getClob(1);

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

          // We access to stream, as this way we don't have to use the CLOB.length() which is slower...
          var br = new java.io.BufferedReader(cl.getCharacterStream());

          while ((aux = br.readLine())!= null)
          strOut.append(aux);

          globalMap.put('gDocTextString', strOut);
          logger.info('gDocTextString is: ' + globalMap.get('gDocTextString&#039);

          ==========

          The solutions in the following post did not work for me. But the samples were useful and I combined both to get solution above:

          http://www.mirthproject.org/index.php?option=com_loudmouth&task=topic&id=162&Itemid=63


          Thanks to everone for the response!

          Comment

          • #6
            Re: Convert SQL Text data type to string?

            It doesn't work in ORACLE.

            I get the following exception:

            org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Invalid precision value. Cannot be less than zero (91fdb23c-5821-4579-a504-b25ba80cdb91#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.c6._c2(91fdb23c-5821-4579-a504-b25ba80cdb91:4)
            at org.mozilla.javascript.gen.c6.call(91fdb23c-5821-4579-a504-b25ba80cdb91)
            at org.mozilla.javascript.optimizer.OptRuntime.callNa me0(OptRuntime.java:106)
            at org.mozilla.javascript.gen.c6._c0(91fdb23c-5821-4579-a504-b25ba80cdb91:34)
            at org.mozilla.javascript.gen.c6.call(91fdb23c-5821-4579-a504-b25ba80cdb91)
            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.c6.call(91fdb23c-5821-4579-a504-b25ba80cdb91)
            at org.mozilla.javascript.gen.c6.exec(91fdb23c-5821-4579-a504-b25ba80cdb91)
            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.NativeMethodAccessorImpl.invoke0(Nativ e Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n 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

            I think is a ORACLE driver problem.

            Has someone read CLOB fields in ORACLE?

            Comment

            • #7
              Re: Convert SQL Text data type to string?

              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('jdbcracle:thin:@//172.30.250.119:1521/XE'
              var conn = ds.getConnection('HORUS','criminal& #039;
              var stm = conn.prepareStatement('SELECT MENSAJE FROM SIGLO.HL7 WHERE ID='+msg['id'].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+'\r\n'

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

              conn.close();

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

              Comment

              • #8
                Re: Convert SQL Text data type to string?

                Fernando - use localMap rather than globalMap, unless you need that value in other chanels or destinations. LocalMap is much faster.
                Chris Lang

                Comment

                • #9
                  Re: Convert SQL Text data type to string?

                  Hi nshaik,

                  If this is the code you used in the transformer, what did you put in the source tab?
                  I tried to select a TEXT type data in the source tab, and got an error.

                  Thanks,
                  Steph

                  Comment

                  • #10
                    Hi all,

                    I'm trying to extract a PDF file inside a BLOB field in my DB, and would like to do it from a DB Reader.

                    I'm experiencing the same problems as above, but I see this is quite an old post, and the solutions may have changed. I'm taking about

                    http://www.mirthproject.org/community/issues/browse/MIRTH-247


                    where it says

                    Updated resultmap to xml to handle CLOB and BLOB types automatically.
                    .

                    Is it already possible now to extract a BLOB type from the resultmap? How could I deal with that in order to Base64 encapsulate it to attach it in an email?

                    Many thanks for your support,

                    -David

                    Comment

                    Previous template Next
                    Working...
                    X

                    We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, personalize advertising, and to analyze site activity. We may share certain information about our users with our advertising and analytics partners. For additional details, refer to our Privacy Policy.

                    By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also acknowledge that this forum may be hosted outside your country and you consent to the collection, storage, and processing of your data in the country where this forum is hosted.

                    I Agree