Announcement

Collapse
No announcement yet.

Convert SQL Text data type to string?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #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.ph...=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.ph...=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.ph...=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/communit...owse/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

                    Working...
                    X