Announcement

Collapse

Mirth Connect 3.12.0 Released!

Mirth Connect 3.12.0 is now available as an appliance update and on our GitHub page. This release includes database performance improvements, improves visual HL7 representation, message pruning, keystore handling, PDF generation, community contributions, and fixes several security vulnerabilities. This release also contains many improvements to commercial extensions. See the release notes for the list of fixes and updates.

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

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

Guidlines in connecting to SQL Server 2005

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

  • Guidlines in connecting to SQL Server 2005

    Hello guys,

    So far I had formulated this solution in connecting to SQL Server 2005 using Mirth. It took me a hard time researching this procedure, and I am glad sharing this with all of you. Just follow and understand the procedure. I hope it helps.

    Before anything, <MID> means <Mirth_Installation_Directory> (e.g. "C:/Mirth")
    Configuring Mirth

    The connector name "SQL Server 2005" must appear on the driver list in the Source and Destination Tab, if you want to use Database Writer or Database Reader. So, you need to configure Mirth.

    [ol] - Open <MID>/conf/custom/dbdrivers.xml, add this XML code in the last line:
    Code:
    <driver class="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="SQL Server 2005" />
    it will look like this:
    Code:
    <drivers >
    	<driver class="sun.jdbc.odbc.JdbcOdbcDriver" name="Sun JDBC-ODBC Bridge" />
    	<driver class="com.mysql.jdbc.Driver" name="MySQL" />
    	<driver class="oracle.jdbc.OracleDriver" name="Oracle 10g Release 2" />
    	<driver class="org.postgresql.Driver" name="PostgreSQL" />
    	<driver class="net.sourceforge.jtds.jdbc.Driver" name="SQL Server/Sybase" />
    	<driver class="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="SQL Server 2005" />
    </drivers>
    [/ol][ol]Next, download the latest Microsoft SQL Server 2005 JDBC Driver from http://msdn.microsoft.com/data/jdbc/. Extract "sqljdbc.jar" file and paste it into "<MID>/lib/custom/". Restart your machine.[/ol]
    Connecting to SQL Server

    [ol]Make sure the Connector Type is "Database Reader" or "Database Writer". Then select "SQL Server 2005" from the driver list. You added this in "dbdrivers.xml"

    In the URL, this line will connect you to your database:

    Code:
    "jdbc:«»sqlserver://ServerName/InstanceName:«»PortNo;databaseName=DbaseName;integratedSecurity=false;"
    This code works SQL Server 2005 Express Edition. Remove the double quotes. I just used it to display the whole URL.
    Set "integratedSecurity=false;" only if you are using SQL Server and Windows Authentication Mode. Then enter database User Name and Password.[/ol]

    And that's it! You need to play around with your SQL queries using this procedure. Try inserting, updating and deleting records to check your Mirth really communicates with your SQL Server 2005. Have fun!

    Post edited by: flpblackforest, at: 03/30/2008 01:53
    Al Hada & Taif Armed Forces Hospital
    Taif Region, Kingdom of Saudi Arabia

    Please help me to create an instant message community by adding me to your Yahoo! Messenger. My YM id is: hl7_alex

  • #2
    Re:Guidlines in connecting to SQL Server 2005

    The drivers that come with Mirth v1.7 works fine with SQL Server 2005. In the drop-down list for database Drivers, just select "SQL Server/Sybase" and you should be good.

    I created a "File Reader - Database Writer" channel in Mirth.

    Destination Tab Settings

    Connector Type: Database Writer
    Driver: SQL Server/Sybase
    URL: jdbc:jtds:sqlserver://IP_ADDRESS:PORT_NUMBER/DATABASE_NAME

    Forum is messing the characters in the URL. It is

    jdbc colon jtds colon sqlserver colon // IP_ADDRESS colon PORT_NUMBER/DATABASE_NAME

    Username:
    Password:
    Use JavaScript: No

    SQL:
    INSERT IGNORE INTO Patient(Account, LastName, FirstName)
    VALUES (${PatientAccount}, ${PatientLastName}, ${PatientFirstName})
    Tried with an ADT A31. I was able to save the data in SQL Server 2005 database.

    Post edited by: nshaik, at: 03/25/2008 10:30

    Comment


    • #3
      Guidlines in connecting to SQL Server 2005 Express

      Thanks for the information nshaik.

      I want to add on this post that the procedure I posted is for Microsoft SQL Server 2005 Express Edition.

      Microsoft SQL Server 2005 and Microsoft SQL Server 2005 Express Edition are different, not only from it's title, but also in building its connection string. The first one doesn't have "Instance Name" which requires in adding "slash" after the ServerName
      Code:
      "jdbc:«»sqlserver://ServerName/InstanceName:«»PortNo;"
      Whenever I use "SQL Server/Sybase" to connect to this type of database, an error appears:

      Code:
      ERROR-406: JDBC Connector error
      ERROR MESSAGE:	Error writing to database: 
      java.sql.SQLException: No suitable driver found for jdbc:«»sqlserver://ServerName/InstanceName:«»PortNo;databaseName=MirthHL7Dbase;integratedSecurity=false;
      Other error message follow...
      and also, based on the entry I made in the <MID>/conf/custom/dbdrivers.xml, there is a big difference in the class name used:

      Code:
      <driver class="net.sourceforge.jtds.jdbc.Driver" name="SQL Server/Sybase" />
      <driver class="com.microsoft.sqlserver.jdbc.SQLServerDriver" name="SQL Server 2005" />
      The second class I entered came directly from Microsoft Website. This concludes that the "SQL Server/Sybase" driver doesn't work with Microsoft SQL Server 2005 Express Edition.

      I researched this method because I don't have access in our Microsoft SQL Server 2005 and I only got Microsoft SQL Server 2005 Express Edition to test Mirth.

      For those who uses Microsoft SQL Server 2005 Express Edition and having trouble in connecting to it via Mirth... TRY THIS ONE! B)

      Post edited by: flpblackforest, at: 03/30/2008 01:47
      Al Hada & Taif Armed Forces Hospital
      Taif Region, Kingdom of Saudi Arabia

      Please help me to create an instant message community by adding me to your Yahoo! Messenger. My YM id is: hl7_alex

      Comment


      • #4
        Re:Guidlines in connecting to SQL Server 2005

        I tried with both SQL Server 2005 (Developer Edition) and SQL Server 2005 Express Edition.

        Both versions of SQL Server 2005 on my box have instance name like:

        ComputerNamebackslashSQL2005 port number 4768
        ComputerNamebackslashSQLEXPRESS port number 2745

        "SQL Server/Sybase" driver works fine with both.

        Can you try connecting to SQL Server 2005 Express using IP address in the URL?

        ]jdbc:jtds:sqlserver://10.100.1.141:2745/TestDB

        2745 is the port number used by SQL Server 2005 Express on my box.
        Refer:

        Microsoft SQL Server Instance not recognized

        http://www.mirthproject.org/index.ph...1&catid=2#1901

        ---------------

        Additional Info:

        In the Microsoft KB article below, refer to the section, "1. Determine the TCP/IP port number of the instance of SQL Server."

        How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by using the client tools in the earlier version of SQL Server
        http://support.microsoft.com/kb/265808

        Hope this helps!

        Post edited by: nshaik, at: 03/31/2008 12:06

        Comment


        • #5
          Re:Guidlines in connecting to SQL Server 2005

          Hi all,

          answering to flpblackforest:

          you can install MS SQL 2005 Express without an instance (called default instance) same than MS SQL 2005 standart edition. You only need to check "show advanced options" or something like this, during Express installation.

          If you need more exact information, do not doubt to reply this message!.

          HTH,

          Ricardo Bernat

          Comment


          • #6
            Re:Guidlines in connecting to SQL Server 2005

            Hello ricber,

            The SQL Server 2005 EE I mentioned is the installer embedded in the Microsoft Visual Studio 2005 Professional Edition, and upgraded with SQL Server 2005 EE with Advanced Services SP2.

            During installation of the service pack, there is an instance name on it by default. I didn't make any changes because it might conflict with SQL Server 2000 PE installed in the same PC.

            I tried "SQL Server/Sybase" driver many times, played with it in different approaches but still the exception "No suitable driver found for jdbc:sqlserver://ServerName/InstanceName:PortNo;" still appears.

            If it's not too much to ask, kindly send to me the channel you said works on SQL Server 2005 EE with "SQL Server/Sybase" driver? I need to check what makes me wrong.

            Thanks!
            Al Hada & Taif Armed Forces Hospital
            Taif Region, Kingdom of Saudi Arabia

            Please help me to create an instant message community by adding me to your Yahoo! Messenger. My YM id is: hl7_alex

            Comment


            • #7
              Re:Guidlines in connecting to SQL Server 2005

              Attached zip file has both test channel and create table script. TestSQL2005ChannelAndTableScript.zip (2479 bytes)

              Comment


              • #8
                Re:Guidlines in connecting to SQL Server 2005

                Hi all,

                about flpblackforest comments:

                mmmmm...don't explain me anymore!!! If you are mixing SQL versions on the same machine you can obtain unexpected features, sure!. On the other side, I recommend, if possible, that you migrate to SQL 2005 EE all your databases, you will win speed, fiability and space!.

                This mixture is dangerous, ask to your machine!!!

                HTH,

                Ricard Bernat

                Comment


                • #9
                  Re:Guidlines in connecting to SQL Server 2005 Express

                  I completed all of the steps you mentioned, but am still receiving an error when trying to log in to the administrator.

                  I have chosen sqlserver2005 from the drop down and of course verified the username/password for the db. The DB URL I am using is:

                  jdbc:jtdsqlserver://DB Server IPB Server Port/DB Name

                  Here is the error log:

                  WARN 2008-05-01 13:45:23,609 [SslListener0-1] org.mortbay.jetty.context./: /users:
                  com.webreach.mirth.server.controllers.ControllerEx ception: java.sql.SQLException: No suitable driver found for jdbc:jtdsqlserver://10.64.216.52:8443/Outcomes
                  at com.webreach.mirth.server.controllers.UserControll er.authorizeUser(UserController.java:116)
                  at com.webreach.mirth.server.servlets.UserServlet.log in(UserServlet.java:114)
                  at com.webreach.mirth.server.servlets.UserServlet.doP ost(UserServlet.java:62)
                  at javax.servlet.http.HttpServlet.service(HttpServlet .java:616)
                  at javax.servlet.http.HttpServlet.service(HttpServlet .java:689)
                  at org.mortbay.jetty.servlet.ServletHolder.handle(Ser vletHolder.java:428)
                  at org.mortbay.jetty.servlet.ServletHandler.dispatch( ServletHandler.java:666)
                  at org.mortbay.jetty.servlet.ServletHandler.handle(Se rvletHandler.java:568)
                  at org.mortbay.http.HttpContext.handle(HttpContext.ja va:1530)
                  at org.mortbay.http.HttpContext.handle(HttpContext.ja va:1482)
                  at org.mortbay.http.HttpServer.service(HttpServer.jav a:909)
                  at org.mortbay.http.HttpConnection.service(HttpConnec tion.java:816)
                  at org.mortbay.http.HttpConnection.handleNext(HttpCon nection.java:982)
                  at org.mortbay.http.HttpConnection.handle(HttpConnect ion.java:833)
                  at org.mortbay.http.SocketListener.handleConnection(S ocketListener.java:244)
                  at org.mortbay.util.ThreadedServer.handle(ThreadedSer ver.java:357)
                  at org.mortbay.util.ThreadPool$PoolThread.run(ThreadP ool.java:534)
                  Caused by: java.sql.SQLException: No suitable driver found for jdbc:jtdsqlserver://10.64.216.52:8443/Outcomes
                  at java.sql.DriverManager.getConnection(Unknown Source)
                  at java.sql.DriverManager.getConnection(Unknown Source)
                  at com.ibatis.common.jdbc.SimpleDataSource.popConnect ion(SimpleDataSource.java:580)
                  at com.ibatis.common.jdbc.SimpleDataSource.getConnect ion(SimpleDataSource.java:222)
                  at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTran saction.init(JdbcTransaction.java:48)
                  at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTran saction.getConnection(JdbcTransaction.java:89)
                  at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeQueryForObject(GeneralStatement.j ava:104)
                  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.queryForObject(SqlMapExecutorDelegate.java:566)
                  at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.queryForObject(SqlMapExecutorDelegate.java:541)
                  at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.qu eryForObject(SqlMapSessionImpl.java:106)
                  at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.que ryForObject(SqlMapClientImpl.java:83)
                  at com.webreach.mirth.server.controllers.UserControll er.authorizeUser(UserController.java:107)
                  ... 16 more

                  ANY HELP IS MUCH APRECIATED!!!

                  Comment


                  • #10
                    Re:Guidlines in connecting to SQL Server 2005

                    Hi all,

                    quoting your message I saw:

                    jdbc:jtdsqlserver://10.64.216.52:8443/Outcomes

                    when the correct format for this expression should be:

                    jdbc:jtds:sqlserver://10.64.216.52:8443/Outcomes

                    You should write a : between jtds and sqlserver, this is the reason why!. If this mjesage is not showed right, please use in nirth icon the show manager option to retrieve a fresh connection chain.

                    HTH,

                    Ricard Bernat

                    Post edited by: ricber, at: 05/01/2008 11:32

                    Comment

                    Working...
                    X