Announcement

Collapse
No announcement yet.

How to get data from database

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

  • How to get data from database

    Hi everyone!
    I'm using Database Reader as my connector type. The question I came up to is how will I get the data found on the database? Can you please give some script on how to do so?? By the way, I'm using SqlServer as my driver.

    Thanks in advance!
    Tonet

  • #2
    Re:How to get data from database

    When you enter a select statement in the SQL area of the source-tab you will get a XML structure for every matching record. These XML structures will be the incoming messages for a destination.

    Comment


    • #3
      Re:How to get data from database

      Hi! Thank you for replying!

      Mirth seems like declaring them as variables. So how do I map these values in the transformer? I can't see the xml here. When I deploy channels, I keep having this error:

      org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to
      reconnect receiver on endpoint "jdbc://query"

      By the way, should I put a javascript like this:

      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://saleslip2k:1443/mirthdb','sa','cardtech');
      var expression = "SELECT patient_id from patient_info";

      var result = dbConn.executeCachedQuery("expression");


      dbConn.close();
      return result;


      Or just a simple sql will do? For example:

      SELECT patient_id FROM patient_info


      Thanks again! I hope you could help me out..:side:

      Tonet =)

      Post edited by: shifting_18, at: 05/03/2007 02:19
      Tonet

      Comment


      • #4
        Re:How to get data from database

        I followed what you said entering the sql I posted earlier.
        i used
        tmp['PID']['PID.1'] as message segment
        ${patient_id} as mapping value

        but I have this error:

        org.mule.config.ConfigurationException: Failed to parse configuration resource "C:\\Program Files\\Mirth\\conf\\mule-config.xml"
        Caused by: org.mule.umo.lifecycle.InitialisationException: Initialisation Failure: missing ) after argument list (b9b0d38f-ace1-4a7c-99bc-7e70e3b23c47#6)
        Tonet

        Comment


        • #5
          Re:How to get data from database

          In the incoming Data tab on the destination you should paste an example of the XML structure that is returned. You could find one thru the
          Dashboard/View messages if the query succeeded. You can map any part of the XML-values to a name although that is not needed. You can also use the tmp['xmltag'] structure directly in your transformer javascript.

          Use Validate java script to check if you have a syntax error in your script.

          You don;t need a javascript to query the database. On the source tab of the Database Reader you can configure the minumum that is needed.

          Post edited by: huubvandemortel, at: 05/03/2007 03:21

          Comment


          • #6
            Re:How to get data from database

            HI! I got this problem here. I still cannot start my channel. The error is said below. Can you please give me a sample transformer javascript.. please?I am now confused on what to use.

            I used this select stmt now:
            SELECT patient_id from patient_info
            FOR XML AUTO

            I was surprised to see an xml on the incoming message on the transformer but now, I don't know how to type the javascript..Can't I make use of Message builder? Hehe..Thanks!

            Thank you so much!!

            Post edited by: shifting_18, at: 05/03/2007 19:20
            Tonet

            Comment


            • #7
              Re:How to get data from database

              You can use the incoming and outgoing templates. Paste the XML to the incoming and a HL7 message to the outgoing data.
              Transorm like

              tmp['PID']['PID.5']['PID.5.1'] = msg['PatientName'].toString() PatientName is a Tag in your incoming XML (a column in your database)

              Comment


              • #8
                Re:How to get data from database

                hi! i tried what you said but I can't see the results. but I really really am confused with the error.

                org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"

                I can't start EVEN start my channel..gosh!
                Tonet

                Comment


                • #9
                  Re:How to get data from database

                  Is'nt there also a login failed message in the trace ?

                  I had that problem trying to login on a domain account. I could not make that work. Creating a new local SQL Server account ( not using Windows Authemtication) made the login work.

                  Stil have no idee why a windows domain account does not work.

                  Post edited by: huubvandemortel, at: 05/06/2007 23:33

                  Comment


                  • #10
                    Re:How to get data from database

                    Hi!
                    The complete error is below. I can't seem to find a login failure. It was pretty much concern with the connection I think. I've checked the parameter and it's correct.
                    It worked when I previously used a file to db writer..

                    org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"
                    at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:34 )
                    at org.mule.providers.AbstractConnectionStrategy.conn ect(AbstractConnectionStrategy.java:67)
                    at org.mule.providers.AbstractMessageReceiver.start(A bstractMessageReceiver.java:396)
                    at org.mule.providers.AbstractConnector.registerListe ner(AbstractConnector.java:518)
                    at org.mule.impl.model.AbstractModel.registerListener s(AbstractModel.java:221)
                    at org.mule.impl.model.AbstractModel.start(AbstractMo del.java:353)
                    at org.mule.MuleManager.start(MuleManager.java:730)
                    at org.mule.config.builders.MuleXmlConfigurationBuild er.configure(MuleXmlConfigurationBuilder.java:207)
                    at org.mule.config.builders.MuleXmlConfigurationBuild er.configure(MuleXmlConfigurationBuilder.java:194)
                    at com.webreach.mirth.server.Mirth.startMule(Mirth.ja va:163)
                    at com.webreach.mirth.server.Mirth.restartMule(Mirth. java:145)
                    at com.webreach.mirth.server.Mirth.run(Mirth.java:101 )
                    Caused by: org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"
                    at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:34 )
                    at org.mule.providers.AbstractConnectionStrategy.conn ect(AbstractConnectionStrategy.java:67)
                    at org.mule.providers.AbstractMessageReceiver.connect (AbstractMessageReceiver.java:353)
                    at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:32 )
                    ... 11 more
                    Caused by: org.mule.providers.ConnectException: Initialisation Failure: Network error IOException: Connection refused: connect
                    at com.webreach.mirth.server.mule.providers.jdbc.Jdbc MessageReceiver.doConnect(JdbcMessageReceiver.java :91)
                    at org.mule.providers.AbstractMessageReceiver.connect (AbstractMessageReceiver.java:360)
                    at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:32 )
                    ... 14 more
                    Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect
                    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(C onnectionJDBC2.java:372)
                    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(C onnectionJDBC3.java:50)
                    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.ja va:178)
                    at java.sql.DriverManager.getConnection(Unknown Source)
                    at java.sql.DriverManager.getConnection(Unknown Source)
                    at com.webreach.mirth.server.mule.providers.jdbc.Jdbc Connector.getConnection(JdbcConnector.java:452)
                    at com.webreach.mirth.server.mule.providers.jdbc.Jdbc MessageReceiver.doConnect(JdbcMessageReceiver.java :89)
                    ... 16 more
                    Caused by: java.net.ConnectException: Connection refused: connect
                    at java.net.PlainSocketImpl.socketConnect(Native Method)
                    at java.net.PlainSocketImpl.doConnect(Unknown Source)
                    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
                    at java.net.PlainSocketImpl.connect(Unknown Source)
                    at java.net.SocksSocketImpl.connect(Unknown Source)
                    at java.net.Socket.connect(Unknown Source)
                    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 net.sourceforge.jtds.jdbc.SharedSocket.createSocke tForJDBC3(SharedSocket.java:289)
                    at net.sourceforge.jtds.jdbc.SharedSocket.<init>(Shar edSocket.java:250)
                    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(C onnectionJDBC2.java:297)
                    ... 22 more

                    Post edited by: shifting_18, at: 05/07/2007 18:34
                    Tonet

                    Comment


                    • #11
                      Re:How to get data from database

                      Are you sure someone is listning to the port on your database connector
                      I get the same messages when I change my port to a number no one is listning to ...

                      Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

                      Comment


                      • #12
                        Re:How to get data from database

                        Are you sure someone is listning to the port on your database connector
                        I get the same messages when I change my port to a number no one is listning to ...

                        Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

                        Comment


                        • #13
                          Re:How to get data from database

                          Are you sure someone is listning to the port on your database connector
                          I get the same messages when I change my port to a number no one is listning to ...

                          Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

                          Comment


                          • #14
                            Re:How to get data from database

                            Are you sure someone is listning to the port on your database connector
                            I get the same messages when I change my port to a number no one is listning to ...

                            Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

                            Comment


                            • #15
                              Re:How to get data from database

                              hi!!
                              Thank you very much for your advices. Now it's working! I'm very happy on it! I'll start now with learning triggers. By the way, do have any idea on how to manipulate the HL7 in Javascript? I made a thread entitled "Javascript help!" I really wish you could help me out. Many thanks to you again!!1
                              Tonet

                              Comment

                              Working...
                              X