Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   Mirth and SQL-Server 2014 via jTDS 1.3.1 (http://www.mirthcorp.com/community/forums/showthread.php?t=14761)

clinjohn 06-16-2015 04:19 PM

Mirth and SQL-Server 2014 via jTDS 1.3.1
 
I've been using Mirth 3.2.1 with a MS SQL-Server database and jTDS 1.3.1 for the JDBC driver. I've had no problem connecting to SQL-Server 2008 and 2012 with the above, but with 2014 I'm getting the error "I/O Error: DB server closed connection". Everything is set up the same for the Mirth users in our 2014 SQL-Server as in the others, and the only thing I've changed in Mirth is the name of the host in the database.url in the mirth.properties file.

I have a suspicion that the problem lies with jTDS. Its website says it supports SQL-Server thru 2012 but doesn't mention 2014. I've brought this up on the jTDS forum and am awaiting responses, but in the meantime I thought I'd check to see if any other Mirth users have run into this. If so, did you resolve it, and how did you do it?

clinjohn 06-17-2015 12:35 PM

Use MS JDBC 4.1 instead of jTDS 1.3.1 for SQL-Server 2014
 
In my previous post I neglected to mention that the inability to connect to SQL-Server 2014 has been preventing getting the Mirth Connect service to boot. This is because we have Mirth Connect set up to use SQL-Server as the home for Mirth's administrative tables. In 2014 I wasn't even able to get the tables built because I never got an initial successful connection.

I've resolved all this now, but to do so I had to switch to Microsoft's own 4.1 JDBC driver.

I haven't received any responses to my post on the the jTDS forum, but I have come across other posts on the web from folks who have also been unable to reach SQL-Server 2014 using jTDS 1.3.1. Their issues don't seem to have been resolved, or at least no resolutions are described in any responding posts. It seems to me that other Mirth Connect users will run into this same problem if they haven't already. Has anyone used SQL-Server 2014 and jTDS 1.3.1 successfully, i.e. not encountered the problem I did?

For those that are curious, to use MS JDBC 4.1 I added the following line to dbdrivers.xml:
<driver class="com.microsoft.sqlserver.jdbc.SQLServerDrive r" name="SQL Server 2014" template="jdbc:sqlserver://host:port;databaseName=dbName;integratedSecurity=f alse;" selectLimit="SELECT TOP 1 * FROM ?" />
Then in mirth.properties I added a similar line using the above format. I also discovered that sqljdbc41.jar needs to be in server-lib/database. Originally I had it in custom-lib, but that doesn't appear to work.

rdejournett 05-24-2018 04:28 AM

Hi we experienced an issue with MC 1.4.2 doing a db call to a SQL Server instance (Server 2008 R2).

The exception was: DETAILS: Wrapped java.sql.SQLException: I/O Error: DB server closed connection.

And the offending line was:

107: var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://localhost:1433/dbname','username','pw');


The above solution worked. To clarify.

1) First google for sqljdbc - on the Microsoft website there should be a download similar to sqljdbc_6.0.8112.200_enu.

2) Go ahead and unarchive it. Go to the Jre8 folder. Copy the Jar to the mirth directory: server-lib/database

3) Change dbdrivers.xml to add in the drivers section:
<driver class="com.microsoft.sqlserver.jdbc.SQLServerDrive r" name="SQL Server 2014" template="jdbc:sqlserver://hostort;databaseName=dbName;integratedSecurity=f alse;" selectLimit="SELECT TOP 1 * FROM ?" />

Then change your call to be:
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.microsoft.sqlserver.jdbc.SQLServerDriver','j dbc:sqlserver://localhost:1433;databaseName=dbname','userr','pw');

You must restart the mirth connect service.


All times are GMT -8. The time now is 11:56 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation